How bad MySQL really is

I've got a customer who has an image gallery, sadly they often have problems with performance. Our first big migration was from Apache with mod_php to nginx with FastCGI and MySQL being on a seperate host. This has worked for almost a year now, but now trouble is here again...

You might wonder, "an image gallery? what can go wrong with that?", well a lot actually. Apart from using the uber-buggy Coppermine Gallery which almost regularly has some really bad security holes, the gallery software features a good amount of badly written SQL statements which bring down MySQL really fast. Due to the lack of developer skills, they also have no DB-abstraction which makes it hard to use with a real database like PostgreSQL.

So why is MySQL so bad?

First let us look at the specs of the DB-Host:

# grep "model name" /proc/cpuinfo 
model name  : Intel(R) Xeon(TM) CPU 2.40GHz
model name  : Intel(R) Xeon(TM) CPU 2.40GHz
model name  : Intel(R) Xeon(TM) CPU 2.40GHz
model name  : Intel(R) Xeon(TM) CPU 2.40GHz

# grep MemTotal /proc/meminfo 
MemTotal:        4152104 kB

# dmesg | grep -i "direct-access"
[    7.069453] scsi 0:0:0:0: Direct-Access     IBM-ESXS MAP3367NC     FN C101 PQ: 0 ANSI: 3
[    7.099705] scsi 0:0:1:0: Direct-Access     IBM-ESXS DTN036C3UCDY10FN S27M PQ: 0 ANSI: 3

Looks quite ok for a single db host which is supposed to host one lousy database for an image gallery.

Resource usage

The database is running production and has accumulated ~500MB. This is by far not much..

# du -hs /var/lib/mysql/
496M    /var/lib/mysql/

Top shows this:

top - 20:33:30 up 271 days, 21:12,  1 user,  load average: 0.00, 0.02, 0.05
Tasks:  85 total,   1 running,  84 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.6%us,  0.1%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4152104k total,  3379848k used,   772256k free,   333684k buffers
Swap:   498004k total,        0k used,   498004k free,   747136k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 
9646 mysql     20   0 2131m 2.1g 4308 S   15 52.1  22343:23 mysqld

So HOW the fscking hell can a Database eat 52.1% of 4GB RAM to hold a 500MB Database and still suck performance-wise?

I am by far now MySQL-Fan, but this shows me once again that MySQL is not a real database. It's a toy for people who want to try out PHP without digging too much into the whole RDBMS thing, but that is not working. It makes me very proud to see real projects come out of the ground not being written in PHP or relying on MySQL as their RDBMS, the lazy PHP days are over, at least for me.

But when i look at my past 4 PHP-free years and the whackadoos that usually use that language, i am so proud i jumped ship and started doing real work.

Solution

Instead of trying to optimize the shity database any further, i've come up with an arrangement for my customer, where i would write him an Image Gallery Software that works and performs.

It is almost finished and is build using the following pieces of software:
* the Scala Language
* the incredible Lift Web Framework
* PostgreSQL for storing Users and FTP Logins
* MongoDB for storing all Gallery-related data

When it's done, i'll write a detailed post about it, be sure. ;)

Next to come, Juniper benchmarks. stay tuned!

Flattr me!

Tell your friends!