So you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache and pushing application to the swap. This causes swapping and causes performance problems. This much is obvious. But how bad is it ? Should you count it same as normal Disk IO as the box is having or is it worse than that ?
[read more...]
To continue fun with FusionIO cards, I wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB with Percona patches).
As benchmark engine I used tpcc-mysql with 1000 warehouses ( which gives around 90GB of data + indexes) on my workhourse Dell PowerEdge R900 ( details about box ).
On storage configuration: FusionIO 160GB SLC and 320GB MLC cards are configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard drives, here I followed Yoshinori Matsunobu’s recommendations http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html and taking fact that FusionIO is not perfect for sequential writes http://www.mysqlperformanceblog.com/2010/01/11/fusionio-320gb-mlc-benchmarks/
I can think of at least two major reasons why systems delay flushing changes to durable storage:
1. So they can do the work when it’s more convenient.
2. So they can do less work in total.
Let’s look at how the second property can be true.
I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:
[read more...]
I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.
The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable … but I noticed one troubling thing: ~2GB of uncheckpointed data.
mysql> SHOW INNODB STATUS\G .... Database pages 2318457 Old database pages 855816 Modified db pages 457902 Log flushed up to 10026890404067 Last checkpoint at 10024612103454 ....
We enabled innodb_adaptive_flushing just before 10am, which resulted in a few changes which were recorded by the Cacti MySQL Templates. The most important change for the client here is the recovery time — by enabling adaptive flushing, InnoDB purged modified data much more aggressively, resulting in very little unflushed changes, which translates to much faster crash recovery.
[read more...]
I thought I should praise Innodb team for all the work they have been doing recently. We see a lot of cool stuff happening, especially in the area of our interest which is Performance And Scalability.
Innodb Plugin 1.0.4 had a lot of great performance improvements and 1.0.5/1.0.6 gets even further with long standing caching issues of large full table scan wiping off the cache. The Group Commit bug is also finally fixed now.
Innodb plugin documentation now seems to be merged with MySQL Documentation which makes it much more usable and Innodb Plugin is shipped with MySQL 5.1 which makes it much easier to use which means wider community testing.
Number of patches suggested by community such as Google and Percona were reworked and included in the plugin during latest months.
We can also see Innodb Plugin 1.0.5 is named Release Candidate which hopefully means it will be considered ready for general use – almost 2 years from the date of initial public release and probably 4 years since the work on Innodb plugin features such as compression or Online index creation has started.
Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided to look at yet another similar variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode.
Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start – there is no variable to control its size and it can take significant amount of memory in some edge cases. Percona patches though provide innodb_dict_size_limit to restrict growth of data dictionary.
[read more...]
One problem made me puzzled for couple hours, but it was really interesting to figure out what's going on.
So let me introduce problem at first. The table is
Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk
The problem is that after restoring table from mysqldump, the query that scans data by primary key was slow. How slow ? Let me show.
[read more...]
I said in my last post, that we're interested in speaking at MySQL meetups, and I'm happy to say that the Los Angeles MySQL Meetup has taken us up on the offer.
On November 18th, I'll be giving an introductory talk on InnoDB/XtraDB Performance Optimization. I will be the second speaker, with Carl Gelbart first speaking on Infobright.
What brings me to LA? On the same day (18th Nov) I'll be teaching a one day class on Performance Optimization for MySQL with InnoDB and XtraDB. If you haven't signed up yet - spaces are still available.
First time I heard about Galera on Percona Performance Conference 2009, Seppo Jaakola was presenting "Galera: Multi-Master Synchronous MySQL Replication Clusters". It was impressed as I personally always wanted it for InnoDB, but we had it in plans at the bottom of the list, as this is very hard to implement properly.
The idea by itself is not new, I remember synchronous replication was announced for SolidDB on MySQL UC 2007, but later the product was killed by IBM.
So long time after PPC 2009 there was available version mysql-galera-0.6, which had serious flow, to setup a new node you had to take down whole cluster. And all this time Codership ( company that develops Galera) was working on 0.7 release that introduces node propagation keeping cluster online. You can play with 0.7pre release by yourself MySQL/Galera Release 0.7pre.
[read more...]
del.icio.us
digg