… 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… keys which are not accessed very frequently. Use what you need Both Memcached and the MySQL query cache are limited in size. If… least expensive queries are the ones you never run, then the most expensive queries very well may be the ones you have to run when…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…MySQL 5 number of Innodb performance counters were exported and now available in SHOW STATUS output. Most… array – how frequently slots are allocated in it and how frequently threads are signaled …. Normal reads originated from query executions are executed by threads running queries. On Unix/Linux …
Post: Managing Slave Lag with MySQL Replication
October 12, 2007 By 15 Comments
… load. If you get 100 active queries running on the slave overloading it, slave thread will most likely will not get CPU or… spikes as load spikes will frequently cause replication lag spikes as well. Locks – MySQL Replication SQL Thread executes queries same as any other… to ALTER TABLE, though running it on Master is frequently not an option either. So this query is often run directly on the Slave…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… mysql_fix_privilege_tables to ensure all new privileges are added, which is something I frequently see forgotten Next step is setup MySQL 5.0 to 5.1 replication to see if it runs properly. It… remove them and we were down to only query time differences. In most cases query time differences were not significant or Percona Server…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…frequently told by people with traditional database background. Things are different with MySQL…queries than doing complicated ones. Of course you would rather use IN() than do 100 of queries in this case. This most…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
…run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently… number, as what most people are often …queries against a 20M rows table: sysbench –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –mysql-socket=/tmp/mysql_sandbox5524.sock –mysql…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
… inconsistency much more frequently in almost 40% though the average observed delay remains just few milliseconds, which is something most applications would… will run a long update while running my test to see how the latency is impacted: mysql> update sbtest2 set k=k+1; Query…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… buffer hit ratio the user desires by running silly queries that do nothing but cause buffer hits…s where I return to what I called “mostly factual” above, because Key_reads actually aren’…MySQL to guide your decisions. It is also not the be-all and end-all of MySQL performance, and people frequently…
Post: Caching techinques
August 8, 2006 By 11 Comments
…is updated. This is how MySQL Query Cache works by removing all queries derived from the table if …would populate cache with result set for most frequent and complicated queries as new data is loaded and …can work in practice. Lets say you’re running big blogging site which has many blogs published…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve …are used. MySQL will execute these subselects on explain to provide plan for top most statement, so …to be examined is more complicated It is frequent approach to take number of rows estimated …
