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 DatabaseCommand 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
