Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided … closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start – there is… operation is slower than MyISAM tables. Though the difference can be related to the size of set of empty tables which is about 10…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
…cache usage efficiency larger object size…Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix Socket) 13500 Selecting from table…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global key_buffer_size…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… not opening and closing tables, which can be solved with decent table cache size, and which is very….tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set (1.08 sec) mysql> set global…
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
…set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size … you have less than 4GB worth of key cache used and then it would crash. If you …will be crashing on random statements associated to random tables. What I find interesting is this bug was …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…cache_sizeblogpercona.commanual max_connect_errorsblogpercona.commanual max_connectionsblogpercona.commanual max_delayed_threadsblogpercona.commanual max_error_countblogpercona.commanual max_heap_table…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… 10 table_cache = 2048 max_allowed_packet = 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 = …set to WT cache: The second is with WB cache: I couldn’t figure out what exactly happened with setting #3 when WB cache…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_cache_size=0 query_cache_type=0 MySQL 5.6.5 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size…_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer… from supplier table (left operand). Test Case D – Join a large data set (>1M rows) from one table with a large table The SQL…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data… with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use…_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…/tmp | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0…. query_cache_type and query_cache_size. The behavior is “no cache” by default still but it is achieved differently now. The query_cache_type…_blocks_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size is enabled by…
