We receive many requests for help with server stalls. They come under various names: lockup, freeze, sudden slowdown. When something happens only once or twice a day, it can be difficult to catch it in action. Unfortunately, this often leads to trial-and-error approaches, which can drag on for days (or even months), and cause a […]
Finding bad queries is a big part of optimization. A scientific optimization process can be simplified to “can anything be improved for less than it costs not to improve it? – if not, we’re done.” In databases, we care most about the work the database is doing. That is, queries. There are other things we […]
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a “roughly equivalent” subquery. The idea — and there may be shortcomings that are poisoning the results here, your comments welcome — is to see how fast the SQL procedure code […]
Introduction When people think about Perconaâ€™s microslow patch immediately a question arises how much logging impacts on performance. When we do performance audit often we log every query to find not only slow queries. A query may take less than a second to execute, but a huge number of such queries may significantly load a […]
We made new binaries for MySQL 5.0.67 build 10 which include next fixes: We addressed concerns about potential logging and statistics overhead, so now you can fully turn on / off query statistics for microslow patch and user statistics in runtime. Next variables were added:
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/slow_query.log |
for compatibility with MySQL 5.1
| userstat_running | OFF |
to control gathering of user statistics.
With both slow_query_log = OFF and userstat_running = OFF […]
Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This […]
Whilst working with a client recently I noticed a large number of temporary tables being created on disk.
show global status like 'Created_tmp%'
| Created_tmp_disk_tables | 91970 |
| Created_tmp_files | 19624 |
| Created_tmp_tables | 1617031 |
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 […]
For a couple of months there have been no updates to our msl patch, however recently I managed some time to change this. The functionality was extended a little bit and whatâ€™s even more important the patch is available for all the recent MySQL releases. To remind anyone who has not yet come across this […]
We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting. This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple. We […]