October 24, 2014

MySQL EXPLAIN limits and errors.

Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out. EXPLAIN can be wrong – this does not happen very often but it […]

Why MySQL could be slow with large tables ?

If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions […]

Using any general purpose computer as a special purpose SIMD computer

Often times, from a computing perspective, one must run a function on a large amount of input. Often times, the same function must be run on many pieces of input, and this is a very expensive process unless the work can be done in parallel. Shard-Query introduces set based processing, which on the surface appears […]

Innodb row size limitation

I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.

Goal driven performance optimization

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as […]

Are you designing IO bound or CPU bound application ?

This topic may look boring and obvious but it is extremely important for MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting work or even more frequently. IO Bound workload is quite different from CPU bound one, which happens when your working set (normally only fraction of your […]

Feature Idea: Finding columns which query needs to access

In query examinations it is often interesting which columns query needs to access to provide result set as it gives you ideas if you can use covering indexes to speed things up or even cache some data by denormalizing tables. So far it has to be done manually – look at SELECT clause, WHERE clause, […]

Watch out for Marketing benchmarks

Whenever I see benchmark results I try to understand if it is technical benchmark – made by people seeking the truth or it is done by Marketing department to wash your brains. Watch out. Whenever you treat marketing benchmarks as technical ones, you make make wrong decision. Take a look at MySQL 5.0 Benchmarks Whitepaper […]

SHOW INNODB STATUS walk through

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about […]