RESOLVED Re: Mystifying mysqld memory usage explosion
Tim: Can you bring your libc to the latest patch level? Not necessary. I resolved the problem: binlog_cache_size was set to 32MB I didn't realise that this would automatically be allocated to every thread, even if there are no InnoDB or BDB tables in the entire instance. This explains why --skip-innodb fixed the problem; without InnoDB, MySQL knew that there would be no transactions occurring, and so would not need the binlog cache for each thread. The documentation probably needs clarifying that this is another per-thread buffer, and it is always allocated to every connection thread if the server supports transactional table types. As a followup question; what happens to the binlog cache if a thread requires more? Does it automatically increase it as needed (up to an eventual limit of max_binlog_cache_size)? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESOLVED Re: Mystifying mysqld memory usage explosion
At 12:23 + 3/26/04, Tim Cutts wrote: Tim: Can you bring your libc to the latest patch level? Not necessary. I resolved the problem: binlog_cache_size was set to 32MB I didn't realise that this would automatically be allocated to every thread, even if there are no InnoDB or BDB tables in the entire instance. This explains why --skip-innodb fixed the problem; without InnoDB, MySQL knew that there would be no transactions occurring, and so would not need the binlog cache for each thread. The documentation probably needs clarifying that this is another per-thread buffer, and it is always allocated to every connection thread if the server supports transactional table types. Yes, thanks for pointing this out. As a followup question; what happens to the binlog cache if a thread requires more? Does it automatically increase it as needed (up to an eventual limit of max_binlog_cache_size)? Yes. http://www.mysql.com/doc/en/Binary_log.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 05:01, Sasha Pachev wrote: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. That's what I thought. Based on the test results you have reported, I would put your libc as the primary suspect, I'm using the system libc which comes with Tru64 5.1B and the next one would be bad build/compiler bugs. I'm using the binary build as supplied by MySQL. I'm going to try compiling mysql myself, and see whether that makes any difference. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. Perhaps... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 06:31, Heikki Tuuri wrote: you can use the command SHOW INNODB STATUS; to check how much memory InnoDB has allocated in total. Please report what it says at the time of the memory explosion. Well, that was informative, but in a negative sort of way. SHOW INNODB STATUS produced identical output when run immediately after server startup and when 50 idle connections were in place, and the virtual memory consumption had increased by around 2GB. You report that even 100 IDLE connections cause the memory explosion. I agree with Sasha that this probably is not a MySQL/InnoDB bug. I have not heard of a similar memory problem from anyone else. No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 10:10, Tim Cutts wrote: No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... The version compiled natively on the machine does the same thing (although it uses a little less memory to start with since it's not statically linked). I suppose the next thing to try is the debug version. I've tried compiling the debug version myself without success, so I'll download the debug version from MySQL and try that. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
Tim Cutts wrote: On 25 Mar 2004, at 10:10, Tim Cutts wrote: No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... The version compiled natively on the machine does the same thing (although it uses a little less memory to start with since it's not statically linked). I suppose the next thing to try is the debug version. I've tried compiling the debug version myself without success, so I'll download the debug version from MySQL and try that. Tim: Can you bring your libc to the latest patch level? -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
I'm not a big mysql expert but I think mysql has some buffers which it keeps per connection here are some examples (from mysql website): / /If you have complex queries |sort_buffer_size| and |tmp_table_size| are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points. / Note: These are per connection values, among |read_buffer_size|, |read_rnd_buffer_size| and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example |sort_buffer_size| is allocated only if MySQL nees to do a sort. Note: be careful not to run out of memory. / I might start up mysql and watch the memory usage as you start dumb clients on at a time - see what the step in memory usage is and match it up against something in your my.cnf good luck yonah Tim Cutts wrote: On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
Tim Cutts wrote: On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. Based on the test results you have reported, I would put your libc as the primary suspect, and the next one would be bad build/compiler bugs. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
Tim, - Original Message - From: Sasha Pachev [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 25, 2004 7:04 AM Subject: Re: Mystifying mysqld memory usage explosion Tim Cutts wrote: On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. Based on the test results you have reported, I would put your libc as the primary suspect, and the next one would be bad build/compiler bugs. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. you can use the command SHOW INNODB STATUS; to check how much memory InnoDB has allocated in total. Please report what it says at the time of the memory explosion. You report that even 100 IDLE connections cause the memory explosion. I agree with Sasha that this probably is not a MySQL/InnoDB bug. I have not heard of a similar memory problem from anyone else. -- Sasha Pachev Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
