[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: mysql connections - how to stop flooding?
From:       "Brent Baisley" <brenttech () gmail ! com>
Date:       2007-04-30 13:08:49
Message-ID: 047401c78b28$b245a590$1c02a8c0 () BrentWD
[Download RAW message or body]

My first guess would be that you have a MyISAM table that gets hit with a long \
running query which locks the table. When that  happens, all other queries start \
queueing up and connections will rise as new queries come in. Once the long running \
query finishes,  all the other queued queries run and finish and then connections \
drop. I'm sure why Apache hits 100% cpu usage, although if my first guess is correct, \
then the Apache box has just as many connections  open as the db box.


----- Original Message ----- 
From: "hydn79" <hayden.james@pinstack.com>
To: <mysql@lists.mysql.com>
Sent: Sunday, April 29, 2007 3:38 PM
Subject: mysql connections - how to stop flooding?


> 
> I have an issue. Both with mysql pconnect enabled and disabled.
> 
> Randomly, between every 5 mins or ever few hours the connections will jump
> by as much as 100 connections of more in the span of a a few seconds (5 - 15
> seconds usually) then return back to normal.
> 
> However, during this peak/flood of connections. Apache (on seperate box)
> hits 100% cpu usage until the connects normalize. Also the apache httpd.exe
> RAM usuage increases at the same time.
> 
> Question.
> 1) Does this sound like a mysql, php or apache issue?
> 2) Is there a config in either my.ini, php.ini or httpd.conf to stop this
> flooding?
> 
> Related Specs
> Two identical servers one with apache and the other DB
> Both with 3GB RAM. Max RAM usage around 35%
> 2x 5140 P4 dual core
> 3x 36GB 15k SCSI RAID 1 (one drive runs OS and 2 drives on Raid)
> 
> My.ini
> ----------------------------------
> [client]
> port=3306
> 
> [mysql]
> default-character-set=latin1
> safe-user-create
> skip-show-database
> 
> [mysqld]
> port=3306
> basedir="D:/mysql/"
> datadir="D:/mysql/Data/"
> default-character-set=latin1
> default-storage-engine=myisam
> max_connections=600
> connect_timeout=10
> wait_timeout=10
> max_connect_errors=10
> key_buffer_size=256M
> tmp_table_size=256M
> table_cache=2048
> max_allowed_packet=16M
> myisam_max_sort_file_size=100k
> myisam_max_extra_sort_file_size=100k
> thread_cache_size=256
> myisam_sort_buffer_size=64M
> read_buffer_size=2M
> join_buffer_size=1M
> sort_buffer_size=1M
> read_rnd_buffer_size=1M
> query_cache_limit=8M
> query_cache_size=128M
> query_cache_type=1
> query_prealloc_size=163840
> query_alloc_block_size=32768
> 
> innodb_additional_mem_pool_size=10M
> innodb_flush_log_at_trx_commit=1
> innodb_log_buffer_size=6M
> innodb_buffer_pool_size=128M
> innodb_log_file_size=100M
> innodb_thread_concurrency=4
> innodb_file_io_threads=4
> 
> open_files_limit=2048
> bulk_insert_buffer_size=8M
> max_heap_table_size=256M
> 
> safe-user-create
> skip-show-database
> old-passwords
> 
> #Don't cache host names.
> skip-host-cache
> #Don't resolve hostnames. All hostnames are IP's or 'localhost'.
> skip-name-resolve
> 
> 
> [mysqldump]
> quick
> max_allowed_packet = 32M
> 
> [myisamchk]
> key_buffer = 128M
> sort_buffer = 128M
> read_buffer = 64M
> write_buffer = 64M
> 
> [mysqlhotcopy]
> interactive-timeout
> 
> Thanks,
> -- 
> View this message in context: \
> http://www.nabble.com/mysql-connections---how-to-stop-flooding--tf3666522.html#a10244744
>  Sent from the MySQL - General mailing list archive at Nabble.com.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]