…, 3771.73 lookups per second Testing 12800 out of 10000000 keys 386.20 seconds, 258.94 lookups per second Testing 25600… row cache would fair better, so I would expect better results with PBXT. I also should check with smaller page sizes available in Percona Server and my expectation is with 4K page size I can fit 4x more distinct rows in my cache…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true — not… query cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too. The query cache can…_packet). And keys for user locks! I set my max_allowed_packet bigger and ran the following on my laptop: mysql> set…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
…and MySQL will need to scan keyblock from the start uncompressing keys to find matching key value…. ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0…there are more reasons to have keys packed so cache hit is better. Especially if …
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… with different IO alignment, not different MySQL configurations, I didn’t try out different MySQL versions or settings. Moreover, I … 16M binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = …
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… some of the most common cache methods for MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL examines each query to… for keys which are not accessed very frequently. Use what you need Both Memcached and the MySQL query cache are limited in size. If you try to cache more information than you can store…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
…already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly…MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches… populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE …
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
…MySQL 5.6.7 locks itself on CREATE INDEX MySQL 5.6.7-rc crashed under tpcc-mysql…cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row …_creation=1 which builds table with primary key only when creating a table and when… this workload. Still substantial. Sorting needs OS Cache It is not clearly seen from the…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… likely to be lower due to less CPU cache usage efficiency larger object size and other reason. So what my test does… are: Cache Type Cache Gets/sec Array Cache 365000 APC Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix….test for MySQL Cache to work CREATE TABLE `test` ( `k` varchar(60) NOT NULL default ”, `val` varchar(255) default NULL, PRIMARY KEY (`k…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… caches and QueryCache is only first in line. There is also all kinds of 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…
