Configuring my.cnf

[Thorton]

Hello,

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?

[linuxrunner]

Post your current my.cnf and are you using MyISAM or InnoDB engine?

[Thorton]

I’m using MyISAM. Actually, my.cnf has nothing at the moment (except this line: set-variable = max_connections=500), because everything was reinstalled today (it’s cPanel powered server).

[linuxrunner]

Can you post the output of top sorted by memory usage?

[Thorton]

3901 mysql 15 0 168m 51m 3436 S 1 2.6 7:19.85 mysqld
28921 nobody 16 0 75808 52m 7180 S 1 2.6 0:06.47 httpd
1061 nobody 16 0 71044 43m 2772 S 1 2.2 0:00.03 httpd
20743 nobody 15 0 77620 53m 6840 S 0 2.7 0:11.93 httpd
837 nobody 15 0 71820 44m 3264 S 0 2.2 0:00.10 httpd
1037 nobody 15 0 71072 43m 2456 S 0 2.1 0:00.03 httpd
1055 nobody 15 0 70924 43m 2380 S 0 2.1 0:00.02 httpd
1077 nobody 15 0 71248 43m 2448 S 0 2.1 0:00.04 httpd
1475 root 16 0 3680 1156 760 R 0 0.1 0:00.40 top
1494 nobody 15 0 70924 43m 2380 S 0 2.1 0:00.02 httpd
1539 nobody 17 0 73636 45m 2412 S 0 2.3 0:00.05 httpd
1 root 16 0 2484 552 472 S 0 0.0 0:00.90 init

[Thorton]

After clicking “Show MySQL Processes” on WHM, I see over 200 proccesses now

[Thorton]

Is it possible that issue is related to number of available mysql connections, or something like this?

I think so, because server gets the same number of requests all the time, but mysql makes very high load only every 20-30 minutes. When load is normal, after clicking “View mysql proccesses”, I see only few proccesses. But about 20 minutes later ALL databases on server seem to be having tons of connections (at the moment there are over 300 proccesses). Then server fails for 5 minutes and mysql is restarted automatically. Everything works fine then (server still gets many apache requests, but sites work well), and after another 20 minutes same problem happens - absolutely all databses start making queries at the same time.

My configuration looks like this now:

[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=8M
query_cache_type=1
max_connections=900
interactive_timeout=150
wait_timeout=40
connect_timeout=10
thread_cache_size=80
key_buffer_size=64M
join_buffer=1M
max_allowed_packet=16M
table_cache=768
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
tmp_table_size=512M
low_priority_updates=1
#log-bin
server-id=1
log-slow-queries
long_query_time = 5
max_user_connections=20

However, it didn’t help at all. If I want to see any data from mysql, website loads about 20-60 seconds. What can I improve here?