Hello Noor,
I am struggling with a similar problem on a linux based system. It's a
Netfinity 4500R, dual pentium, RAID-5 with a rather new ServeRaid-4L
scsi controler.
On certain moments, mostly in the peak hours, mysql suddenly behaves
weird and the load increases very very quick. The swap used is also
increasing very quick (was that also the case on your system ?).
We use mysql in combination with Apache::Session which uses GET_LOCK. We
saw that no lock could be obtained, which could be the cause for an
increase og threads being used.
At the moment the wevserver and mysql server are restarted automically
when the number of threads is higher than 60 (by a cron job), which is a
clumbsy solution of course....
I have seen this problem with 3.23.29-beta, 3.23.30-gamma and 3.23.31.
I consider downgrading to 3.22.x :-/
Herald
Noor Dawod wrote:
> Hello all,
>
> I've been trying to fix a problem we're facing without luck for the last
> few weeks. I also searched mailing lists for a similar problem, both
> MySQL and FreeBSD, without luck.
>
> The situation is as follows... A brank new server was deployed last
> month for our company. It's a 933Mhz Xeon-based server, with 1GB of
> memory, a RAID-5 system utilizing 4 IBM SCSI 10K RPM LVD disks. As you
> can see, a very high-performance server for the Web. I installed FreeBSD
> 4.1.1 on it, then upgraded to 4.2-STABLE up to last week's snapshot.
> Currently, it has Apache 1.3.14 + mod_ssl, MySQL 3.23.31 and PHP
> 4.0.4pl1 -- as you can imagine, most RECENT versions.
>
> The problem persists, though, and here it is: every once in a while,
> sometimes once a week, the mysqld process goes into OVERHEAT state with
> CPU usage getting as high as 97%, thus making it almost impossible for
> clients to connect to it. I checked mysqld's process usage once while
> this happened, and I found around 60 processes (connections). Not a big
> deal as I saw much more processes connect to the same daemon before, and
> still it continued to work.
>
> Anyways, this problem keeps bugging me all the time. I look at lists
> over and over and cannot understand why this is happening. I've even
> tried limiting Apache to a minimum of connections (sometimes as low as
> 300 maximum requests for the whole Apache daemons), but it didn't
> help -- eventualy, mysqld would go into this almost-dead state and no
> more Web pages would be served correctly.
>
> I'm attaching the my.cnf file for you to examine. Remember, this is the
> stock my.cnf file that came with MySQL for large Web sites (those having
> more than 512MB of memory), with a little modification.
>
> I'd appreciate any hint or fix that you might have, and I would even let
> serious bug-hunters/mysql gurus get a shell on our server to try to
> check and fix this problem.
>
> Thanks in advance.
>
> Noor
>
>
> ------------------------------------------------------------------------
>
> # This is for large system with memory = 512M where the system runs mainly
> # MySQL.
>
> [client]
> #password = your_password
> port = 3306
> socket = /mysql/mysql.socket
>
> [mysqld]
> port = 3306
> socket = /mysql/mysql.socket
> pid-file = /mysql/mysql.pid
> tmpdir = /mysql/tmp/
> skip-locking
> skip-name-resolve
> log
> set-variable = key_buffer=256M
> 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=64M
> set-variable = thread_cache=8
> set-variable = thread_concurrency=2 # Try number of CPU's*2
> set-variable = max_connections=200
> #log-bin
> #server-id = 1
>
> # Uncomment the following if you are using BDB tables
> #set-variable = bdb_cache_size=64M
> #set-variable = bdb_max_lock=100000
>
> # Point the following paths to different dedicated disks
> #tmpdir = /tmp/
> #log-update = /path-to-dedicated-directory/hostname
>
> [mysqldump]
> quick
> set-variable = max_allowed_packet=16M
>
> [mysql]
> no-auto-rehash
> #safe-updates # Remove the comment character if you are not familiar with SQL
>
> [isamchk]
> set-variable = key_buffer=128M
> set-variable = sort_buffer=128M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [myisamchk]
> set-variable = key_buffer=128M
> set-variable = sort_buffer=128M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [mysqlhotcopy]
> interactive-timeout
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> 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
> my.cnf.txt
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> attachment.txt
>
> Content-Type:
>
> text/plain
>
>
---------------------------------------------------------------------
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
