Thank you Matt, Jeremy and Gerald for helping me. It seems that the code is opening many connections. I have decreased "wait_timeout" and it should be better for the time being.
Thanks and Regards, Shailesh Agrawal -- "For sale, parachute, used once, never opened, small stain." -----Original Message----- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 7:22 AM To: Jobs PHP Workshop; [EMAIL PROTECTED] Subject: Re: MySQL process increasing problem.. Hi, Yes, in top, those are threads not processes, as Gerald already said. Not that it really matters. :-) Your connections in PROCESSLIST are sleeping (e.g. idle). What are the clients? It looks like you may be using persistent connections with a Web app. If you want to get rid of the sleeping threads (which shouldn't be much harm, except for a bit of memory usage), don't use persistent connections, or lower the wait_timeout and/or interactive_timeout server variables to have MySQL disconnect idle clients sooner. Hope that helps. Matt ----- Original Message ----- From: "Jobs PHP Workshop" To: <[EMAIL PROTECTED]> Sent: Wednesday, November 05, 2003 11:08 AM Subject: MySQL process increasing problem.. > Hi, > Problem is, Mysql server in our production machine keeps spawning child > processes and it reaches to a level where mysql hangs and the only > solution is to restart our mysql server. I have checked all the > databases using "mysqlcheck" command and every table is OK now. > Recently, MySQL server suffered from low disk space and hence some of > the tables got corrupted. We have repaired them using "mysqlcheck -r" > option. Can this be a Reason of the mysqld process spawning problem ??. > Some help will really save me as this is a production machine. We have > MySQL 3.23.x running and Following is more technical details that you > may need to think upon: > > (1) HERE IS THE PARTIAL OUTPUT OF 'TOP' COMMAND: > > 2:52pm up 1 day, 22:34, 2 users, load average: 0.30, 0.09, 0.03 134 > processes: 133 sleeping, 1 running, 0 zombie, 0 stopped CPU states: > 0.9% user, 2.7% system, 0.0% nice, 96.3% idle > Mem: 1028316K av, 1015484K used, 12832K free, 0K shrd, 180292K > buff > Swap: 522072K av, 4096K used, 517976K free 470328K > cached > > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND > 6097 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld > 6099 mysql 8 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld > 6100 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld > 6105 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:03 mysqld > 6108 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld > 6109 mysql 9 0 23004 22M 1528 S 0.0 2.2 0:00 mysqld > > > (2) HERE IS THE PARTIAL LIST OF PROCESSES OUT OF TOTAL 94 AS A RESULT OF > 'SHOW PROCESSLIST' COMMAND.REST OTHER PROCESSES ARE ALSO SIMILAR: > > id User Host Database Command Time Status > SQL-query > > ---------------------------------------------------------------------- -- > --- > 131 u125 localhost db125 Sleep 6243 --- --- > 139 csha localhost db_cshaw_com Sleep 2494 --- --- > 140 tanb localhost db_tban_com Sleep 1985 --- --- > 141 tanb localhost db_tban_com Sleep 2006 --- --- > 142 tanb localhost db_tban_com Sleep 1977 --- --- > 143 tanb localhost db_tban_com Sleep 1948 --- --- > 144 tanb localhost db_tban_com Sleep 1939 --- --- > 145 tanb localhost db_tban_com Sleep 1410 --- --- > 146 tanb localhost db_tban_com Sleep 1394 --- --- > 147 dbpr localhost dbpropertydb Sleep 1092 --- --- > 154 root localhost mysql Query 0 --- SHOW > PROCESSLIST > > (3) HERE IS THE 'extended-status' of MySQL: > > +--------------------------+----------+ > | Variable_name | Value | > +--------------------------+----------+ > | Aborted_clients | 1 | > | Aborted_connects | 0 | > | Bytes_received | 6271826 | > | Bytes_sent | 18499758 | > | Connections | 158 | > | Created_tmp_disk_tables | 105 | > | Created_tmp_tables | 1174 | > | Created_tmp_files | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Delayed_errors | 0 | > | Flush_commands | 1 | > | Handler_delete | 283 | > | Handler_read_first | 618 | > | Handler_read_key | 598750 | > | Handler_read_next | 600043 | > | Handler_read_prev | 0 | > | Handler_read_rnd | 20400 | > | Handler_read_rnd_next | 21962199 | > | Handler_update | 1035 | > | Handler_write | 22201 | > | Key_blocks_used | 15582 | > | Key_read_requests | 1403805 | > | Key_reads | 85703 | > | Key_write_requests | 5742 | > | Key_writes | 4974 | > | Max_used_connections | 88 | > | Not_flushed_key_blocks | 0 | > | Not_flushed_delayed_rows | 0 | > | Open_tables | 64 | > | Open_files | 131 | > | Open_streams | 0 | > | Opened_tables | 20961 | > | Questions | 63778 | > | Select_full_join | 1288 | > | Select_full_range_join | 0 | > | Select_range | 0 | > | Select_range_check | 0 | > | Select_scan | 7348 | > | Slave_running | OFF | > | Slave_open_temp_tables | 0 | > | Slow_launch_threads | 0 | > | Slow_queries | 0 | > | Sort_merge_passes | 0 | > | Sort_range | 0 | > | Sort_rows | 20400 | > | Sort_scan | 1405 | > | Table_locks_immediate | 49707 | > | Table_locks_waited | 1 | > | Threads_cached | 0 | > | Threads_created | 157 | > | Threads_connected | 88 | > | Threads_running | 1 | > | Uptime | 18794 | > +--------------------------+----------+ > > Thanks.., > Shailesh Agrawal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
