… threads) with fast IO (OCZ R4 1.6TB) and 128GB memory. For the hot test, the data fits in the buffer… the working set fits in memory, InnoDB almost always performs better, at least for this workload. Notes MySQL version used: 5.6… data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… Server for MySQL will now be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL performance can be found in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has… explicitly named foreign key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… Server for MySQL will now be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL performance can be found in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has… explicitly named foreign key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… a six-hour tutorial about Percona XtraDB Cluster (PXC) for MySQL. I actually had more material than I covered (by design… replication feedback mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack of understanding… the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory. It keeps…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…a single row (well, the PRIMARY KEY index entry for that row). This means … or work around it with by fsyncing to memory (Battery or capacitor-backed write cache), etc.,…standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication?…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) … to add another index. Q: We have a database that has about 400GB of indexes. The indexes can’t fit in memory anymore. How does this affect performance? A: Typically you do not need all your indexes…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory‘; +——————————-+ | sum(data_length+index… tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory‘; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name… become very slow when index does not fit to memory any more. Creating Index by sort is great Creating index by sort can provide…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just…_scanblogpercona.commanual server_idblogpercona.commanual shared_memoryblogpercona.commanual shared_memory_base_nameblogpercona.commanual show_slave_auth_infoblogpercona.commanual skip_…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a… is in memory workload. For UNION case the optimizer is more advanced and it is able to deal with ranges: mysql [localhost…
