Over last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems as well as stalls and due to coarse invalidation is not as efficient as it could be. These are however mostly due to neglect Query Cache received over almost 10 years, with very [...]
Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
There are many angles you can look at the system to predict in performance, the model baron has published for example is good for measuring scalability of the system as concurrency growths. In many cases however we’re facing a need to answer a question how much load a given system can handle when load is [...]
Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]
Estimating Replication Capacity
July 20, 2010 By 4 Comments
It is easy for MySQL replication to become bottleneck when Master server is not seriously loaded and the more cores and hard drives the get the larger the difference becomes, as long as replication remains single thread process. At the same time it is a lot easier to optimize your system when your replication runs [...]
Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread [...]
Is your MySQL Server Loaded ?
March 19, 2010 By 6 Comments
So you’re running the benchmark/stress test – how do you tell if MySQL server is really loaded ? This looks like the trivial question but in fact, especially when workload consists of simple queries I see the load generation and network really putting a lot less load on MySQL than expected. For example you may [...]
Three ways to know when a MySQL slave is about to start lagging
October 8, 2008 By 8 Comments
The trouble with slave lag is that you often can’t see it coming. Especially if the slave’s load is pretty uniform, a slave that’s at 90% of its capacity to keep up with the master can be indistinguishable from one that’s at 5% of its capacity. So how can you tell when your slave is [...]
Google’s user_statistics V2 port and changes
September 12, 2008 By 9 Comments
Recently Google published V2 release of patches, one of them user_statistics we use in our releases. New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes: New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc) New command SHOW CLIENT_STATISTICS, which shows statistics per client’s hostname, not per user [...]
MySQL extensions for hosting
April 24, 2008 By 11 Comments
A few weeks ago I was asked to isolate some functionalities from Mark Callaghan’s MySQL patch bundle. They were extensions adding per-user and per-table accounting to the database, accessible with a new set of commands such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data [...]
MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to less believe in common sense or common sense of documentation writers and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I’ve discovered even read_buffer_size [...]
