… Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you…) Page hash 127499384 Dictionary cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0) Threads… total any more as Innodb has moved allocating memory from operation system directly not from addition memory pool (hence 0). Such allocations…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… that somewhere around of 99% of advice about tuning MySQL‘s key cache hit ratio is wrong, even when you hear it… the operating system‘s cache, then a Key_read is a disk read — but if it is cached, then it’s just a system call…-second intervals and compare the results to your IO system‘s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…, query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to use for your table. Take this number add to it number of memory Operation System and other applications need, add might be 32MB more for MySQL Server code and various small…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
…problem for similar reason as MySQL Restart – the traffic on different boxes might be different and hence cache content. Replication Traffic Changes…lag. Same may happen with operating system and other system software involved. In could be even system which queries mysql. I’ve seen monitoring updates…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…’t know whether a read will be satisfied from the operating system cache, or whether it’ll have to go to disk. (This… Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** 1. row *************************** Name: dim1…
Post: Configuring MySQL For High Number of Connections per Second
January 6, 2012 By 17 Comments
… to MySQL is made, it can go into the back_log, which effectively serves as a queue for new connections on operating system size to allow MySQL to handle spikes. Although MySQL connections are quite fast compared to… you’re working with many connections per second is thread_cache which impacts cost of connections dramatically. You want it to…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server there is significant chance data will still remain in Operation System cache speeding up…
Post: MySQL should have dynamic durability settings
April 8, 2008 By 4 Comments
If you’re using Innodb tables MySQL has innodb_flush_log_at_trx_commit variable which defines … set it to 2 which is flushes data to operation system cache (so if MySQL crashes transaction is not lost) but does not save… be decided for each transaction separately in session context. What MySQL Could do to take it further is to implement couple…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted … also help you to size your key buffer compared to operating system cache. I also use it to see which tables may be… whatever database you have active. It does not work with MySQL before 5.0 still of course 
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…operating system I’m working with. System performance Next I investigate what the system…mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql…| | Aborted_connects | 29 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 9630066 | | …
