Re: Crashing under high load
Jeremy Zawodny wrote: On Fri, Aug 02, 2002 at 05:41:09PM -0500, Shane Allen wrote: The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. That's not normal at all. MySQL shouldn't care about the system load. It may run *slower* but it shouldn't die. Generally if it dies. Its because the operating system reached some limit. Often if you look at the hostname.err file in /var/lib/mysql there will be some clue as to what resource you ran out of. Some possibilities are o Virtual memory (swap space) o Open file descriptors o Address space o Stack space I think you need to examine your system when its underload and see if any of these resources are near exhaustion. Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
Our application (Win32) gives MySQL a real pounding, but we have never had it die on a server with proper resources. On Win32 systems, you can not underestimate the importance of virtual memory (swap file) and drive defragmentation. On Win32 systems with SCSI RAID, we set the virtual memory to 20% or more of the total disk storage capacity. If the customer insists on cheaper EIDE drives, we install three ... two in a normal mirrored config, and a third drive that contains nothing more than a big swap file. We make the swap files static (same min and max) to reduce file fragmentation. We no longer support servers that are not running DisKeeper (full version ... not the crippled one in M$ distributions). Gerald Jensen - Original Message - From: Mike Wexler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Shane Allen [EMAIL PROTECTED]; Georg Richter [EMAIL PROTECTED] Sent: Saturday, August 03, 2002 7:14 PM Subject: Re: Crashing under high load Jeremy Zawodny wrote: On Fri, Aug 02, 2002 at 05:41:09PM -0500, Shane Allen wrote: The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. That's not normal at all. MySQL shouldn't care about the system load. It may run *slower* but it shouldn't die. Generally if it dies. Its because the operating system reached some limit. Often if you look at the hostname.err file in /var/lib/mysql there will be some clue as to what resource you ran out of. Some possibilities are o Virtual memory (swap space) o Open file descriptors o Address space o Stack space I think you need to examine your system when its underload and see if any of these resources are near exhaustion. Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
Shane, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 03, 2002 2:21 AM Subject: Re: Crashing under high load On Fri, Aug 02, 2002 at 05:41:09PM -0500, Shane Allen wrote: The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. it is not normal for MySQL or any other program to die under a high load. I regularly run stress tests on MySQL/InnoDB on our 4-way Linux-2.4.16 server, and a load of 20 is common in those tests. Tim Perdue's article in PHPBuilder is old. And I am not sure he knew about the problem of glibc allocating memory over thread stacks in Linux. The most common cause of crashing under a high load is that on Linux the memory allocation of MySQL approaches 2 GB. There is a bug in glibc which allows glibc to allocate memory over thread stacks without giving any error message. Other common causes are buggy disk drivers. In your my.cnf max_connections has a very big value 2500. You have compiled MySQL yourself, but have not set thread stack size lower than the default 2 MB? Then just the thread stacks can occupy 5 GB of memory. Sort buffer is set to 5 MB. Under a high load sort queries can pile up and eat up 12.5 GB of memory. You should try setting sort_buffer and record_buffer to 1M. . [mysqld] skip-locking set-variable= max_connections=2500 set-variable= query_cache_size=64M set-variable= wait_timeout=3600 set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=3000 # NOTE: Record buffer and Sort buffer are NOT shared among threads # They are per-thread memory buffers. set-variable= sort_buffer=5M set-variable= record_buffer=5M .. That's not normal at all. MySQL shouldn't care about the system load. It may run *slower* but it shouldn't die. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,352,421,911 queries (240/sec. avg) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Crashing under high load
I recently read an article (on PHPBuilder; it's fairly old) that spoke of MySQL dying under high load. Recently (since I built 3.23.51 and on my test build of 4.0.2) I have had problems with MySQL dying (got signal 11, et al.) under extreme high load. For example, if I set up a 60-thread spider, and run it in our test environment (1 front end web server, 1 database server, mysql 4.0.2 alpha compiled with a custom glib-2.2.5 + MySQL patch to linuxthreads)(heavily database driven), I can crash MySQL after only 30 minutes or so. The sad part is, we haven't hit the 2500 connection limit that I've set. I've tried checking the physical resources on the boxen, and MySQL doesn't appear to have consumed all the resources on the box at this point, although the load average when MySQL dies is usually around 15. I've been tweaking values, and found that increasing the table_cache from our previous setting of 400 to 3000 has caused *vastly* improved stability, however I cannot imagine that a table_cache adjustment should prevent a crash... I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? Additionally, I'd love to know: Is it likely that something in my build is causing mysqld to crash (given that there are no other problems whatsoever with the build)? Since I increased the table cache it has become more stable -- is this the expected behaviour? Should the increased table_cache have made any difference at all? *** I don't want to ask leading questions, but... is it possible that we are hitting, say 200 processes, and when they all start using their 5M sort buffers (see below) that we're running out of memeory and causing MySQL to die? If so, is there any way to avoid having MySQL die? I'd rather a query take 3 minutes than kill the daemon... *** Below is my MySQL config, and some other pertinent data. Please let me know if I can provide any further information to help answer above questions. Also, if anyone has advice on how to make MySQL more stable, please let me know. Thanks in advance. --Other stuff-- MySQL built with: root@local-dbmaster-test:/usr/local/src/mysql-4.0.2-alpha/BUILD# ./compile-pentium --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local glibc-2.2.5 built with: root@local-dbmaster-test:/usr/local/src/glibc-2.2.5# ./configure --enable-add-ons=linuxthreads --prefix=/usr/local/mysql-glibc-2.2.5 --enable-kernel=2.4.18 gcc-2.95.4 (debian stable package) was used to build both kernel is running patched to enable a higher open file limit per process (up to 4096) --/etc/my.cnf-- [mysqld] skip-locking set-variable= max_connections=2500 set-variable= query_cache_size=64M set-variable= wait_timeout=3600 set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=3000 # NOTE: Record buffer and Sort buffer are NOT shared among threads # They are per-thread memory buffers. set-variable= sort_buffer=5M set-variable= record_buffer=5M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= long_query_time=4 tmpdir=/data/tmp user=mysql #log=/usr/local/var/mysqld.log log-slow-queries=/usr/local/var/slow-queries.log pid-file=/usr/local/var/mysqld.pid server-id=1 #master-host=host #master-user=user #master-password=secret #log-bin #binlog-do-db=db [mysql] set-variable= max_allowed_packet=1M [mysqldump] set-variable= max_allowed_packet=1M [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] open-files-limit=8192 err-log=/usr/local/var/mysqld.err -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
On Friday, 2. August 2002 20:48, Shane Allen wrote: Hi, I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? you have a lot of programs, which prefer to die under heavy load. The main question is not why they die, the question is why is the load so high? I hope you already analyzed your system, your mysql-server and your queries and can could give us some more additional information. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
On Fri, Aug 02, 2002 at 10:59:18PM +0200, Georg Richter wrote: On Friday, 2. August 2002 20:48, Shane Allen wrote: I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? you have a lot of programs, which prefer to die under heavy load. The main question is not why they die, the question is why is the load so high? I hope you already analyzed your system, your mysql-server and your queries and can could give us some more additional information. Well, most programs, in my experience, will keep running, even if load is 200, they just run slowly since they aren't getting processor load. As for why things are busy on the box, well, we run a fairly popular site... we have a primary database server that handles a large majority of our queries. Discussions about setting up a cluster or some other such thing aside, this server handles something to the tune of 3000 queries every five minutes during our off hours. During peak times, it's hit over 100k queries in five minutes, where quires == selects + updates + inserts, etc. We have spent a good deal of time lately optimizing what few queries were not already as good as they can be, but because of the large amounts of data we're dealing with, we sometimes join across 5 tables -- not much we can do about that. We're also performing a fairly extensive audit of our processes to reduce the number of necessary queries. We're also running MySQL databases on all of our front-end webservers that store non-changing data, like ISO country codes and such. All these things are intended to keep the load as low as possible. So, clustering aside (it's not an option right now), I am concerned about why MySQL dies... syslog and ntpd, for example, doesn't die under high load. On our front-end webservers, neither Apache nor cron die. So why is MySQL dying? Which takes me back to my original set of questions... The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. So, if there is additional information that you would like in order to help us solve this problem, I will be happy to provide it. What would you like to know. Again, thanks in advance for any help you can provide. -- Shane Allen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
Well, most programs, in my experience, will keep running, even if load is 200, they just run slowly since they aren't getting processor load. How's the memory? Typically things (especially Mysql) croak if they bonk on memory - if you're into the swap on high load, you're screwed. -- - Eric Anderson Wild Web Amateurs CyberIron Bodybuilding ICQ 3849549 http://www.wildwebamateurs.com http://www.cyberiron.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Crashing under high load
On Fri, Aug 02, 2002 at 05:41:09PM -0500, Shane Allen wrote: The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. That's not normal at all. MySQL shouldn't care about the system load. It may run *slower* but it shouldn't die. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,352,421,911 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
