RE: MySQL 4.0.2 is topping out at 1024 threads!
FIXED! Ok Thanks to Eric on this one, the wait_timeout configuration was what fixed my sleepy connection problems! Thanks ERIC! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eric Gunnett [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:37 AM To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! wait_timeout Will set the maximum amout of time a thread will be in the Sleep state before MySQL drops it. Eric Gunnett System Administrator Zoovy, Inc. [EMAIL PROTECTED] >>> "Matt Babineau" <[EMAIL PROTECTED]> 11/17/04 11:35AM >>> Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: > From: > Matt Babineau > <[EMAIL PROTECTED]> >To: > [EMAIL PROTECTED] > Subject: > RE: MySQL 4.0.2 is topping out at > 1024 threads! > Date: > Wed, 17 Nov 2004 11:18:04 -0800 > (16:18 CLST) > > This is a very strange problem. As you can see there isn't a lot going > on, under a million queries. No problem right? This is a dual cpu 2.8 > Ghz server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > Connection id: 25394 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.20-standard-log > Protocol version: 10 > Connection: 63.12.130.192 via TCP/IP > Client characterset:latin1 > Server characterset:latin1 > TCP port: 3306 > Uptime: 19 hours 40 min 2 sec > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush > tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- > > mysql> > > > == > > # Example mysql config file for very large systems. > # > # This is for large system with memory of 1G-2G where the system runs > mainly # MySQL. > # > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this # > installation this directory is /var/lib/mysql) or # ~/.my.cnf to set > user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients [client] > #password = your_password > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > connect_timeout = 10 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 > max_connections = 1024 max_user_connections = 1024 > > # Don't listen on a TCP/IP port at all. This can be a security > enhancement, # if all processes that need to connect to mysqld run on > the same host. > # All interaction with mysqld must be made via Unix sockets or named > pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! >
RE: MySQL 4.0.2 is topping out at 1024 threads!
Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: > From: > Matt Babineau > <[EMAIL PROTECTED]> >To: > [EMAIL PROTECTED] > Subject: > RE: MySQL 4.0.2 is topping out at > 1024 threads! > Date: > Wed, 17 Nov 2004 11:18:04 -0800 > (16:18 CLST) > > This is a very strange problem. As you can see there isn't a lot going > on, under a million queries. No problem right? This is a dual cpu 2.8 > Ghz server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > Connection id: 25394 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.20-standard-log > Protocol version: 10 > Connection: 63.12.130.192 via TCP/IP > Client characterset:latin1 > Server characterset:latin1 > TCP port: 3306 > Uptime: 19 hours 40 min 2 sec > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush > tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- > > mysql> > > > == > > # Example mysql config file for very large systems. > # > # This is for large system with memory of 1G-2G where the system runs > mainly # MySQL. > # > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this # > installation this directory is /var/lib/mysql) or # ~/.my.cnf to set > user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients [client] > #password = your_password > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > connect_timeout = 10 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 > max_connections = 1024 max_user_connections = 1024 > > # Don't listen on a TCP/IP port at all. This can be a security > enhancement, # if all processes that need to connect to mysqld run on > the same host. > # All interaction with mysqld must be made via Unix sockets or named > pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! > # > #skip-networking > > # Replication Master Server (default) > # binary logging is required for replication log-bin > > # required unique id between 1 and 2^32 - 1 # defaults to 1 if > master-host is not set # but will not function as a master if omitted > server-id = 1 > > # Replication Slave (comment out master section to use this) # # To > configure this host as a replication slave, you can choose between # > two methods : > # > # 1) Use the CHANGE MASTER TO command (fully described in our manual) > - > #the syntax is: > # > #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, > #MASTER_USER=, MASTER_PASSWORD
Re: MySQL 4.0.2 is topping out at 1024 threads!
In the last episode (Nov 17), Matt Babineau said: > This is a very strange problem. As you can see there isn't a lot going on, > under a million queries. No problem right? This is a dual cpu 2.8 Ghz > server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- So your real problem is not really the one you originally asked about? You now seem more interested in reducing the number of threads instead of raising the limit. Just run "show processlist", see what's taking up all your connections, and fix the clients :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: > From: > Matt Babineau > <[EMAIL PROTECTED]> >To: > [EMAIL PROTECTED] > Subject: > RE: MySQL 4.0.2 is topping out at > 1024 threads! > Date: > Wed, 17 Nov 2004 11:18:04 -0800 > (16:18 CLST) > > This is a very strange problem. As you can see there isn't a lot going > on, > under a million queries. No problem right? This is a dual cpu 2.8 Ghz > server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > Connection id: 25394 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.20-standard-log > Protocol version: 10 > Connection: 63.12.130.192 via TCP/IP > Client characterset:latin1 > Server characterset:latin1 > TCP port: 3306 > Uptime: 19 hours 40 min 2 sec > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush > tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- > > mysql> > > > == > > # Example mysql config file for very large systems. > # > # This is for large system with memory of 1G-2G where the system runs > mainly > # MySQL. > # > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this > # installation this directory is /var/lib/mysql) or > # ~/.my.cnf to set user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients > [client] > #password = your_password > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > connect_timeout = 10 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 > max_connections = 1024 > max_user_connections = 1024 > > # Don't listen on a TCP/IP port at all. This can be a security > enhancement, > # if all processes that need to connect to mysqld run on the same > host. > # All interaction with mysqld must be made via Unix sockets or named > pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! > # > #skip-networking > > # Replication Master Server (default) > # binary logging is required for replication > log-bin > > # required unique id between 1 and 2^32 - 1 > # defaults to 1 if master-host is not set > # but will not function as a master if omitted > server-id = 1 > > # Replication Slave (comment out master section to use this) > # > # To configure this host as a replication slave, you can choose > between > # two methods : > # > # 1) Use the CHANGE MASTER TO command (fully described in our manual) > - > #the syntax is: > # > #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, > #MASTER_USER=, MASTER_PASSWORD= ; > # > #where you replace , , by quoted strings > and > # by the master's port number (3306 by default). > # > #Example: > # > #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, > #MASTER_USER='joe', MASTER_PASSWORD='secret'; > # > # OR > # > # 2) Set the variables below. However, in case you choose this method, > then > #start replication for the first time (even unsuccessfully, for > example > #if you mistyped the password in master-password and the slave > fails to > #connect), the s
RE: MySQL 4.0.2 is topping out at 1024 threads!
he_size = 384M #bdb_max_lock = 10 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Thanks All! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 8:26 AM To: Gleb Paharenko Cc: [EMAIL PROTECTED] Subject: Re: MySQL 4.0.2 is topping out at 1024 threads! In the last episode (Nov 17), Gleb Paharenko said: > > Help! I can't figure out a way to stop my server from topping out at > > 1024 threads. This is a very strange behavoir. I have tons of legit > > use on my database server but I don't think the threads are dying > > does anyone have any suggestions for this? > > Similar problems are often found in lists. Usually they are solved by > increasing file limits. You likely need to increase open-files-limit. If you're running Linux, you may need to recompile your linuxthreads library also: http://dev.mysql.com/doc/mysql/en/Source_notes-Linux.html -- Dan Nelson [EMAIL PROTECTED] -- 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]
Re: MySQL 4.0.2 is topping out at 1024 threads!
In the last episode (Nov 17), Gleb Paharenko said: > > Help! I can't figure out a way to stop my server from topping out > > at 1024 threads. This is a very strange behavoir. I have tons of > > legit use on my database server but I don't think the threads are > > dying does anyone have any suggestions for this? > > Similar problems are often found in lists. Usually they are solved by > increasing file limits. You likely need to increase open-files-limit. If you're running Linux, you may need to recompile your linuxthreads library also: http://dev.mysql.com/doc/mysql/en/Source_notes-Linux.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.2 is topping out at 1024 threads!
Hello. Similar problems are often found in lists. Usually they are solved by increasing file limits. You likely need to increase open-files-limit. >Help! I can't figure out a way to stop my server from topping out at 1024 >threads. This is a very strange behavoir. I have tons of legit use on my >database server but I don't think the threads are dying does anyone have any >suggestions for this? > >Thanks, > >Matt Babineau >Web Developer >Criticalcode - http://www.criticalcode.com >"Matt Babineau" <[EMAIL PROTECTED]> wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.2 is topping out at 1024 threads!
Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
