Hello,
I have a hopefully simple question here:
My web site is dynamically serving 300k page views a day from a MySQL
database, running on a dual P3/700 with 512 megs of ram. Considering the
complexity of my queries and the data amount involved, I know I should
expect the server to be overworked, but the weird thing is how the
problem manifests itself:
When I start up MySQL and Apache, things seem to run fine for a while, and
they continue running fine for about 24 hours. Then, when MySQL memory
usage has grown to around 70-80 megs, things start to slow down. Queries
which previously were over in a few hundredths of a second start taking up
to several seconds to complete, usually spending the extra time "Sending
data" or "Copying to temp table".
As you'd expect, the server gets bogged down rather quickly at this
point, serving new requests very slowly if at all. Restarting MySQL helps
right away, buying me another 24 hours of stable uptime.
What could cause this? I suspect it has something to do with the settings
in my.cnf, but I've tried many different combinations without success. If
someone could point me to the right variable(s) to tweak, that would be
helpful in itself. Suggested values for my system would help even more.
For now I can handle things by restarting MySQL every night, but that's
not a good solution. Shouldn't MySQL, in theory, be able to reuse memory
and other resources well enough for a system which stays stable for 24
hours to be expected to stay stable for a year? Could this be some sort of
memory leak in either MySQL or Apache?
Any help or advice would be much appreciated. Server details below.
Thanks,
Jon Valvatne
Webmaster,
AvidGamers.Com
Details:
Dual PIII/700
512MB RAM
9GB SCSI Drive
MySQL 3.23.37
Apache 1.3.12
PHP 4.0.4pl1
>From my.cnf:
skip-locking
skip-networking
set-variable = max_connections=18
set-variable = key_buffer=200M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=20M
set-variable = thread_cache=8
set-variable = thread_concurrency=4 # Try number of CPU's*2
set-variable = query_buffer_size=16k
set-variable = tmp_table_size=6M
set-variable = delayed_insert_limit=15
set-variable = max_write_lock_count=1
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
