…really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of … let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5…flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800…see the impact of growth in data-set on the insert rate. We can…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… the read_rnd_buffer_size/mrr_buffer_size were set to 6M, the query time dropped to ~40s. So… 2.2G 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 … number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is ‘Handler_mrr_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_…data_read 1.84G 1.89G 1.53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_pages… query time is reduced further when buffer size is set large enough so that the index tuples fit in …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… equal. Some indexes may be placed in sorted way or pages placed in random places – this may affect index scan/range… MySQL limitation which requires you to be extra careful working with large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… character data; try to make your rows fixed-length. One reason denormalization is important is that nested-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash joins, you… (within the page — the pages themselves may be widely separated, but that’s another matter). Don’t Store Non-Aggregated Data I’ve…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so…_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so… you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…data_pending_writesblogpercona.commanual Innodb_data_readblogpercona.commanual Innodb_data_readsblogpercona.commanual Innodb_data_writesblogpercona.commanual Innodb_data_writtenblogpercona.commanual Innodb_dblwr_pages…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… read-ahead depth here. These settings mean there can be quite significant stall waiting for the page which is being requested via… with a large patch, and the ‘last’ serious bug in it was only recently found and fixed: http://bugs.mysql.com/bug… setup and switching roles is a good way to get large data manageable. MySQL Master Master Manager can help you to automate it…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…use of ORDER BY in interactive applications with large data sets being sorted. On many web sites you will…else make sure you block requests with too large page numbers. For some cases, for example if …use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; …
