Mysql and server load on a linode64

Hi there,

I rent a linode64 (with Debian) as well, and I have quite several dynamic websites running with acceptable performance. I'm using various CMSs and scripts as well - Mambo, drupal, phpwiki, etc; no problem.

But indeed some CMS / script can hit even a powerful server rather hard. I remember an article that compares various CMSs, where the worst are about 50x slower than the fastest !

I thought Exhibit is the culprit, but then I realise that phpbb is having performance problem as well… (phpbb is quite light on server resources)

Sorry if this is not helping you directly - but basically I think this is indeed config issue, the server should be already adequate for your need.

cheers, HS

@Alexis2:

Hi guys

I'm not really a linux guru, but i have already searched google and all the forums i could find w/o a solution.

My linode 64 is running a debian config, with the latest php, sql and apache 1.3.

Everytime there is a sql query server load goes high, cpu usage can be 100% on a simple query for a phpbb, thus a very slow loading of my pages.

i'm not getting that many hits, and from top i can see i'm the only one trying to access the pages, and i see the mysql taking 90-100% of the cpu for some time

I run Exhibit engine for photo galleries, and sql queries often take 1500-5000 millisec

here's an example of my photo site : http://www.underwaterpics.org/

and for a phpbb (sorry it's in french language) : http://diabeteplongee.com/phpBB2/

i'm not sure of what to do :(

do i need to upgrade to a larger linode ? i'm affraid it's a config problem and that it still be slow with a more expensive setup

thanks !

Alex

You can try tweaking your my.cnf to use less memory. Here are the relavent lines in mine:

[mysqld]
skip-innodb
skip-bdb

skip-locking
key_buffer=16k
max_allowed_packet=1M
table_cache = 4
sort_buffer_size = 64K
net_buffer_length = 2K
thread_stack=64K
# keep secure by default!
##bind-address    = 127.0.0.1
##port          = 3306
# this can make it even more secure:
skip-networking

If you use innodb or bdb, then you wouldn't add those lines. Also, all connections to my database happen through a socket, so I do not even need networking support. You should run a 'ps aux' command before and after to see how much the memory usage went down. After you edit this file, you will have to restart mysqld.

Hope this helps.

-John

Don't forget that indexes can make a huge difference to database performance. You might want to somehow check to see if those slow queries are using indexes properly. The authors of the software should have done this but …. you never know.

I had several sites happily running PHP and MySQL for quite some time on a Linode 64. I eventually went to a 96 but my main memory hog was Spamassassin.

Cheers

Ross

I run spamassasin on linode64 with mysql etc.. how much difference did the extra 32 ram make by moving to a linode 96?

I have also found that by tuning the my.cnf file, purging the ib_logfiles in the mysql data folder and the ibdata file (with mysql not running of course) I have quite drastically reduced both my load average and the performance hit of the mysql processes.