| Configuring my.cnf [message #1143] |
Tue, 24 April 2007 16:07  |
 Thorton
Messages: 19
Registered: March 2007 |
Junior Member |
|
|
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?
|
|
|
|
|
|
|
|
| Re: Configuring my.cnf [message #1149 is a reply to message #1143 ] |
Tue, 24 April 2007 19:34  |
Thorton
Messages: 19
Registered: March 2007 |
Junior Member |
|
|
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?
[Updated on: Tue, 24 April 2007 19:40]
|
|
|
Members
Search
Help
Register
Login
Home
