Performance problems with mysql on Linux vps

[Mutant]

I'm running an app using mysql (all InnoDB) on a linux vps, and I keep hitting a few performance issues... updates and inserts seem to periodically run really slowly... often around 1s, sometimes up to 10s. Select performance seems pretty good.

The app's under a very light load at the moment, and I haven't seen similar issues locally (also on linux)... have done a bit of tuning (and a *lot* of googling), but am wondering if it's something to do with vzfs or something on the server, rather than mysql itself?

Anyone seen anything similar or got any ideas?

[harry]

to eUKhost discussion board.
It would be better if you talk to tech support staff of eUKhost via Live Chat on this issue, they would provide suitable solution

[flesso]

Which VPS server hosting package are you using?

[vzAddict]

Hello,

PS is a "Virtual Server" that has Virtual memory due to which it is not possible to compare with server that has Physical Resources like Dedicated server. A Dedicated Server or Local System has Physical RAM and CPU which gives far better performance than Virtual Resources.

So, if the Mysql database are using high resources we can surely try to optimize them but if the problem still persists I would suggest you to upgrade to one of our Dedicated Server where you will get Physical resources.

Please send an email to vps[at]eukhost.com for us to try and optimize the database.

[Mutant]

Hi,

I realise it's a virtual server, but there's no real reason why it should be taking so long for simple update/insert queries to run under virtually zero load. As I said, selects seem fine, and CPU/Memory usage is fine too. That's why I suspect there's some disk IO issue.

Anyway, thanks for the comments, I'll send an email to support.

Thanks.

[mediaspace]

Hi Mutant,

Did you get any joy from support aside from them simply restarting services all the time - like i'm getting?

Hugh

[Mutant]

Actually, they were quite helpful in pointing out some settings I needed to tweak in the my.cnf (these were appropriate for my VPS setup anyway)

innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_thread_concurrency = 8

*BUT* they didn't realise (and neither did I) that you have to delete the old innodb log files before restarting the server, otherwise you get an error message whenever you run a query (because the InnoDB engine hasn't started up). So I thought at first that they'd managed to completey corrupt my DB.... I had to do a bit of googling to find out the solution....

These settings basically make the innodb logfile bigger, which is what it uses to store writes so it can flush less frequently... This seems to have sorted the performance problems... altho I also run everything in a transaction, which is supposed to be more efficient too.

On the plus side, it made me realise I need to keep rotating backups of my DB

[Mutant]

I should add that I'm using Ubuntu, and have installed / configured most of the stuff myself (and I have a reasonable idea what I'm doing in admining a sever.... well most of the time )

So your mileage may vary.