… Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you… down. What you might see instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
… setting innodb_flush_neighbor_pages=0 , to disable flushing of neighborhood pages (not available in MySQL 5.5.8). The problem with flushing neighborhood…_flush_neighbor_pages = 0 innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_io_capacity=500 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache=10000 [mysql] socket=/tmp/mysql.sock (post edited by Fred…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last… – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce… large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
…. Besides Operating System needs you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer… anything. On Linux, FreeBSD, Solaris you need to set innodb_flush_method=O_DIRECT. On other Operating Systems you may be…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure …need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses …reflective to large full table scan and index scan queries, though this is really not the point. The …
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 9 Comments
…=1, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1. Also note … reason as MySQL Restart – the traffic on different boxes might be different and hence cache content. … which queries mysql. I’ve seen monitoring updates which would add excessively expensive and frequent query on …
Comment: Using MyISAM in production
September 7, 2006 By 36 Comments
… free blocks when a single query is being removed from the cache. Regularly consolidating the free space with FLUSH QUERY CACHE may or may not help, I haven’t benchmarked it. See bugs: http://bugs.mysql…
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
… can use MySQL with Active Active Clustering and MyISAM tables if you follow certain rules like enabling external locking, disabling query cache and… really being compatible with such setup. As Oli describes even query cache which was implemented many years ago in 4.0 was… which Oli mentions is table cache which does not seems to be properly controlled. Manually doing FLUSH TABLES does not really works…
Post: The perils of uniform hardware and RAID auto-learn cycles
November 16, 2010 By 16 Comments
… way over a short time span (a couple of hours). Queries were taking multiple seconds to execute instead of being practically… addition, samples of SHOW INNODB STATUS showed a long pending flush list (hundreds), and usually one pending pwrite. After talking through… some threshold. I’ve seen this before — for example, a query cache bottleneck, or an insert buffer bottleneck, or any of a…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 MySQL 5.6.5…_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch…_files_in_group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch…
