… memory is what really really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of memory 26 EC2 Compute Units…_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized at 55G and I am… performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload. OK, so that…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… if you have a small buffer pool and a very large number of rows from table2 that do not fit into…) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these benchmark runs and that the…_size increased to 6M, the query time was ~300s while when both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G), because the query was taking far… of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks…, the query time goes down from ~11min to under a minute. The query time is reduced further when buffer size is set large enough…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large… allocated for query execution, which size can be controlled by tmp_table_size and which also only exist for duration of query execution so…. In Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length…
Post: Beware large Query_Cache sizes
March 23, 2007 By 3 Comments
… ? If you set query_cache_size relatively high at 256MB or more. It can be seen worse if your query cache size is in Gigabytes. At the same time check how many queries do you have in cache – Qcache_queries_in_cache – if it… to solve the problem ? The easiest solution is to decrease query cache size – smaller values do not have such problems but also often…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… works – we’ve seen performance issues with large query_cache_size also sort_buffer_size and read_buffer_size may not give you better performance if… well cached so the cost of table cache miss is not very high, you may be better of with significantly reduced table cache size… focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause significant performance problems. Interesting…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If you’re using … query executions Query Parsing and optimization needs memory. This is usually small to be ignored but certain queries can have very large …
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both… so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open… in normal operation. query_cache_size If your application is read intensive and you do not have application level caches this can be great…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be… needs you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer… pool size choice. You do not want OS to cache what Innodb is caching already. Innodb cache is more efficient compared to OS cache because…
