Re: Where am I wasting resources? How to fix this problem?
On Thursday 30 April 2009 21:49:13 Chuck Swiger wrote: > Hi, VeeJay-- > > On Apr 30, 2009, at 12:02 PM, VeeJay wrote: > > Guys, I am not very good on freebsd, its you guys who help me to > > keeping my > > server up... I hope you can spare a few minutes to sort this > > problem... > > > > last pid: 19656; load averages: 1.00, 1.00, > > 1.00 > > up 2+05:00:12 19:18:47 > > 3049 processes:2 running, 3047 sleeping > > CPU: 12.5% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle > > Mem: 6253M Active, 3810M Inact, 921M Wired, 128K Cache, 214M Buf, > > 4683M Free > > Swap: 32G Total, 32G Free > > > > PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU > > COMMAND > > 830 mysql 1500 440 1670M 813M ucond 1 0:00 100.00% > > mysqld > > It sure looks like you're running into a system limit with the maximum > # of threads available to the mysql process. There's no such limit, see pthread_create(3) and pthread.h: #define PTHREAD_THREADS_MAX __ULONG_MAX but the 1500 is suspicious. Suspicious enough to be a MySQL configuration value or compile time option. The only way to get to the bottom of it, is to watch the number of threads in the mysql process and attach ktrace to it the moment it approaches 1500, to see if pthread_create actually does return EAGAIN and get a hint as to where. My suspicion however is that the thread abstraction of MySQL sets EAGAIN. A my.cnf certainly would help. -- Mel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hi, VeeJay-- On Apr 30, 2009, at 12:02 PM, VeeJay wrote: Guys, I am not very good on freebsd, its you guys who help me to keeping my server up... I hope you can spare a few minutes to sort this problem... last pid: 19656; load averages: 1.00, 1.00, 1.00 up 2+05:00:12 19:18:47 3049 processes:2 running, 3047 sleeping CPU: 12.5% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle Mem: 6253M Active, 3810M Inact, 921M Wired, 128K Cache, 214M Buf, 4683M Free Swap: 32G Total, 32G Free PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU COMMAND 830 mysql 1500 440 1670M 813M ucond 1 0:00 100.00% mysqld It sure looks like you're running into a system limit with the maximum # of threads available to the mysql process. That's likely to be a consequence of some kind of query deadlock which is causing processes to get stuck and not be able to complete their work, resulting in subsequent requests also blocking until MySQL is no longer able to support more connection requests. There isn't enough information about what you're doing to really go much further-- something as simple as using MyISAM with full table- level locking versus InnoDB with row-level locking could be the problem, or you might have to take a closer look at the workload and outstanding queries. Regards, -- -Chuck ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hi, VeeJay-- On Apr 30, 2009, at 12:02 PM, VeeJay wrote: Guys, I am not very good on freebsd, its you guys who help me to keeping my server up... I hope you can spare a few minutes to sort this problem... last pid: 19656; load averages: 1.00, 1.00, 1.00 up 2+05:00:12 19:18:47 3049 processes:2 running, 3047 sleeping CPU: 12.5% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle Mem: 6253M Active, 3810M Inact, 921M Wired, 128K Cache, 214M Buf, 4683M Free Swap: 32G Total, 32G Free PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU COMMAND 830 mysql 1500 440 1670M 813M ucond 1 0:00 100.00% mysqld It sure looks like you're running into a system limit with the maximum # of threads available to the mysql process. That's likely to be a consequence of some kind of query deadlock which is causing processes to get stuck and not be able to complete their work, resulting in subsequent requests also blocking until MySQL is no longer able to support more connection requests. There isn't enough information about what you're doing to really go much further-- something as simple as using MyISAM with full table- level locking versus InnoDB with row-level locking could be the problem, or you might have to take a closer look at the workload and outstanding queries. Regards, -- -Chuck ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hi Mel I tried your suggestions by giving values vm.kmem_size_max="1024M" vm.kmem_size="1024M" but still got mysql hung without any luck here is the top output: even though there is plenty of free memory Guys, I am not very good on freebsd, its you guys who help me to keeping my server up... I hope you can spare a few minutes to sort this problem... last pid: 19656; load averages: 1.00, 1.00, 1.00 up 2+05:00:12 19:18:47 3049 processes:2 running, 3047 sleeping CPU: 12.5% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle Mem: 6253M Active, 3810M Inact, 921M Wired, 128K Cache, 214M Buf, 4683M Free Swap: 32G Total, 32G Free PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU COMMAND 830 mysql 1500 440 1670M 813M ucond 1 0:00 100.00% mysqld 19649 sshUser 1 440 16304K 8340K CPU1 1 0:00 0.39% top 18277 sshUser 1 440 26448K 2756K select 0 0:28 0.00% sshd 841 root 1 440 115M 17132K select 0 0:24 0.00% httpd 790 postfix 1 40 4600K 1972K kqread 1 0:07 0.00% qmgr 14353 apache1 40 117M 50896K sbwait 0 0:05 0.00% httpd 17564 apache1 40 119M 16084K sbwait 0 0:05 0.00% httpd 18320 sshUser 1 440 21560K 5920K select 1 0:04 0.00% sftp-server 14395 apache1 40 117M 51684K sbwait 0 0:04 0.00% httpd 782 root 1 40 4604K 1548K kqread 5 0:04 0.00% master 14456 apache1 40 120M 52440K sbwait 0 0:03 0.00% httpd 629 root 1 440 5688K 1252K select 2 0:02 0.00% syslogd 18687 apache1 40 116M 21528K sbwait 1 0:02 0.00% httpd 17759 apache1 40 118M 15852K sbwait 3 0:02 0.00% httpd 17561 apache1 40 117M 19368K sbwait 5 0:02 0.00% httpd 14609 apache1 40 116M 50696K sbwait 2 0:02 0.00% httpd 14623 apache1 40 117M 46444K sbwait 0 0:02 0.00% httpd 17599 apache1 40 118M 14884K sbwait 1 0:02 0.00% httpd 16262 apache1 40 119M 37000K sbwait 5 0:02 0.00% httpd 15176 apache1 40 119M 32660K sbwait 3 0:01 0.00% httpd 16147 apache1 40 118M 37540K sbwait 3 0:01 0.00% httpd 14624 apache1 40 118M 45248K sbwait 2 0:01 0.00% httpd 18696 apache1 40 116M 13544K sbwait 3 0:01 0.00% httpd 15694 apache1 40 118M 31304K sbwait 2 0:01 0.00% httpd 17442 apache1 40 118M 17872K sbwait 5 0:01 0.00% httpd 14621 apache1 40 117M 45496K sbwait 4 0:01 0.00% httpd 17412 apache1 40 118M 21500K sbwait 0 0:01 0.00% httpd 17504 apache1 40 118M 16052K sbwait 0 0:01 0.00% httpd 14626 apache1 40 118M 50984K sbwait 0 0:01 0.00% httpd 17448 apache1 40 119M 19772K sbwait 5 0:01 0.00% httpd 12204 apache1 40 116M 43588K sbwait 4 0:01 0.00% httpd 17560 apache1 40 118M 15300K sbwait 0 0:01 0.00% httpd 17729 apache1 40 119M 19828K sbwait 3 0:01 0.00% httpd 18017 apache1 40 119M 17808K sbwait 3 0:01 0.00% httpd 16802 apache1 40 118M 37124K sbwait 4 0:01 0.00% httpd 15640 apache1 40 118M 31864K sbwait 4 0:01 0.00% httpd 18411 apache1 40 119M 15084K sbwait 0 0:01 0.00% httpd -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hej Mel Well, I have removed these values kern.maxdsiz="4096M" kern.dfldsiz="2048M" kern.maxssiz="1024MB" and have added: vm.kmem_size_max="1024M" vm.kmem_size="1024M" having crossed my fingers and hoping, I don't get this error again Thanks for your help! -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
On Tuesday 28 April 2009 14:21:45 VeeJay wrote: > Hello Peter > > Thanks... I have tried the values but even after rebooting, I am still > getting the same old values as: > > server1# sysctl -a | grep maxdsiz > compat.ia32.maxdsiz: 536870912 > > server1# sysctl -a | grep maxssiz > compat.ia32.maxssiz: 67108864 It's a kenv(1) variable. Either way I don't think it's the problem. mysqld uses 1500 threads and many apache processes waiting for mysql to reply. You should figure out why that is, cause that sounds like a query that's holding a table lock and needing to sort the intermediate result set, stalling all other queries. If you really have ~1500 connections and consider that normal operation, then you may need more kernel memory. amd64 doesn't have a process memory limit (feature or bug I'm undecided on), so you can delete those. Instead set: vm.kmem_size_max="1024M" vm.kmem_size="1024M" -- Mel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
On Tuesday 28 April 2009 14:29:42 Wojciech Puchar wrote: > [r...@wojtek ~]# sysctl -a |grep maxpr > kern.maxproc: 5266 > kern.maxprocperuid: 4739 > > > i don't know if there is limit Not relevant. See pthread_create(): EAGAIN is returned for lack of kernel memory or going over PHTREAD_THREADS_MAX which is ULONG_MAX. 1500 threads isn't even close to USHORT_MAX. -- Mel ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
[r...@wojtek ~]# sysctl -a |grep maxpr kern.maxproc: 5266 kern.maxprocperuid: 4739 i don't know if there is limit ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hello Peter Thanks... I have tried the values but even after rebooting, I am still getting the same old values as: server1# sysctl -a | grep maxdsiz compat.ia32.maxdsiz: 536870912 server1# sysctl -a | grep maxssiz compat.ia32.maxssiz: 67108864 Even I tried both methods i.e. kern.maxdsiz="4096M" kern.dfldsiz="2048M" kern.maxssiz="1024MB" and later kern.maxdsiz="4294967296" kern.dfldsiz="2147483648" kern.maxssiz="1073741824" but still no change :( -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
VeeJay wrote: > Thanks Wojciech > > How can I find out the process count limit and how can I change it? > > And what is the maximum limit I can give with this hardware? > > Hello Veejay, Add thise to your loader.conf mysql# cat /boot/loader.conf kern.maxdsiz="4096M" kern.dfldsiz="2048M" kern.maxssiz="1024MB" and reboot the system. This should help. These are values from one my mysql boxes. It is also also in mysql notes for freebsd(see bottom) http://dev.mysql.com/doc/refman/5.1/en/freebsd.html Peter ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Thanks Wojciech How can I find out the process count limit and how can I change it? And what is the maximum limit I can give with this hardware? -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
#define EAGAIN 35 /* Resource temporarily unavailable */ check your process count limit. On Tue, 28 Apr 2009, VeeJay wrote: Sorry Folks, I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 And I am keep getting this error after a while "*1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug*" Then I am unable to shutdown the server or kill the processes not even connect through mysql client... -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org" ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Hi folks then if I check with the process: I get following output: # ps ax | grep mysqld 797 con- I 0:00.00 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/localhost.server1.pid 835 con- S 8:17.81 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/localhost.server1.pid --port=3306 -- I am running FreeBSD, Apache, MySQL on a Dell PowerEdge 2950 III having 2 x CPU 3,0 GHz Intel Xeon L5450 Quad-Core 2x6MB cache WITH 16 GB RAM Below is the output from my my.cnf, may be that can help to fix this problem: -start # The following options will be passed to all MySQL clients [client] port= 3306 socket= /tmp/mysql.sock default-character-set=utf8 # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 1024M max_allowed_packet = 16M table_cache = 1024 sort_buffer_size = 3M read_buffer_size = 12M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M user=mysql set-variable=local-infile=0 init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci bind-address=127.0.0.1 skip-innodb skip-name-resolve default-storage-engine = MyISAM wait_timeout = 60 log_slow_queries = /var/db/mysql/mysqld.slow.log long_query_time=3 log-queries-not-using-indexes connect_timeout=10 join_buffer=3M max_connections = 2000 query_cache_type = 1 query_cache_limit = 2M query_cache_size = 128M skip-name-resolve thread_cache_size = 8 thread_concurrency = 8 interactive_timeout=100 join_buffer_size=2M key_buffer_size=1024M max_connect_errors=1000 ft_min_word_len=2 ft_max_word_len=15 skip-networking log-bin=mysql-bin expire_logs_days=7 server-id= 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [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 -end -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Re: Where am I wasting resources? How to fix this problem?
Sorry Folks, I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 And I am keep getting this error after a while "*1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug*" Then I am unable to shutdown the server or kill the processes not even connect through mysql client... -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
Where am I wasting resources? How to fix this problem?
Hello guys and gurus I am keep getting this error after a while "*1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug*" Even though, I have 16GB memory and 32GB swap. But mysqlserver stops answering. Could you please help me to fix this problem? I am running FreeBSD on a Dell PowerEdge 2950 III having 2 x CPU 3,0 GHz Intel Xeon L5450 Quad-Core 2x6MB cache WITH 16 GB RAM Here is a snapshot from using top to have a overview of system resources... last pid: 27056; load averages: 1.05, 1.04, 1.01 up 3+16:22:44 09:01:53 3038 processes:2 running, 3036 sleeping CPU: 12.4% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle Mem: 6237M Active, 4005M Inact, 925M Wired, 40K Cache, 214M Buf, 4491M Free Swap: 32G Total, 32G Free PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU COMMAND 3010 mysql 1500 510 1643M 822M ucond 2 0:01 100.00% mysqld 27030 sshUser 1 440 16304K 8316K CPU0 0 0:01 0.39% top 26838 sshUser 1 450 26448K 2464K select 5 0:22 0.20% sshd 853 root 1 440 114M 19060K select 0 0:32 0.00% httpd 790 postfix 1 40 4600K 2120K kqread 2 0:12 0.00% qmgr 23542 apache1 40 118M 46520K sbwait 3 0:09 0.00% httpd 23420 apache1 40 118M 42340K sbwait 7 0:08 0.00% httpd 23543 apache1 40 118M 51032K sbwait 2 0:08 0.00% httpd 22853 apache1 40 118M 48384K sbwait 4 0:07 0.00% httpd 23768 apache1 40 118M 35432K sbwait 0 0:07 0.00% httpd 23748 apache1 40 118M 36560K sbwait 2 0:07 0.00% httpd 22861 apache1 40 118M 48420K sbwait 2 0:07 0.00% httpd 23618 apache1 40 117M 45696K sbwait 2 0:06 0.00% httpd 23222 apache1 40 118M 46080K sbwait 3 0:06 0.00% httpd 23700 apache1 40 118M 39572K sbwait 0 0:06 0.00% httpd 23534 apache1 40 118M 43984K sbwait 0 0:06 0.00% httpd 23439 apache1 40 118M 42980K sbwait 4 0:06 0.00% httpd 23480 apache1 40 118M 42724K sbwait 0 0:05 0.00% httpd 782 root 1 40 4604K 1552K kqread 2 0:05 0.00% master 26843 sshUser 1 440 21560K 5824K select 1 0:05 0.00% sftp-server 23066 apache1 40 118M 42328K sbwait 3 0:05 0.00% httpd 23619 apache1 40 118M 48012K sbwait 3 0:05 0.00% httpd 23224 apache1 40 118M 46436K sbwait 3 0:05 0.00% httpd 23220 apache1 40 118M 50776K sbwait 3 0:05 0.00% httpd 23176 apache1 40 118M 44956K sbwait 5 0:04 0.00% httpd 23467 apache1 40 118M 41692K sbwait 0 0:04 0.00% httpd 23294 apache1 40 116M 45552K sbwait 0 0:04 0.00% httpd 22884 apache1 40 118M 48596K sbwait 0 0:04 0.00% httpd 23214 apache1 40 118M 48508K sbwait 4 0:04 0.00% httpd 23177 apache1 40 118M 44844K sbwait 6 0:04 0.00% httpd 23278 apache1 40 117M 44812K sbwait 5 0:04 0.00% httpd 23497 apache1 40 117M 41612K sbwait 0 0:04 0.00% httpd 23477 apache1 40 118M 42332K sbwait 2 0:04 0.00% httpd 23371 apache1 40 118M 42176K sbwait 4 0:04 0.00% httpd 23563 apache1 40 118M 45096K sbwait 2 0:04 0.00% httpd 629 root 1 440 5688K 1252K select 0 0:04 0.00% syslogd 23119 apache1 40 118M 42088K sbwait 0 0:03 0.00% httpd Any help will be appreciated -- Thanks! BR / vj ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "freebsd-questions-unsubscr...@freebsd.org"
