I studied some macroeconomics in school. I’m still interested in it 20 years hence. I was recently in a discussion about query optimization and how to prioritize what to fix first. My pen and paper started graphing things, and here we are with an abstract thought. Bear with me. This is for entertainment purposes, mostly, but may actually have a small amount of value in your thought processes around optimizing queries. This is a riff on various supply, demand graphs from macroeconomics.
In the graph below:
- Vertical: number of distinct problem queries
- Horizontal: Database “query load capacity” gains (from optimization)
- LIRQ (long and/or infrequently run queries)
- SFRQ (short, frequently run queries)
- AC: Absolute capacity (the point at which you’re going as fast as I/O platform you run on will let you and your query capacity bottlenecks have less to do with queries and more to do with not enough IOPS).
- E (subscript) O: Equilibrium of optimization
On LIRQ: Simply put, on a typical OLTP workload, you may have several long and infrequently running queries on the database that are “problems” for overall system performance. If you optimize those queries, your performance gain in load capacity is sometimes fairly small.
ON SFRQ: Conversely, optimizing short running but very very frequently run “problem queries” can sometimes create very large gains in query load capacity. Example: a covering index that takes a query that’s run many thousands of times a minute from 10 milliseconds down to < 1 millisecond by ensuring the data is in the bufferpool can give you some serious horsepower back.
On AC: Working on optimizing often run queries that are not creating an I/O logjam do not return any benefits. You can only go as fast as your platform will let you, so if you are getting close to the point where your database is so well optimized that you really can’t read or write to disk any faster, then you have hit the wall and you will produce little ROI with your optimization efforts unless you make the platform faster (which moves the red line to the right).
On EO: Often run long (or somewhat long) queries are the low hanging fruit. They should stand out naturally and be a real “apparent pain” in the processlist or in application response times without even bothering to pt-query-digest.
Speaking of pt-query-digest: digests of the slow query log (when log_query_time is set to 0) are a good way to figure out what types of queries are taking up the lion’s share of your database load. You will be able to tell via the ranking and the total time and percentiles shown in the digest what queries are taking up your database’s valuable time. I wish for you that you have SFRQ, so that your optimization effort may produce high rewards in capacity gain.
Thanks for bearing with me on my database capacity economics.