Jon Valvatne wrote:
>
> 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
Hi,
Are you sure that you need 200Mb of key_buffer cache ?
Since your machine is obviously swapping a lot when it slows down
(you can verify that with vmstat), I think you should try first to lower RAM usage.
Use 'mysqladmin extended-status' and find out how much RAM you need for key_buffer
(check 'Key_blocks_used' variable) and reduce key_buffer value.
Hope this helps
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
