We often hear from customers and MySQL experts that fragmentation causes problems such as wasting disk space, increasing backup times, and degrading performance. Typical remedies include periodic "optimize table" or dump and re-load (for example, see Project Golden Gate). Unfortunately, these techniques impact database availability and/or require additional administrative cost and complexity. Tokutek's Fractal Tree algorithms do not not cause fragmentation, and we're looking for ways to measure the effects of fragmentation to quantify TokuDB's benefits.

I ran some tests using the iiBench benchmark as an experiment to try and quantify the impact of fragmentation, and observed some interesting …

It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema. The column was being used to represent a integer primary or foreign key column.

In a representative production instance (one of a dozen plus distributed production database servers) the overall database footprint was decreased from ~10 GB to ~2 GB, a 78% saving. In total, 15 columns across just 4 tables were changed from DECIMAL(31,0) to INT UNSIGNED.

One single table > 5GB was reduced to under 1GB (a 81% saving). This being my record for any GB+ tables in my time working with the MySQL database.

Had this server for example had 4GB of RAM, and say 2.5GB allocated to the innodb_buffer_pool_size, this one change moved the system from requiring more consistent disk access (4x data to memory) to being able to store all data in memory. Tests showed …

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.

In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)

Anyway, if you do a local mysqldump also, or for instance use …

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

Let's check the adaptive flushing options at this post.

HOW THEY WORK

< adaptive_checkpoint=reflex (older method)>

What is wrong here (the part of SHOW INNODB STATUS):

--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 9 queries in queue
100 read views open inside InnoDB

It is relationship between queries active - queries inside innodb+queries in the queue totalling 17 with "read views open inside InnoDB" which is a fancy way of saying "active transactions" which is 100.

Typically you would want this ratio to be close to 1, may be 2 which would correspond all active transactions doing active work and spending little time waiting on the application. Waiting transactions are bad because they hold lock as well as other resources, such as preventing innodb purge operation from proceeding.

If you see something like this in your envinronment it often makes sense to check the the list of transactions too. Chances are you would see something like:

---TRANSACTION 0 1044183147, …

If you ever wondered how big is that or another index in InnoDB ... you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR - since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

We chose to put this into xtrabackup for a couple of reasons - the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact results).

Let's see how it works. I have one table with size 13Gb what was filled during about 2.5 years.
…

I saw Mark Callaghan’s post, and his graph showing miss rate as a function of cache size for InnoDB running MySQL.  He plots miss rate against cache size and compares it to two simple models:

• 
  
• A linear model where the miss rate is (1-C/D)/50, and
  
• A inverse-proportional model where the miss rate is D/(1000C).

He seemed happy (and maybe surprised) that that the linear model is a bad match and that inverse-proportional model is a good match.  The linear model is the one that would make sense if every page were equally likely to have a hit.

I’ll argue here that it’s not so surprising.  Suppose that miss rate has a heavy-tailed distribution, such as Zipf’s law. An …

It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM table few times, though Table Locks is probably the most important issue - with modern multi core servers not only the fact you can't well mix SELECTs and UPDATEs but also the fact only one update can be happening at the time can be the problem, not to mention Key Cache which often becomes serious contention issue.

The problem we often run into during migration is Full Text Search indexes which are not supported for Innodb tables. So what can you do ?

Leave Tables as …

Today's the day I fly to Los Angeles to teach a private training class, en route to Santa Clara/San Francisco for our public training workshops next week.

Our Montreal practice teach at Station-C went great - it was an opportunity to do a road test and iron out any kinks in the delivery.

What did I learn in the process?

• Solid examples provide context.  I didn't have the best SHOW GLOBAL STATUS data for the practice teach, but I've edited my slides and our official classes will have much better information.
• Operational issues are one of the most important things people want to hear more information on.  A fair number of students know that dropping an index is not as painless as it should be, but not everyone knows about tools like Flipper and MMM.  It's not the main focus of our InnoDB/XtraDB workshop, but we will cover how to solve these …