… usually much better to simply check it. Start MySQL With 10GB Innodb buffer pool for example and see how large RSS and VSZ…. You may want to make MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other performance… always save you from swapping. Some specific kernel versions may have other settings to play with. Finally you can try locking MySQL…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
… can only do good” change as increasing buffer pool size could cause performance problems. MySQL Restart MySQL Restarts may include recovery time but what….6MB/sec which may take 24+hours to feel 200GB buffer pool. Automatic Buffer Pool Save/Restore available in Percona Server is great help in…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… be in date_id order’; /*!40101 SET character_set_client = @saved_cs_client */; CREATE TABLE `dim_flight` ( `UniqueCarrier` char(7) DEFAULT…=3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 11 Comments
… called “buffer” similar to “doublewrite buffer” it is really the space in the tablepace. Though it can be cached in the buffer pool same as other pages. This property allows insert buffer to survive transaction commits and even MySQL restarts. Really it… there is very good question if insert buffer really makes sense for SSD because saving random IO is not so much needed…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
…. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload… failure handling scenarios. Cold Start – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so… in storage engine caches or in OS cache. You can save these into .sql file and use –init-file to make…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer…buffer itself. large_pages – Use of Large Pages for allocation of large global areas such as Innodb Buffer Pool, Key Buffer…_count Variable which may save you from read …
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…total which easily fits in buffer pool. Fitting of the working set in buffer is paramount as if …use existing solutions you can use memcache + another mysql instance (or simply the database which is not… updates from the chunk in single transaction saving on log commits significantly. I mentioned you …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql…. so it’s hard to read. If the InnoDB buffer pool is set to 22GB, it’s much easier to…of tables that really matter. That’s why I save this part of the analysis for last. You …
Post: The perils of uniform hardware and RAID auto-learn cycles
November 16, 2010 By 16 Comments
…, the buffer pool got more dirty pages, InnoDB started creating more pages, and the percentage of dirty pages in the buffer pool went up… before — for example, a query cache bottleneck, or an insert buffer bottleneck, or any of a variety of other things. Rather… command we needed to disable auto-learn, (++text files and saving links to reference material), sent that over the Skype chat…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 14 Comments
I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant … id values. The table (and index) is much larger than buffer pool size. The first problem was replication lag, which are mainly… couple of partitions which were actively used could fit in buffer pool completely so replication became CPU bound (single thread) instead of…
