It would be just great if someone could provide me some tips how to get the best MySQL performance on my server. I have server with 2xDual Opteron CPUs, 2 GB RAM and 160 GB SATA drive. There is Apache 1.3.x, PHP 4.4.4 and MySQL 4.1.21 installed. Server hosts about 100 domains, each domain has database with about 25.000 rows (about 100 MB of data per database).
Basically all the queries are like these:
“select * from keywords where keyword=‘xxx’”;
“update keywords set some_field=‘very_big_text’ where id=‘xx’”;
But the performance isn’t good (yes, I use full index for column “keyword”). Server gets about 200 Apache requests at the same time, and mysql drives me crazy. I think Apache is tweaked well itself, because when I try to open any site (without mysql database, only simple html) it’s being opened in speed of light. But when I try to open page with content from mysql database, it takes about 20-60 seconds (depends on current server load) before anything is displayed.
May someone suggest optimal values for my.cnf file and some other tips (if any), please?