Monitoring MySQL isn’t just for data centers or large deployments. Even if you have a single MySQL server, monitoring is essential; as with your vehicle, there’s a lot to know about the system while it’s running to help you foresee or avoid malfunctions. mycheckpoint is one solution among many worth trying.
You can download mycheckpoint from the Google Code Archive. Documentation is available at Shlomi Noach’s openark website.
Essential MySQL admin tool: Shard-Query
Queries against partitioned or sharded data sets can be accelerated dramatically using Shard-Query, which parallelizes certain queries behind the scenes. Queries that use the following constructs can benefit from Shard-Query’s parallel execution:
- Subqueries in the FROM clause
- UNION and UNION ALL
- IN
- BETWEEN
Aggregate functions SUM, COUNT, MIN, and MAX can be used with those constructs, too. For example, this query can be executed in parallel by Shard-Query:
SELECT DayOfWeek, COUNT(*) AS c
FROM ontime_fact
JOIN dim_date USING(date_id)
WHERE Year
BETWEEN 2000 AND 2008
GROUP BY DayOfWeek
ORDER BY c DESC;
Benchmarks show that parallelizing that query reduces its response time by roughly 85 percent, from 21 seconds to 3.
Shard-Query is not a stand-alone tool; it requires other programs like Gearman, and it’s relatively complex to set up. But if your data is partitioned and your queries use any of the constructs listed above, then the benefits are worth the effort.
You can download Shard-Query from GitHub as part of the Swanhart Tools collection. Additional documentation is available in the MariaDB Knowledge Base.
Essential MySQL admin tool: pt-archiver
As tables become larger, queries against them can become slower. Many factors influence response times, but if you have optimized everything else and the only remaining suspect is a very large table, then archiving rows from that table can restore fast query-response times.
Unless the table is unimportant, you should not brazenly delete rows. Archiving requires finesse to ensure that data is not lost, that the table isn’t excessively locked, and that the archiving process does not overload MySQL or the sever. The goal is an archiving process that is reliable and unnoticeable except for the beneficial effect of reducing query times. pt-archiver achieves all this.
pt-archiver has two fundamental requirements, the first of which is that archivable rows must be identifiable. For example, if the table has a date column and you know that only the last N years of data are needed, then rows with dates older than N years ago can be archived. Moreover, a unique index must exist to help pt-archiver identify archivable rows without scanning the entire table. Scanning a large table is costly, so an index and specific SELECT statements are used to avoid table scans.
In practice, pt-archiver automatically handles the technical details. All you have to do is tell it what table to archive, how to identify archivable rows, and where to archive those rows. These rows can be purged, copied to another table, or written to a dump file for future restoration if needed. Once you’re comfortable with the tool, there are many options to fine-tune the archiving process. Also, pt-archiver is pluggable, so it can be used to solve complex archiving needs without patching the code.
You can download pt-archiver from Percona. It is also available as part of the Percona Toolkit.
Essential MySQL admin tool: oak-security-audit
When was the last time you audited the security of your MySQL servers? You’re not alone if “never” is the answer. There are many companies that provide security audits, but unless nothing ever changes after those audits, then the security of your MySQL environment should be checked regularly.
External threats are one obvious reason to enforce MySQL security, but internal threats like current or former employees are often more dangerous because they are (or were) trusted. Security is also important for enforcing privacy (medical/HIPAA regulations), preventing accidental access (for example, logging into the production server instead of the development server), or enabling third-party programs to interact with your systems.
For those looking to increase the security of their deployments, oak-security-audit is a worthwhile, free, open source tool that performs basic MySQL security audits. It doesn’t require any setup; just run it against your MySQL servers, and it prints a report with risks and recommendations about accounts, account privileges, passwords, and some general best practices, like disabling network access. Here’s a snippet of a report:
-- Looking for anonymous user accounts-- --------------------------------------------
-- Passed--
-- Looking for accounts accessible from any host-- ---------------------------------------------
-- Found 1 accounts accessible from any host. Recommended actions:RENAME USER 'msandbox'@'%' TO 'msandbox'@'<specific host>';
oak-security-audit focuses just on MySQL security, so it’s not a replacement for a full system security audit by a human, but it’s a great first line of defense that is easy to use. You could run it weekly with cron and have the reports emailed to you.
You can download oak-security-audit as part of Shlomi Noach’s openark kit collection of MySQL utilities. Documentation is available on Shlomi Noach’s GitHub pages.