I have an application that currently runs hosted on Gradwell.net, using their MySQL server. I want to move the application to my Linode 96. At first, I tried installing PostgreSQL and porting the application. The performance was abysmal. A typical run which takes 1:05 on Gradwell took 25:15. I put it on my home server (two Athlon MP1800+ processors, 1024Mb of RAM) and on PostgreSQL it runs in 7:45. Still too slow. So I tried it on my home server with MySQL, and it ran in 0:32.

So I decided to try it on the Linode with MySQL. And the performance sucks sucks suck. It took 42:01.

I think the application itself is a bit big, "top" shows it as 12m. But there's got to be some way to tune MySQL to fit well on a 96Mb linode. Any hints?

Have you tweaked MySQL via my.cnf? Sometimes that helps performance

_________________
-- Surferdude

Tweaked what? Any suggestions what parameters to tune, and what values work on a 96?

Theres a file in /etc called my.cnf which contains the directives for MySQL. Caker made a my.cnf file designed for low memory at http://www.theshore.net/~caker/uml/my.cnf Besides that, you should think about a memory upgrade, purchased via the LPM in the extras section.

_________________
-- Surferdude

There are two things I'd watch in two windows as queries are being ran:

# watch -n1 cat /proc/io_status

and

# vmstat 1

For the former, if you see tokens go negative, you've hit the limiter -- most likely due to swap thrashing, which the second command will show you (si and so).

Some of mysql's default settings will eat up a decent chunk of RAM, and depending on other things you've got running you might be swap thrashing.

-Chris

I'm not seeing much activity in the io_tokens - hovering around 400000 or just slightly below. But it's still damn slow. Using the previous poster's link to that my.cnf file, I tried those values (and restarted mysqld of course) and it's no faster.

It's amazingly annoying - I took the database dumps and the code directly from Gradwell to here, and yet on Gradwell it takes 1:14 and here it takes 43:54.

If this is a restore of a previous database, did you carry over all the indexes?

-Chris

Just a follow-up post -- Thanks to http://planet.linode.com I noticed this:

http://xcski.com/blogs/pt/2005/06/02/ok ... -the-table

Keep in mind that Xen is coming, which will greatly improve performance. Compare the following kernel build times:

Host(noXen): 5m5.993s
dom0 (Xen host): 5m19.758s
domU(Xenode): 5m49.133s
UML(Linode): 15m24.464s

-Chris