… software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for…_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool… caused by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL. Now…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer…_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5… slow as compared to both MySQL 5.5 and MySQL 5.6. For MariaDB 5.5 the query time decreases when the join…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… have my website develop in PHP with MYSQL, these are MYSQL values: datadir=/data/mysql socket=/data/mysql/mysql.sock max_connections = 600 wait_timeout…-slow-queries=/var/log/mysql-slow-queries.log long_query_time=15 log-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql/drbd…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled…, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5.5… when the correct query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL 5.6…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… as a first-class member of the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places… Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved scalability That’s really just a sampling. A good starting point for learning more is the MySQL versus…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… saved by query cache. Now lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache Status Query Cache Configuration I would just mention couple of points – as protection from one query wiping…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local…
Post: MySQL caching methods and tips
April 4, 2011 By 4 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 see if the contents have been stored in the query cache…
