-
Schooner On-Demand Webinars now Available
Database Best Practices for 2012: A Roadmap to Lower TCOIntended for IT Directors, C-level executives and their direct reports. If your organization suffers from unavailable, inaccurate or less-than-instantaneous data and struggle to strike a balance between affordable IT solutions and those that really deliver, join this webinar series to learn more about high availability paradigm shifts and business continuity best practices.Best Practice #1: Spend Less for High Availability.Watch it at your convenience. It is available now. (32 minutes) Tips, Tricks and Tools to Improve DBA Quality of Life: A Schooner Tech SeriesIntended for DBAs and Sys Admins responsible for managing, monitoring and maintaining data centers.Radically Simpler Database Admin: A Tour of the SchoonerSQL GUIWatch it at your convenience. It is available now. (29 minutes)More information can be found here:-http://www.schoonerinfotech.com/resources/index.php (click on Webinars)
-
A year with Drizzle
Today I'm coming out of the closet. Since I'm a professional database expert I try to be like the mainstream and use the commercial MySQL forks (including MySQL itself). But I think those close to me have already known for some time that I like community based open source projects. I cannot deny it any longer, so let me just say it: I'm a Drizzle contributor and I'm very much engaged!
I've been eyeing the Drizzle project since it started in 2008. Already then there were dozens of MySQL hackers for which this project was a refuge they instantly flocked to. Finally a real open source project based on MySQL code that they could contribute to, and they did. It was like a breath of fresh air in a culture that previously had only accepted one kind of relationships: that between an employer and an employee. Drizzle was more liberal. It accepted also forms of engagement already common in most other open source projects that are based on relationships between 2 or more consenting contributors.
But in 2008 I wasn't yet ready to engage with Drizzle. Like I said, I worked in a role where I would go to database users and help them use MySQL in demanding production settings. So as much as I admired Drizzle already back then, I needed something that could give me good releases, and support me when needed.
read more
-
The relationship between Innodb Log checkpointing and dirty Buffer pool pages
This is a time-honored topic, and there’s no shortage of articles on the topic on this blog. I wanted to write a post trying to condense and clarify those posts, as it has taken me a while to really understand this relationship.
Some basic facts
Most of us know that writing into Innodb updates buffer pool pages in memory and records page operations in the transaction (redo) log.
Behind the scenes those updated (dirty) buffer pool pages are flushed down the to the tablespace.
If Innodb stops (read: crashes) with dirty buffer pool pages, Innodb recovery must be done to rebuild the last consistent picture of the database.
Recovery uses the transaction log by redoing (hence the name ‘redo log’) the page operations in the log that had not already been flushed to the tablespaces.
Ultimately this mechanism was an optimization for slow drives: if you can sequentially write all the changes into a log, it will be faster to do on the fly as transactions come in than trying to randomly write the changes across the tablespaces. Sequential IO trumps Random IO.
However, even today in our modern flash storage world where random IO is significantly less expensive (from a latency perspective, not dollars), this is still an optimization because the longer we delay updating the tablespace, the more IOPs we can potentially conserve, condense, merge, etc. This is because:
The same row may be written multiple times before the page is flushed
Multiple rows within the same page can be written before the page is flushed
Innodb Log Checkpointing
So, first of all, what can we see about Innodb log checkpointing and what does it tell us?
mysql> SHOW ENGINE INNODB STATUS\G
---
LOG
---
Log sequence number 9682004056
Log flushed up to 9682004056
Last checkpoint at 9682002296
This shows us the virtual head of our log (Log sequence Number), the last place the log was flushed to disk (Log flushed up to), and our last Checkpoint. The LSN grows forever, while the actual locations inside the transaction logs are reused in a circular fashion. Based on these numbers, we can determine how many bytes back in the transaction log our oldest uncheckpointed transaction is by subtracting our ‘Log sequence number’ from the ‘Last checkpoint at’ value. More on what a Checkpoint is in a minute. If you use Percona server, it does the math for you by including some more output:
---
LOG
---
Log sequence number 9682004056
Log flushed up to 9682004056
Last checkpoint at 9682002296
Max checkpoint age 108005254
Checkpoint age target 104630090
Modified age 1760
Checkpoint age 1760
Probably most interesting here is the Checkpoint age, which is the subtraction I described above. I think of the Max checkpoint age as roughly the furthest back Innodb will allow us to go in the transaction logs; our Checkpoint age cannot exceed this without blocking client operations in Innodb to flush dirty buffers. Max checkpoint age appears to be approximately 80% of the total number of bytes in all the transaction logs, but I’m unsure if that’s always the case.
Remember our transaction logs are circular, and the checkpoint age represents how far back the oldest unflushed transaction is in the log. We cannot overwrite that without potentially losing data on a crash, so Innodb does not permit such an operation and will block incoming writes until the space is available to continue (safely) writing in the log.
Dirty Buffer Pool Pages
On the other side, we have dirty buffers. These two numbers are relevant from the BUFFER POOL AND MEMORY section of SHOW ENGINE INNODB STATUS:
Database pages 65530
...
Modified db pages 3
So we have 3 pages that have modified data in them, and that (in this case) is a very small percentage of the total buffer pool. A page in Innodb contains rows, indexes, etc., while a transaction may modify 1 or millions of rows. Also realize that a single modified page in the buffer pool may contain modified data from multiple transactions in the transaction log.
As I said before, dirty pages are flushed to disk in the background. The order in which they are flushed really has little to nothing to do with the transaction they are associated with, nor with the position associated with their modification in the transaction log. The effect of this is that as the thread managing the dirty page flushing goes about its business, it is not necessarily flushing to optimize the Checkpoint age, it is flushing to try to optimize IO and to obey the LRU in the buffer pool.
Since buffers can and will be flushed out of order, it may be the case that there are a lot of transactions in the transaction log that are fully flushed to disk (i.e., all the pages associated with said transaction are clean), but there still could be older transactions that are not flushed. This, in essence, is what fuzzy checkpointing is.
The checkpoint process is really a logical operation. It occasionally (as chunks of dirty pages get flushed) has a look through the dirty pages in the buffer pool to find the one with the oldest LSN, and that’s the Checkpoint. Everything older must be fully flushed.
The main reason this is important is if the Checkpoint Age is not a factor in dirty buffer flushing, it can get too big and cause stalls in client operations: the algorithm that decides which dirty pages to flush does not optimize for this [well] and sometimes it is not good enough on its own.
So, how can we optimize here? The short of it is: make innodb flush more dirty pages. However, I can’t help but wonder if some tweaks could be made to the page flushing algorithm to be more effective there in choosing older dirty pages. It is clear how that algorithm works without reading the source code.
There are a lot of ways to tune this, here is a list of the most signficant, roughly ordered from oldest to newest, and simultaneously listed from least effective to most effective:
innodb_max_dirty_pages_pct: This attempts to keep the percentage of dirty pages under control, and before the Innodb plugin this was really the only way to tune dirty buffer flushing. However, I have seen servers with 3% dirty buffers and they are hitting their max checkpoint age. The way this increases dirty buffer flushing also doesn’t scale well on high io subsystems, it effectively just doubles the dirty buffer flushing per second when the % dirty pages exceeds this amount.
innodb_io_capacity: This setting, in spite of all our grand hopes that it would allow Innodb to make better use of our IO in all operations, simply controls the amount of dirty page flushing per second (and other background tasks like read-ahead). Make this bigger, you flush more per second. This does not adapt, it simply does that many iops every second if there are dirty buffers to flush. It will effectively eliminate any optimization of IO consolidation if you have a low enough write workload (that is, dirty pages get flushed almost immediately, we might be better off without a transaction log in this case). It also can quickly starve data reads and writes to the transaction log if you set this too high.
innodb_write_io_threads: Controls how many threads will have writes in progress to the disk. I’m not sure why this is still useful if you can use Linux native AIO. These can also be rendered useless by filesystems that don’t allow parallel writing to the same file by more than one thread (particularly if you have relatively few tables and/or use the global tablespaces) *cough ext3 cough*.
innodb_adaptive_flushing: An Innodb plugin/5.5 setting that tries to be smarter about flushing more aggressively based on the number of dirty pages and the rate of transaction log growth.
innodb_adaptive_flushing_method: (Percona Server only) This adds a few new different algorithms, but the more effective ones adjust the amount of dirty page flushing using a formula that considers the Checkpoint age and the Checkpoint age target (something you can set in Percona Server, otherwise it is effectively just a hair under the Max Checkpoint age). The two main methods here would be ‘estimate’ (good default) and ‘keep_average’ designed for SSD type storage. Running Percona Server and using this method is my go-to setting for managing Checkpoint age.
To be clear, the best we have today (IMO) is using innodb_adaptive_flushing_method in Percona server.
In any case, if you run any kind of production MySQL server, you should be monitoring your Checkpoint age and your Innodb dirty pages and try to see the relationship with those values and your write operations on disk. The additional controls in 5.5 and Percona server are excellent reasons to consider an upgrade.
-
MySQL User Group Day on FreeNode !
As MySQL Community Managers, Dave an myself are always looking for ways to gather feedback on MySQL. We want to make ourselves available to you. The current plan is the last Monday of every month Dave and Myself will be on #freenode and join a chat room called MySQL_user_groups. We will also try to be available as much as possible in a chat room called MySQL_Community. You can join us and ask questions or just let us know your opinions. We want to hear about everything from your best presentation topics, to the worst. What is the biggest struggle you find with user groups ? How could Oracle help?
-
Announcing MySQL Monitoring Plugins from Percona
We’ve released a new set of monitoring plugins for MySQL servers and related software. With these plugins, you can set up world-class graphing and monitoring for your MySQL servers, using your own on-premises Cacti and Nagios software. The Cacti plugins are derived from an existing set of templates we’ve been using for several years, but the Nagios check plugins are brand new. They are informed by the research we did into the causes and preventions of MySQL downtime.
Like all Percona software, the plugins are open-source and free, licensed under the GNU GPL. The source code and issue tracker are hosted at Launchpad. The 0.9.0 release is ready to download now, and the reference manual is also online. In the next release we will integrate this into our package management, so you can install through YUM and APT repositories.
The new monitoring plugins add first-rate support for MySQL to popular enterprise opensource monitoring systems, and that’s why we’ve added them to the list of software included in our support contracts. If you’re an existing customer of Percona’s MySQL Support services, your contract automatically covers these new plugins, too; you don’t need a new contract for that. Under the support contract, you’ll get help installing, configuring, troubleshooting, and administering your monitoring plugins, as well as being entitled to bug fixes. And as always, we’re also available to extend the plugins to support your environment or broaden the range of systems and software that they can monitor.
Please use the Launchpad bug tracker to report issues. Happy monitoring!
|