RE: Troubleshoot excessive memory usage in InnoDB
Memory leaks are unlikely, but possible. Upgrade to a newer version. Killing threads that say Sleep _may_ free up some memory, but unlikely to be more than even 1MB each. It _may_ cause grief for the developers, if they haven't bulletproofed their code enough to handle lost connection. Mostly that frees up thread_stack= 192K Once you have followed the advice in http://mysql.rjweb.org/doc.php/memory there is not much more that an infrastructure guy can do. I have roots in many sides of this issue. Once I have tuned a system, I turn to the queries, schema, overall architecture, etc. Some caches act like there is a memory leak. What happens is that they grow as needed, up to some specified limit. This is especially visible for key_buffer_size. Query_cache_size = 256M may be hurting performance; I recommend no more than 50M. (The link explains.) Until the system starts swapping, there should be no problem with the growing memory usage. At that point, performance will tank. The quick fix is to decrease innodb_buffer_pool_size and/or key_buffer_size. If you provide SHOW GLOBAL STATUS and SHOW VARIABLES, I can look for other issues. -Original Message- From: Denis Jedig [mailto:d...@syneticon.net] Sent: Saturday, April 20, 2013 2:16 AM To: mysql@lists.mysql.com Subject: Re: Troubleshoot excessive memory usage in InnoDB 19.04.2013 23:39, Ilya Kazakevich: Try to use tuning-primer.sh: this scripts reads your variables and prints memory size you need for that. I tried that. The results are inconspicious: MEMORY USAGE Max Memory Ever Allocated : 5.27 G Configured Max Per-thread Buffers : 1.92 G Configured Max Global Buffers : 5.15 G Configured Max Memory Limit : 7.07 G Physical Memory : 22.98 G Max memory limit seem to be within acceptable norms Although the logics behind the tuning primer script are rather simple and I understand predicting the memory usage for MySQL is much harder: http://www.mysqlperformanceblog.com/2012/03/21/troubleshooting-mysql- memory-usage/ 20.04.2013 00:26, Rick James: What's the STATUS value of Threads_running? If it really is ~60-100 connection threads, then there could be any of a few temp allocations for the queries. Some allocations are per-subquery. Usually around 2-4. I also tried checking if killing / resetting existing (idle) connections would significantly reduce memory usage when mysqld has reached ~20 GB - it would not, so this is either not related to connection states or the memory is leaking from there in a way which would be unaffected by closing the connection. Is the system I/O bound? Or CPU bound? Or neither? Neither - the system has plenty of headroom for both. The data working set easily fits into the RAM, the amount of UPDATEs is negligible (resulting in 100 write requests per second for the I/O subsystem). 1-minute load average is 2-3 under normal (non-swapping) conditions with 6 CPU cores available. I recommend you optimize the queries. I cannot do much about it. I am the infrastructure guy who is fixing the obviously broken DBMS. What I still cannot figure out is if the behavior is due to a misconfiguration or a regression / bug to file. And MySQL counters are not exactly helping - it is completely opaque to me where the memory is going. -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Troubleshoot excessive memory usage in InnoDB
19.04.2013 23:39, Ilya Kazakevich: Try to use tuning-primer.sh: this scripts reads your variables and prints memory size you need for that. I tried that. The results are inconspicious: MEMORY USAGE Max Memory Ever Allocated : 5.27 G Configured Max Per-thread Buffers : 1.92 G Configured Max Global Buffers : 5.15 G Configured Max Memory Limit : 7.07 G Physical Memory : 22.98 G Max memory limit seem to be within acceptable norms Although the logics behind the tuning primer script are rather simple and I understand predicting the memory usage for MySQL is much harder: http://www.mysqlperformanceblog.com/2012/03/21/troubleshooting-mysql-memory-usage/ 20.04.2013 00:26, Rick James: What's the STATUS value of Threads_running? If it really is ~60-100 connection threads, then there could be any of a few temp allocations for the queries. Some allocations are per-subquery. Usually around 2-4. I also tried checking if killing / resetting existing (idle) connections would significantly reduce memory usage when mysqld has reached ~20 GB - it would not, so this is either not related to connection states or the memory is leaking from there in a way which would be unaffected by closing the connection. Is the system I/O bound? Or CPU bound? Or neither? Neither - the system has plenty of headroom for both. The data working set easily fits into the RAM, the amount of UPDATEs is negligible (resulting in 100 write requests per second for the I/O subsystem). 1-minute load average is 2-3 under normal (non-swapping) conditions with 6 CPU cores available. I recommend you optimize the queries. I cannot do much about it. I am the infrastructure guy who is fixing the obviously broken DBMS. What I still cannot figure out is if the behavior is due to a misconfiguration or a regression / bug to file. And MySQL counters are not exactly helping - it is completely opaque to me where the memory is going. -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Troubleshoot excessive memory usage in InnoDB
Hi all. In a specific MySQL installation of 5.6.10 using InnoDB tables, I am observing unusual memory consumption patterns. The memory usage is growing constantly - even beyond the physical memory limits. The entire on-disk storage is 41 GB (uncompressed), yet memory usage is happily growing to values larger than 50 GB. The databases mainly experience read load with complex queries and subSELECTs running ~60-100 connection threads. Although the docs state that there should be no memory leaks, this case certainly looks like one at first glance. http://dev.mysql.com/doc/refman/5.6/en/memory-use.html suggests that temporary in-memory tables would be used for this purpose so I suspected unfreed temporary tables to be the culprit. But memory usage growth rates did not change significantly even after lowering tmp_table_size to 2M (from 64M). Also, I have been unable to find a way to determine the size of in-memory temporary tables at any given time. Some of the STATUS counters: | Com_select| 424614 | | Com_update| 3444| | Created_tmp_disk_tables | 1716| | Created_tmp_files | 43 | | Created_tmp_tables| 4002| | Uptime| 5112| The total number of tables over all databases is 1370. my.cnf contains the following memory-related values: max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 max_connections= 1000 innodb_buffer_pool_size = 5000M innodb_log_file_size= 256M innodb_flush_method = O_DIRECT query_cache_limit = 1M query_cache_size= 256M join_buffer_size= 256k tmp_table_size = 2M max_heap_table_size = 64M read_buffer_size= 1M ft_min_word_len = 3 open_files_limit= 1 A replication slave of this very host is running 5.6.10 with MyISAM tables and the mysqld process does not exceed 1 GB in memory utilization even after several hours of operation under similar load. I have posted a question to http://dba.stackexchange.com/questions/40413 which I will update with further information as I get it. Any hints on how to hunt the resource hog greatly appreciated, -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Troubleshoot excessive memory usage in InnoDB
Hello, Try to use tuning-primer.sh: this scripts reads your variables and prints memory size you need for that. Here is example of its output: MEMORY USAGE Max Memory Ever Allocated : 2.86 G Configured Max Per-thread Buffers : 1.80 G Configured Max Global Buffers : 2.10 G Configured Max Memory Limit : 3.91 G Physical Memory : 5.82 G I am not sure if it works correctly with 5.6 Ilya -Original Message- From: Denis Jedig [mailto:d...@syneticon.net] Sent: Saturday, April 20, 2013 1:17 AM To: mysql@lists.mysql.com Subject: Troubleshoot excessive memory usage in InnoDB Hi all. In a specific MySQL installation of 5.6.10 using InnoDB tables, I am observing unusual memory consumption patterns. The memory usage is growing constantly - even beyond the physical memory limits. The entire on-disk storage is 41 GB (uncompressed), yet memory usage is happily growing to values larger than 50 GB. The databases mainly experience read load with complex queries and subSELECTs running ~60-100 connection threads. Although the docs state that there should be no memory leaks, this case certainly looks like one at first glance. http://dev.mysql.com/doc/refman/5.6/en/memory-use.html suggests that temporary in-memory tables would be used for this purpose so I suspected unfreed temporary tables to be the culprit. But memory usage growth rates did not change significantly even after lowering tmp_table_size to 2M (from 64M). Also, I have been unable to find a way to determine the size of in-memory temporary tables at any given time. Some of the STATUS counters: | Com_select| 424614 | | Com_update| 3444| | Created_tmp_disk_tables | 1716| | Created_tmp_files | 43 | | Created_tmp_tables| 4002| | Uptime| 5112| The total number of tables over all databases is 1370. my.cnf contains the following memory-related values: max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 max_connections= 1000 innodb_buffer_pool_size = 5000M innodb_log_file_size= 256M innodb_flush_method = O_DIRECT query_cache_limit = 1M query_cache_size= 256M join_buffer_size= 256k tmp_table_size = 2M max_heap_table_size = 64M read_buffer_size= 1M ft_min_word_len = 3 open_files_limit= 1 A replication slave of this very host is running 5.6.10 with MyISAM tables and the mysqld process does not exceed 1 GB in memory utilization even after several hours of operation under similar load. I have posted a question to http://dba.stackexchange.com/questions/40413 which I will update with further information as I get it. Any hints on how to hunt the resource hog greatly appreciated, -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Troubleshoot excessive memory usage in InnoDB
What's the STATUS value of Threads_running? If it really is ~60-100 connection threads, then there could be any of a few temp allocations for the queries. Some allocations are per-subquery. 5.6 has a lot of new tricks for optimizing certain subqueries -- such as testing out all possible indexes, then creating the optimal one. Is the system I/O bound? Or CPU bound? Or neither? I recommend you optimize the queries. Provide us with EXPLAIN for the query you see most often in SHOW PROCESSLIST, together with SHOW TABLE STATUS and SHOW CREATE TABLE. The solution may be as easy as adding an index or turning a subquery into a JOIN. Granted, that would not help to nail down the suspected memory leak. -Original Message- From: Ilya Kazakevich [mailto:ilya.kazakev...@jetbrains.com] Sent: Friday, April 19, 2013 2:40 PM To: 'Denis Jedig'; mysql@lists.mysql.com Subject: RE: Troubleshoot excessive memory usage in InnoDB Hello, Try to use tuning-primer.sh: this scripts reads your variables and prints memory size you need for that. Here is example of its output: MEMORY USAGE Max Memory Ever Allocated : 2.86 G Configured Max Per-thread Buffers : 1.80 G Configured Max Global Buffers : 2.10 G Configured Max Memory Limit : 3.91 G Physical Memory : 5.82 G I am not sure if it works correctly with 5.6 Ilya -Original Message- From: Denis Jedig [mailto:d...@syneticon.net] Sent: Saturday, April 20, 2013 1:17 AM To: mysql@lists.mysql.com Subject: Troubleshoot excessive memory usage in InnoDB Hi all. In a specific MySQL installation of 5.6.10 using InnoDB tables, I am observing unusual memory consumption patterns. The memory usage is growing constantly - even beyond the physical memory limits. The entire on-disk storage is 41 GB (uncompressed), yet memory usage is happily growing to values larger than 50 GB. The databases mainly experience read load with complex queries and subSELECTs running ~60-100 connection threads. Although the docs state that there should be no memory leaks, this case certainly looks like one at first glance. http://dev.mysql.com/doc/refman/5.6/en/memory-use.html suggests that temporary in-memory tables would be used for this purpose so I suspected unfreed temporary tables to be the culprit. But memory usage growth rates did not change significantly even after lowering tmp_table_size to 2M (from 64M). Also, I have been unable to find a way to determine the size of in-memory temporary tables at any given time. Some of the STATUS counters: | Com_select| 424614 | | Com_update| 3444| | Created_tmp_disk_tables | 1716| | Created_tmp_files | 43 | | Created_tmp_tables| 4002| | Uptime| 5112| The total number of tables over all databases is 1370. my.cnf contains the following memory-related values: max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 max_connections= 1000 innodb_buffer_pool_size = 5000M innodb_log_file_size= 256M innodb_flush_method = O_DIRECT query_cache_limit = 1M query_cache_size= 256M join_buffer_size= 256k tmp_table_size = 2M max_heap_table_size = 64M read_buffer_size= 1M ft_min_word_len = 3 open_files_limit= 1 A replication slave of this very host is running 5.6.10 with MyISAM tables and the mysqld process does not exceed 1 GB in memory utilization even after several hours of operation under similar load. I have posted a question to http://dba.stackexchange.com/questions/40413 which I will update with further information as I get it. Any hints on how to hunt the resource hog greatly appreciated, -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Memory Usage.
How can I know how memory is being used by Mysql? I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even that I have tables over 40GB... Thanks! xD
Re: Memory Usage.
Am 25.04.2011 16:24, schrieb Andrés Tello: How can I know how memory is being used by Mysql? I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even that I have tables over 40GB... Thanks! xD depends on storage-engine (myisam or innodb), buffer-sizes, size of the query-cache, size of keys - the target is not to use the full memory, the target is use available momory wise search for mysqltuner.pl very interesting is the memory per connection (depends on many buffer-params) becuase with a little mistake the possible max usage can easily be some hundret GB! [--] Up for: 17d 21h 53m 13s (39M q [25.329 qps], 132K conn, TX: 60B, RX: 19B) [--] Reads / Writes: 67% / 33% [--] Total buffers: 6.1G global + 1.2M per thread (500 max threads) [OK] Maximum possible memory usage: 6.7G (66% of installed RAM) [OK] Slow queries: 0% (13/39M) [OK] Highest usage of available connections: 72% (362/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/74.1M [OK] Key buffer hit rate: 97.6% (109M cached / 2M reads) [OK] Query cache efficiency: 73.8% (23M cached / 32M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (247 temp sorts / 731K sorts) [OK] Temporary tables created on disk: 0% (1K on disk / 1M total) [OK] Thread cache hit rate: 99% (362 created / 132K connections) [!!] Table cache hit rate: 8% (131 open / 1K opened) [OK] Open file limit used: 0% (67/30K) [OK] Table locks acquired immediately: 99% (18M immediate / 18M locks) [!!] InnoDB data size / buffer pool: 16.6G/5.0G -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
RE: Memory Usage.
-Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 25, 2011 10:24 AM To: Mailing-List mysql Subject: Memory Usage. How can I know how memory is being used by Mysql? I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even that I have tables over 40GB... Thanks! xD [JS] The amount of memory used will be the smallest of 1. Available physical memory 2. Usable memory (this is a limitation built into the code design, both at OS level and at the MySQL/storage engine level) 3. Allowed memory (a configuration setting) 4. Needed memory 1 and 2 are usually easy to find out. When it comes to 4, things get very murky. As someone else said, the goal is to use memory wisely. That means finding a trade-off between efficient use of memory and speed. Once you understand and make some choices with 4, you can go back and tinker with 3. As any performance consultant worth his salt will tell you, It depends. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query for memory usage
Hello, How to query the memory usage for mysql server? for example, how much memory mysqld has used? and how much memory available for mysqld? The OS is Linux. Thanks. Young. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
quick question on mysql memory usage
Hi, My understanding is that the memory utilization of mysql can be calculated roughly using the formula like: (All global memory related server variables + max_connections * session memory related server variables) As I noticed that most global variables like key_buffer_size, innodb_buffer_szie will not return back the memory to OS since they are using (mt)malloc/free, My question is: will mysql return the memory to the OS after closing some connections? Such as, when one connection executing a query which located sort buffer, and created tempory table, after complete the query or close the query, will the memory used by this query release to OS? Thanks in advance for your info. Regards, jenny
Re: mysqld memory usage
Quoth Walter Heck li...@olindata.com: You could bring it down, but the real question is if you really want to do that? Making the buffers and caches smaller will reduce the memory used, but it also reduces performance. Noted. Could you tell us what you are hoping to use MySQL for and why you wanna bring the memory usage down? WordPress blogs. Why, because my VPS is very low-powered. I have PostgreSQL installed as well and it uses less than 1.5% of memory when idle. Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqld memory usage
Quoth wult...@gmail.com: Through your conf file(s) you have told MySQL how much memory it may consume. As long as the server does not go beyond what it is told it may consume it is not doing anything wrong. Thanks. I'll have a look in the conf file. At the moment it's running as it came out of the box. The server will not return all memory when idle because various things will be held in several caches. Start hacking your configuration file if you wish to decrease idle consumption. Noted. Also, 5.0 is very broad. Somerhing like 5.0.67 on 64 bit intel is way more informitive. Most people have not migrated to 5.1 yet, and many people are using versions older than 5.0 . Fair enough. It's 5.0.51a-21 on 32 bit Intel Xeon. Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqld memory usage
So I assume you have a terabyte of RAM in the server, since you didn't say... OMG, it's using 143GB of RAM when it's idle? Wow.. :-) You need to provide some more details here. I can't judge whether there is any issue at all. Baron On Sun, Feb 1, 2009 at 8:35 AM, Sebastian Tennant seb...@smolny.plus.com wrote: Hi all, I recently installed MySQL (version 5.0) on my Debian Lenny VPS and mysqld uses 14.3% of memory when idle. Is this a known issue? I'm aware that version 5.0 is not the latest version but it's the one currently shipped by Debian Lenny (testing) so I'm loathe to 'manually' install a later version. Is there anything I can do to bring this figure down? Any advice/tips/pointers much appreciated. Regards, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqld memory usage
Quoth Baron Schwartz ba...@xaprb.com: So I assume you have a terabyte of RAM in the server, since you didn't say... OMG, it's using 143GB of RAM when it's idle? Wow.. :-) You need to provide some more details here. I can't judge whether there is any issue at all. Sorry. My VPS has 144 MB of RAM so mysqld is using about ~ 21 MB when idle. Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqld memory usage
Hi all, I recently installed MySQL (version 5.0) on my Debian Lenny VPS and mysqld uses 14.3% of memory when idle. Is this a known issue? I'm aware that version 5.0 is not the latest version but it's the one currently shipped by Debian Lenny (testing) so I'm loathe to 'manually' install a later version. Is there anything I can do to bring this figure down? Any advice/tips/pointers much appreciated. Regards, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqld memory usage
You could bring it down, but the real question is if you really want to do that? Making the buffers and caches smaller will reduce the memory used, but it also reduces performance. Could you tell us what you are hoping to use MySQL for and why you wanna bring the memory usage down? Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Sun, Feb 1, 2009 at 2:35 PM, Sebastian Tennant seb...@smolny.plus.com wrote: Hi all, I recently installed MySQL (version 5.0) on my Debian Lenny VPS and mysqld uses 14.3% of memory when idle. Is this a known issue? I'm aware that version 5.0 is not the latest version but it's the one currently shipped by Debian Lenny (testing) so I'm loathe to 'manually' install a later version. Is there anything I can do to bring this figure down? Any advice/tips/pointers much appreciated. Regards, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: memory usage
Hi Joerg, Thanks a lot for the info. regards anandkl On 7/23/08, Joerg Bruehe [EMAIL PROTECTED] wrote: Hi ! Ananda Kumar wrote: Hi All, I have setup slave db. The machine configuration details of this slave is same as master. OS=redhat 8 cpu 16GB RAM key_buffer_size=3000M innodb_buffer_pool_size=1M. But when i do top, in the master db Cpu(s): 0.5%us, 0.3%sy, 0.0%ni, 87.2%id, 11.9%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16436956k total, 16350252k used,86704k free, 9188k buffers Swap: 16386292k total,37232k used, 16349060k free, 2358944k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 28706 mysql 15 0 14.3g 13g 4688 S6 84.1 540:21.55 mysqld On slave db Cpu(s): 0.7%us, 0.2%sy, 0.0%ni, 87.3%id, 11.8%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16436956k total, 16351536k used,85420k free,16400k buffers Swap: 16386292k total, 164k used, 16386128k free, 4289520k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 14042 mysql 18 0 14.0g 11g 4652 S7 72.5 265:08.62 mysqld 435 root 10 -5 000 S0 0.0 3:29.07 kswapd0 As you can see the RES in master is 13g, but on slave its 11G any specific reason for this. This is causing some of the sql's on the slave to be slower than master for the same select statement on both master and slave. RES is the amount of RAM used by the process, as controlled by the memory allocation (Linux kernel). It depends not only on the address space requested by the process, but also on those by other processes and on the paging / swapping policy (which AFAIK depends on the which process accesses memory how frequent). The MySQL process on the slave has a smaller RES if and only if there is reason for the Linux kernel to allocate less RAM, this typically means there are other processes requiring it more urgently. *If* the database load on the master is higher, or accesses more data, or there is less competition about the RAM, it is only natural that RES for the slave is less. Database performance strongly depends on caching: If the cache on the master already contains the needed pages, and on the slave it doesn't, it is normal that the first statement needing them has to wait for disk I/O and so is slower. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
Re: memory usage
Hi ! Ananda Kumar wrote: Hi All, I have setup slave db. The machine configuration details of this slave is same as master. OS=redhat 8 cpu 16GB RAM key_buffer_size=3000M innodb_buffer_pool_size=1M. But when i do top, in the master db Cpu(s): 0.5%us, 0.3%sy, 0.0%ni, 87.2%id, 11.9%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16436956k total, 16350252k used,86704k free, 9188k buffers Swap: 16386292k total,37232k used, 16349060k free, 2358944k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 28706 mysql 15 0 14.3g 13g 4688 S6 84.1 540:21.55 mysqld On slave db Cpu(s): 0.7%us, 0.2%sy, 0.0%ni, 87.3%id, 11.8%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16436956k total, 16351536k used,85420k free,16400k buffers Swap: 16386292k total, 164k used, 16386128k free, 4289520k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 14042 mysql 18 0 14.0g 11g 4652 S7 72.5 265:08.62 mysqld 435 root 10 -5 000 S0 0.0 3:29.07 kswapd0 As you can see the RES in master is 13g, but on slave its 11G any specific reason for this. This is causing some of the sql's on the slave to be slower than master for the same select statement on both master and slave. RES is the amount of RAM used by the process, as controlled by the memory allocation (Linux kernel). It depends not only on the address space requested by the process, but also on those by other processes and on the paging / swapping policy (which AFAIK depends on the which process accesses memory how frequent). The MySQL process on the slave has a smaller RES if and only if there is reason for the Linux kernel to allocate less RAM, this typically means there are other processes requiring it more urgently. *If* the database load on the master is higher, or accesses more data, or there is less competition about the RAM, it is only natural that RES for the slave is less. Database performance strongly depends on caching: If the cache on the master already contains the needed pages, and on the slave it doesn't, it is normal that the first statement needing them has to wait for disk I/O and so is slower. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory usage
Hi All, I have setup slave db. The machine configuration details of this slave is same as master. OS=redhat 8 cpu 16GB RAM key_buffer_size=3000M innodb_buffer_pool_size=1M. But when i do top, in the master db Cpu(s): 0.5%us, 0.3%sy, 0.0%ni, 87.2%id, 11.9%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16436956k total, 16350252k used,86704k free, 9188k buffers Swap: 16386292k total,37232k used, 16349060k free, 2358944k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 28706 mysql 15 0 14.3g 13g 4688 S6 84.1 540:21.55 mysqld On slave db Cpu(s): 0.7%us, 0.2%sy, 0.0%ni, 87.3%id, 11.8%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16436956k total, 16351536k used,85420k free,16400k buffers Swap: 16386292k total, 164k used, 16386128k free, 4289520k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 14042 mysql 18 0 14.0g 11g 4652 S7 72.5 265:08.62 mysqld 435 root 10 -5 000 S0 0.0 3:29.07 kswapd0 As you can see the RES in master is 13g, but on slave its 11G any specific reason for this. This is causing some of the sql's on the slave to be slower than master for the same select statement on both master and slave. regards anandkl
memory usage - mysql tuning!!
Hi.. Fairly new to mysql, in particular tuning. I have a test mysql db, on a test server. I've got a test app that runs on multiple servers, with each test app, firing/accessing data from the central db server. the central server is on a 2GHz, 1GMem, 100G system. MySQL is the basic app. the remote/test apps are doing basic selects/inserts, with a few basic select.. group/order by. the db schema appears to be pretty straight forward, with primary/unique fields. keep in mind, i'm not a dba!!! the my.cnf file is pretty basic. there has been a modification for the key_buffer_table entry... my issue, is that when i examine the central mysql (show processlist) i see a number of connections (~10) with the majority being in a sleep status.. However, when i then check the OS, using top, i see that mysql is running, consuming ~ 80-90% of the cpu cycles... so, i'm trying to figure out how to diagnose/solve this issue. any pointers, comments, suggestions will be greatly appreciated. this instance of mysql, is 5.x, and is running on a virtual rhel5 os, under vmware... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: memory usage - mysql tuning!!
bruce a écrit : Hi.. Fairly new to mysql, in particular tuning. I have a test mysql db, on a test server. I've got a test app that runs on multiple servers, with each test app, firing/accessing data from the central db server. the central server is on a 2GHz, 1GMem, 100G system. MySQL is the basic app. the remote/test apps are doing basic selects/inserts, with a few basic select.. group/order by. the db schema appears to be pretty straight forward, with primary/unique fields. keep in mind, i'm not a dba!!! the my.cnf file is pretty basic. there has been a modification for the key_buffer_table entry... my issue, is that when i examine the central mysql (show processlist) i see a number of connections (~10) with the majority being in a sleep status.. However, when i then check the OS, using top, i see that mysql is running, consuming ~ 80-90% of the cpu cycles... so, i'm trying to figure out how to diagnose/solve this issue. any pointers, comments, suggestions will be greatly appreciated. this instance of mysql, is 5.x, and is running on a virtual rhel5 os, under vmware... thanks Hi, if mysql is the only program running on your test server it's normal that it's using 80-90% of the used cpu cycles ... Is it using 80% of the total CPU cycles or juste 80% of the used Cpu cyles? If your Cpu is running at 0.05 and mysql at 80% it means that mysql is just using 4% of the system's CPU, mysql has to listen to new incomming queries even when there are none so it's normal that it uses up some CPU ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory usage in relation to open connections
hello, i'm searching for some information about how many system memory mysql uses in relation to the open connections. in the last weeks we often had the problem of too many connections, but before raising the configuration value, we would like to calculate a maximum amount of used memory. the system has 1GB ram, and according to statistics most of it is already in use. current value of max_connections for MySQL is 160. are there any algorithms to calculate the average/maximum memory usage? thanks in advance, jonas PS: i'm not subscribed to the list, please Cc me in your replies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Memory Usage on Windows? Re: Replication still stopping...
as i can see you are running mysql on windows. If i start my db server (5.0.45/innodb/win2k) the server uses about ~80K handles (as seen in taskmgr) and memory usage increases around 1g. Taskmgr.exe says that there is some swapping (the box has only 1gb ram). The DB itself is small (~50mb or so). My Question is, did you have the same things on your box? Did you have performace issues which resultes from the memory usage? I can't even keep it running for longer that 24 hours, and I don't know why I haven't even started looking into memory issues or performance. When it is runnning, as a test, I change a record on the master, and I notice that almost immediately, the same change is made on the slave. Works perfectly for a few hours, then it just stops working. It almost appears to be a network related issue, but I can't seem to track it down. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] Memory Usage on Windows? Re: Replication still stopping...
Hi Jesse, 071020 14:43:51 InnoDB: Started; log sequence number 0 142497221 071020 14:43:51 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. as i can see you are running mysql on windows. If i start my db server (5.0.45/innodb/win2k) the server uses about ~80K handles (as seen in taskmgr) and memory usage increases around 1g. Taskmgr.exe says that there is some swapping (the box has only 1gb ram). The DB itself is small (~50mb or so). My Question is, did you have the same things on your box? Did you have performace issues which resultes from the memory usage? Thanks Ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Thread Memory Usage
Hello all, I am looking for a way to view the max values that have been used by all threads or a signal thread for read_buffer, read_rnd_buffer, sort_buffer, and net_buffer. I want to know these values so that I can turn them appropriately. Regards, --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax: 260.459.6271
Memory usage question
We very recently began replicating data from a master to a slave and since doing that we've noticed that most of the RAM in the machine 2 GB is being used with very little (relatively) free (12MB - 50MB). I've looked at several forums and have done some web searches to see if there was any mention of this but haven't seen anything. Is this a normal occurrence? Is there some kind of tuning that I can do to free up more memory? Thanks for listening. Steve
Re: Memory usage question
In the last episode (Sep 22), Blumenkrantz, Steve said: We very recently began replicating data from a master to a slave and since doing that we've noticed that most of the RAM in the machine 2 GB is being used with very little (relatively) free (12MB - 50MB). I've looked at several forums and have done some web searches to see if there was any mention of this but haven't seen anything. Is this a normal occurrence? Is there some kind of tuning that I can do to free up more memory? It's normal. Free memory is wasted memory. The OS will use what memory is not allocated by processes as disk cache. A better indicator that you are low on memory is high swap usage and swapin/swapouts per second. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB memory usage clarification
The following are from the InnoDB configuration page: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. | glibc| may allow the process heap to grow over thread stacks, which crashes your server. Can someone please explain what this means. We have a 32bit Linux x86 server with 16gigs of ram. Because it is 32bit and not 64bit we cant really make much use of all the ram. I am wondering which values I can safely increase without crashing the server. Here are some of the parameters we are using in our conf file: thread_concurrency = 16 table_cache = 512 innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M From SHOW INNODB STATUS: BUFFER POOL AND MEMORY -- Total memory allocated 462835472; in additional pool allocated 3569664 Buffer pool size 24576 Free buffers 0 Database pages 23956 Modified db pages 11531 Free buffers is 0. Someone mentioned that because its a quad xeon each CPU would have 2gigs of ram to work with. Does this mean that I can set the innodb buffer pool much higher ? any feedback is welcome. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB memory usage clarification
Mayuran, It depends on a bunch of things. What else is running on this server? Does the distro you use have the ability to take advantage of all 16 gig (ie if you have apache running, will it be stuck in the same 4 gig as MySQL, or can it use the memory above the 4 gig limit). How big is your database? The innodb_buffer_pool_size holds data from your database in memory; if you run a query, and the data is in the buffer_pool, the query returns very quickly. If it is not in the buffer_pool, then MySQL/InnoDB has to go to disk to get the data. If your database is 100 megabytes, there is not much sense in setting a buffer_pool of 1 gigabyte. If your database is 10 gigabytes, then you will probably encounter some slowness as the disk is being accessed. How many users will connect? Each user requires some memory for the connection, for sorting, etc, etc. The following equation gives you an idea of how much memory MySQL will consume, based on various parameters: innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB If you try to grab too much, mysql will crash. Check your distribution to figure out what the max process size is. David Mayuran Yogarajah wrote: The following are from the InnoDB configuration page: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. | glibc| may allow the process heap to grow over thread stacks, which crashes your server. Can someone please explain what this means. We have a 32bit Linux x86 server with 16gigs of ram. Because it is 32bit and not 64bit we cant really make much use of all the ram. I am wondering which values I can safely increase without crashing the server. Here are some of the parameters we are using in our conf file: thread_concurrency = 16 table_cache = 512 innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M From SHOW INNODB STATUS: BUFFER POOL AND MEMORY -- Total memory allocated 462835472; in additional pool allocated 3569664 Buffer pool size 24576 Free buffers 0 Database pages 23956 Modified db pages 11531 Free buffers is 0. Someone mentioned that because its a quad xeon each CPU would have 2gigs of ram to work with. Does this mean that I can set the innodb buffer pool much higher ? any feedback is welcome. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Memory Usage
On Tuesday 10 August 2004 05:25 pm, Jeremy Zawodny wrote: On Thu, Aug 05, 2004 at 10:27:54AM -0500, Sashi Ramaswamy wrote: Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. I suspect most of that is shared memory, not private. I am not sure I understand. Could you please elaborate? How do I get MySQL to release this memory? Thanks for your help. Sashi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Memory Usage
On Thu, Aug 05, 2004 at 10:27:54AM -0500, Sashi Ramaswamy wrote: Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. I suspect most of that is shared memory, not private. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
High Memory Usage
Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. Any ideas on how to fix this problem? I tried flushing logs, query cache, etc but nothing seems to help. Thanks for you help. Sashi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Mystifying mysqld memory usage explosion
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 I'm presuming that I have some configuration variable wrong somewhere, but I can't work out what it is. Here's our .cnf file for the instance: [mysqld_safe] err-log=/mysql/log_3365/err.log [mysqladmin] socket=/mysql/data_3365/mysql_3365.sock port=3365 [mysqld] binlog_cache_size=32M datadir=/mysql/data_3365/databases interactive_timeout=2678200 key_buffer=1024M #log=/mysql/log_3365/query.log log_bin=/mysql/log_3365/bin.log log_slow_queries=/mysql/log_3365/slow.log log_warnings max_allowed_packet=16M max_binlog_size=2000M max_connections=1024 net_write_timeout=60 pid-file=/mysql/data_3365/mysql_3365.pid port=3365 query_cache_size=32M read_buffer_size=256K socket=/mysql/data_3365/mysql_3365.sock sort_buffer_size=2M table_cache=512 thread_cache_size=16 wait_timeout=2678200 # Replication options server_id=1 I've tried reducing sort_buffer_size to a pathetic 32K, and it makes no difference, so it's not that. Similarly, I've tried reducing max_allowed_packet, and that makes no difference. The code in question is running a large number of compute jobs on a Linux cluster, and these jobs talk to the MySQL server both to inform a master control process what is going on, and secondly to store their results in it. If I constrain the number of simultaneously running jobs to 20, then MySQL only grows to about 3.5 GB, 2.5 GB of which were allocated as soon as it started, so it looks like each connection is allocating around 50 MB inside MySQL, but I don't know where this is coming from. Surely each thread within the OS doesn't take 50 MB before it's allocated anything else? Any ideas, including ways I can get MySQL to tell me more about what it's doing, would be most helpful. The query log, even with log-warnings on, does not tell us much. Many thanks in advance... 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: memory usage probs
In case anyone else encounters this particular symptom, it turns out the
problem was gcc using some orphaned headers for mysql 3.23.56 sitting in
/usr/include/mysql rather than the correct mysql 4.0.17 ones residing in
/usr/local/include/mysql, thus yielding all the strange behaviour.
M.
On Sat, 20 Mar 2004, Matthew Hodgson wrote:
Hi,
I've been having problems with segfaults under mod_auth_mysql in Apache
1.3, which I think i've narrowed down to the MYSQL connection structure
getting corrupted on my particular mysql installation - specifically
manifesting itself with strange values of the free_me field, which results
in the structure being incorrectly free'd.
If anyone can confirm that the following shows something fundamentally
wrong with my mysql install i'd appreciate it hugely:
box 130% cat test.c
#include mysql/mysql.h
int main() {
MYSQL *m;
m = mysql_init(0);
printf(free_me is %d\n, m-free_me);
return 0;
}
box 131% gcc -g -L/usr/local/lib/mysql -lmysqlclient -lm -o foo test.c
box 132% ./foo
free_me is 0
This is using libmysqlclient.so.12.0.0 on a P4 Xeon running linux 2.4.22
with libc 2.3.1; mysql 4.0.17 built from source with:
./configure --prefix=/opt/mysql-4.0.17
--localstatedir=/usr/local/var/mysql --without-innodb --without-docs
--without-bench --with-mysqld-user=mysql
The code in libmysql.c appears to say:
mysql_init(MYSQL *mysql) {
if (!mysql) {
malloc mysql
mysql-free_me=1;
}
...
}
so I'm at a complete loss to explain why m-free_me appears as 0 in the
above mini example.
Meanwhile, precisely the same program on a dual PIII machine running
Debian Woody, libmysqlclient 12.0.0 to match 4.0.16 yields:
deb 30% ./foo
free_me is 1
Superficially other operations seem to work - but segfaults ensue on
mysql_close(); and gdb reveals several fields of the connection structure
to change radically between simple operations. For instace running a
mysql_select_db() sets the value of free_me to 0x03, amongst others:
freshly inited mysql_handle, populated with some settings:
(gdb) print *mysql_handle
$1 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728
, buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000
out of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0
'\0', no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities
= 139395456, protocol_version = 0, field_count = 0,
server_status = 1074172704, thread_id = 0, affected_rows = 0, insert_id = 0,
extra_info = 0, packet_length = 0, status = MYSQL_STATUS_READY,
fields = 0x0, field_alloc = {free = 0x0, used = 0x0, pre_alloc = 0x0, min_malloc =
0, block_size = 0, error_handler = 0x2f43},
free_me = 0 '\0', reconnect = 0 '\0', options = {connect_timeout = 3306,
client_flag = 8197, compress = 44 ',', named_pipe = 0 '\0',
port = 10, host = 0x0, init_command = 0x2 Address 0x2 out of bounds, user =
0x8 Address 0x8 out of bounds, password = 0x0,
unix_socket = 0x0, db = 0x0, my_cnf_file = 0x0, my_cnf_group = 0x0, charset_dir
= 0x0, charset_name = 0x0, use_ssl = 0 '\0',
ssl_key = 0x0, ssl_cert = 0x0, ssl_ca = 0x0, ssl_capath = 0x0}, scramble_buff =
\0\0\0\0\0\0\0\0, charset = 0x0, server_language = 0}
(gdb) step
506 if (mysql_select_db(mysql_handle,m-mysqlDB) != 0) {
(gdb) print *mysql_handle
$2 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728
, buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000
out of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0
'\0', no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities
= 139395456, protocol_version = 0,
field_count = 139395592, server_status = 1074172704, thread_id = 0, affected_rows
= 0, insert_id = 0, extra_info = 0, packet_length = 0,
status = MYSQL_STATUS_READY, fields = 0x, field_alloc = {free =
0x, used =
memory usage probs
Hi,
I've been having problems with segfaults under mod_auth_mysql in Apache
1.3, which I think i've narrowed down to the MYSQL connection structure
getting corrupted on my particular mysql installation - specifically
manifesting itself with strange values of the free_me field, which results
in the structure being incorrectly free'd.
If anyone can confirm that the following shows something fundamentally
wrong with my mysql install i'd appreciate it hugely:
box 130% cat test.c
#include mysql/mysql.h
int main() {
MYSQL *m;
m = mysql_init(0);
printf(free_me is %d\n, m-free_me);
return 0;
}
box 131% gcc -g -L/usr/local/lib/mysql -lmysqlclient -lm -o foo test.c
box 132% ./foo
free_me is 0
This is using libmysqlclient.so.12.0.0 on a P4 Xeon running linux 2.4.22
with libc 2.3.1; mysql 4.0.17 built from source with:
./configure --prefix=/opt/mysql-4.0.17
--localstatedir=/usr/local/var/mysql --without-innodb --without-docs
--without-bench --with-mysqld-user=mysql
The code in libmysql.c appears to say:
mysql_init(MYSQL *mysql) {
if (!mysql) {
malloc mysql
mysql-free_me=1;
}
...
}
so I'm at a complete loss to explain why m-free_me appears as 0 in the
above mini example.
Meanwhile, precisely the same program on a dual PIII machine running
Debian Woody, libmysqlclient 12.0.0 to match 4.0.16 yields:
deb 30% ./foo
free_me is 1
Superficially other operations seem to work - but segfaults ensue on
mysql_close(); and gdb reveals several fields of the connection structure
to change radically between simple operations. For instace running a
mysql_select_db() sets the value of free_me to 0x03, amongst others:
freshly inited mysql_handle, populated with some settings:
(gdb) print *mysql_handle
$1 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728 ,
buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000 out
of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0 '\0',
no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities =
139395456, protocol_version = 0, field_count = 0,
server_status = 1074172704, thread_id = 0, affected_rows = 0, insert_id = 0,
extra_info = 0, packet_length = 0, status = MYSQL_STATUS_READY,
fields = 0x0, field_alloc = {free = 0x0, used = 0x0, pre_alloc = 0x0, min_malloc =
0, block_size = 0, error_handler = 0x2f43},
free_me = 0 '\0', reconnect = 0 '\0', options = {connect_timeout = 3306, client_flag
= 8197, compress = 44 ',', named_pipe = 0 '\0',
port = 10, host = 0x0, init_command = 0x2 Address 0x2 out of bounds, user = 0x8
Address 0x8 out of bounds, password = 0x0,
unix_socket = 0x0, db = 0x0, my_cnf_file = 0x0, my_cnf_group = 0x0, charset_dir =
0x0, charset_name = 0x0, use_ssl = 0 '\0',
ssl_key = 0x0, ssl_cert = 0x0, ssl_ca = 0x0, ssl_capath = 0x0}, scramble_buff =
\0\0\0\0\0\0\0\0, charset = 0x0, server_language = 0}
(gdb) step
506 if (mysql_select_db(mysql_handle,m-mysqlDB) != 0) {
(gdb) print *mysql_handle
$2 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728 ,
buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000 out
of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0 '\0',
no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities =
139395456, protocol_version = 0,
field_count = 139395592, server_status = 1074172704, thread_id = 0, affected_rows =
0, insert_id = 0, extra_info = 0, packet_length = 0,
status = MYSQL_STATUS_READY, fields = 0x, field_alloc = {free = 0x,
used = 0x0, pre_alloc = 0x0, min_malloc = 0,
block_size = 0, error_handler = 0x2f43}, free_me = 3 '\003', reconnect = 0 '\0',
options = {connect_timeout = 3306, client_flag = 8197,
compress = 44 ',', named_pipe = 0 '\0', port = 10, host = 0x0, init_command = 0x2
Address 0x2 out of bounds,
user = 0x8 Address 0x8 out of bounds, password = 0x0, unix_socket = 0x0, db =
0x0, my_cnf_file = 0x0, my_cnf_group = 0x0,
RE: mysql memory usage
Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... Alexis P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
Hi, In every instance I've seen, MySQL always allocates the amount you set for key_buffer at server startup even if it never comes close to being *used*. (It shouldn't be doing malloc()s or whatever for that on the fly. :-)) Same thing for query_cache_size. Now about join, read, and sort buffers, that's correct that they're only allocated when doing those specific operations. BUT, the thing I'm not clear on is whether the amount you have set is allocated all at once or as needed up to the specified size. Of course, if the whole size is needed, it would be faster to allocate it all at once. But if not. Maybe I should ask on the Internals list sometime. Matt - Original Message - From: Alexis Guia Sent: Wednesday, November 05, 2003 5:30 AM Subject: RE: mysql memory usage Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... Alexis P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
On Wed, Nov 05, 2003 at 11:30:46AM -, Alexis Guia wrote: Sorry, but I disagree :/ I always used 250MB of key buffer, and MySQL never allocates more than 50MB, in my database. Read buffer is only allocated when full scans are done. Join buffer is allocated when there are joins without index use. Sort buffer is allocated when needed, and etc... I'm confused. Exactly which of my statements are you disagreeing with? Jeremy P.S.: you can test it easily, doing specific queries for each case. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de Novembro de 2003 23:51 To: Alexis Guia Cc: 'Benjamin KRIEF'; [EMAIL PROTECTED] Subject: Re: mysql memory usage On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 1,974,835,485 queries (430/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
On Wed, Nov 05, 2003 at 07:10:41PM -0600, Matt W wrote: Hi, In every instance I've seen, MySQL always allocates the amount you set for key_buffer at server startup even if it never comes close to being *used*. (It shouldn't be doing malloc()s or whatever for that on the fly. :-)) Same thing for query_cache_size. Yes. Now about join, read, and sort buffers, that's correct that they're only allocated when doing those specific operations. Yes, as documented in the fine manual. BUT, the thing I'm not clear on is whether the amount you have set is allocated all at once or as needed up to the specified size. Of course, if the whole size is needed, it would be faster to allocate it all at once. But if not. I'm 95% sure it's allocated all at once. But a quick scan of the source would reveal the truth. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 1,974,935,882 queries (430/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql memory usage
Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). ;) Alexis -Original Message- From: Benjamin KRIEF [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 3 de Novembro de 2003 21:00 To: [EMAIL PROTECTED] Subject: mysql memory usage hi everyone. i'd like to know if mysql always uses all the key_buffer size it has been given in my.cnf especially, on my server with : set-variable= thread_stack=128K set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=4M set-variable= net_buffer_length=8K top shows this : 21:39:49 up 134 days, 3:00, 1 user, load average: 21.82, 24.32, 21.84 Tasks: 375 total, 20 running, 355 sleeping, 0 stopped, 0 zombie Cpu(s): 83.3% user, 16.7% system, 0.0% nice, 0.0% idle Mem:901156k total, 823388k used,77768k free, 6360k buffers Swap: 1951888k total, 3376k used, 1948512k free, 711876k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 18598 mysql 20 0 54524 53m 2300 R 10.0 6.1 1:35.66 mysqld (329 lines like this one, except for the %CPU column) obviously , my server (dual pIII 1Ghz) is a bit exhausted. but my point is : mysql is threaded, so i guess the whole mysql size is 54524 (swap+physical). why doesn't mysql use the key_buffer size ? mysqladmin variables show the good key_buffer_size value. mytop says my key efficiency is 99.54%. maybe it has something to do with the database files size? bye. * Benjamin KRIEF * Directeur Technique * IGUANE Studio * -- 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 memory usage
On Tue, Nov 04, 2003 at 10:09:01AM -, Alexis Guia wrote: Hi, I think that MyISAM uses the key buffer only if needed. The same happens with almost all the other buffers (read buffer, sort buffer, etc.). True, but there's a subtle difference between uses and allocates. If you tell MySQL that it has 16GB for a key_buffer, it'll allocate 16GB even if it only ever uses 28KB. The same is true of several (probably all?) other buffers. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 51 days, processed 1,925,645,484 queries (428/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql memory usage
hi everyone. i'd like to know if mysql always uses all the key_buffer size it has been given in my.cnf especially, on my server with : set-variable= thread_stack=128K set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=128 set-variable= sort_buffer=4M set-variable= net_buffer_length=8K top shows this : 21:39:49 up 134 days, 3:00, 1 user, load average: 21.82, 24.32, 21.84 Tasks: 375 total, 20 running, 355 sleeping, 0 stopped, 0 zombie Cpu(s): 83.3% user, 16.7% system, 0.0% nice, 0.0% idle Mem:901156k total, 823388k used,77768k free, 6360k buffers Swap: 1951888k total, 3376k used, 1948512k free, 711876k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 18598 mysql 20 0 54524 53m 2300 R 10.0 6.1 1:35.66 mysqld (329 lines like this one, except for the %CPU column) obviously , my server (dual pIII 1Ghz) is a bit exhausted. but my point is : mysql is threaded, so i guess the whole mysql size is 54524 (swap+physical). why doesn't mysql use the key_buffer size ? mysqladmin variables show the good key_buffer_size value. mytop says my key efficiency is 99.54%. maybe it has something to do with the database files size? bye. * Benjamin KRIEF * Directeur Technique * IGUANE Studio * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: # processes vs. #threads, and memory usage (Revisited for thread cache)
Bringing this back up again, because the number of extra 'threads' mysql is using seems to vary widely, from I've noticed sometimes, from 5 more than the threads I'm using to more than 50! My memory usage also seems to differ accordingly. It seems to be because my thread cache is set to 40, so 'mytop' shows 38 threads cached, with only 20 connected right now. Shouldn't it kill off those threads after awhile? Is that what the wait_timeout field is for? I thought it was for keeping the connection open, rather than the connection cached. Mine is currently at '600', and it doesn't seem to be clearing up the cache... The reason this is important is because I suspect my 'slow queries' comes up when I run out of ram and it starts using disk swap - which happens because mysql is taking up so much extra memory. Thanks, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Lenz Grimmer [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 2:08 PM To: [EMAIL PROTECTED]; Kevin Subject: Re: # processes vs. #threads, and memory usage *** PGP Signature Status: unknown *** Signer: Unknown, Key ID = 0xB27291F2 *** Signed: 9/25/2003 2:07:43 PM *** Verified: 9/25/2003 3:02:03 PM *** BEGIN PGP VERIFIED MESSAGE *** Hi, On Thu, 25 Sep 2003, Kevin wrote: When I start mysql, the processlist shows 10 processes: Ps aux | grep mysql | wc -l 10 On Linux (I assume this is what we're talking about here), threads are shown as separate processes, too. On other OS'es you will only see one mysqld process in the process table. But 'mytop' (handy command line utility to see processes) shows only the number of threads I'm using. Yes, but only the threads that serve client requests. The MySQL daemon spawns a few other helper threads as well (e.g. some for InnoDB, some for replication). These are not shown in mytop, but the OS process list (if it supports showing threads as well) Anyone know why its using 10 processes if it only has 1 thread? I'm not familiar with how 'ps' tries to report on threads (since mysql technically supposedly has only 1 process). Is it doing something wrong? No, you're all fine. No worries :) My main concern is for the reported memory usage. If ps is right, and it IS using that memory, what is it doing? 1 31454 mysql23M 2.1M 108M 23M 34819 S mysqld 2 31456 mysql23M 2.1M 108M 23M 34819 S mysqld .. continue until .. 29 31639 mysql23M 2.1M 108M 23M 34819 S mysqld 30 31709 mysql23M 2.1M 108M 23M 34819 S mysqld That's the total number of memory for the mysqld process including all it's threads - it's just being repeated for every thread. Bye, LenZ -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr *** END PGP VERIFIED MESSAGE *** -- 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: # processes vs. #threads, and memory usage (Revisited for thread cache)
In the last episode (Sep 27), Kevin said: Bringing this back up again, because the number of extra 'threads' mysql is using seems to vary widely, from I've noticed sometimes, from 5 more than the threads I'm using to more than 50! My memory usage also seems to differ accordingly. It seems to be because my thread cache is set to 40, so 'mytop' shows 38 threads cached, with only 20 connected right now. Shouldn't it kill off those threads after awhile? Is that what the wait_timeout field is for? I thought it was for keeping the connection open, rather than the connection cached. Mine is currently at '600', and it doesn't seem to be clearing up the cache... Idle threads should take up almost no RAM (thread_stack plus a little bit of overhead), so there should be no need to kill them. If you're swapping, add more RAM, or reduce mysql's memory usage by reducing its buffer sizes (check the manual for which ones are used when). wait_timeout is how long before an idle client connection is terminated. If the total thread count is greater than thread_cache, the thread exits too. The reason this is important is because I suspect my 'slow queries' comes up when I run out of ram and it starts using disk swap - which happens because mysql is taking up so much extra memory. Well, that's easy enough to test; just watch vmstat output during a query. If you are swapping, either reduce the global cache settings (key_buffer, query_cache_size, or one of the many innodb_*_size variables), or the per-query settings (sort_buffer, tmp_table_size, join_buffer_size, etc). RAM is cheap too. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
# processes vs. #threads, and memory usage
Hello, When I start mysql, the processlist shows 10 processes: Ps aux | grep mysql | wc -l 10 But 'mytop' (handy command line utility to see processes) shows only the number of threads I'm using. Anyone know why its using 10 processes if it only has 1 thread? I'm not familiar with how 'ps' tries to report on threads (since mysql technically supposedly has only 1 process). Is it doing something wrong? My main concern is for the reported memory usage. If ps is right, and it IS using that memory, what is it doing? 1 31454 mysql23M 2.1M 108M 23M 34819 S mysqld 2 31456 mysql23M 2.1M 108M 23M 34819 S mysqld .. continue until .. 29 31639 mysql23M 2.1M 108M 23M 34819 S mysqld 30 31709 mysql23M 2.1M 108M 23M 34819 S mysqld Thanks, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Charlie Brewer [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 12:13 PM To: [EMAIL PROTECTED] Subject: Noob Question: Error 1046 Evenin', Sorry to send noob questions, I know how annoying they can be, but we need help too ;) Anyways, Im using a self teach book to teach myself PHP, MySQL and Apache. The book is getting into MySQL and basic commands. Anyways, they gave an example, and I typed it in as shown in the book, but I get an error. I took a screenshot (I thought that would be easier) and posted it here: http://hostultra.org/brewer/mysql.jpg Any help would be very much so appreciated, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: # processes vs. #threads, and memory usage
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 25 Sep 2003, Kevin wrote: When I start mysql, the processlist shows 10 processes: Ps aux | grep mysql | wc -l 10 On Linux (I assume this is what we're talking about here), threads are shown as separate processes, too. On other OS'es you will only see one mysqld process in the process table. But 'mytop' (handy command line utility to see processes) shows only the number of threads I'm using. Yes, but only the threads that serve client requests. The MySQL daemon spawns a few other helper threads as well (e.g. some for InnoDB, some for replication). These are not shown in mytop, but the OS process list (if it supports showing threads as well) Anyone know why its using 10 processes if it only has 1 thread? I'm not familiar with how 'ps' tries to report on threads (since mysql technically supposedly has only 1 process). Is it doing something wrong? No, you're all fine. No worries :) My main concern is for the reported memory usage. If ps is right, and it IS using that memory, what is it doing? 1 31454 mysql23M 2.1M 108M 23M 34819 S mysqld 2 31456 mysql23M 2.1M 108M 23M 34819 S mysqld .. continue until .. 29 31639 mysql23M 2.1M 108M 23M 34819 S mysqld 30 31709 mysql23M 2.1M 108M 23M 34819 S mysqld That's the total number of memory for the mysqld process including all it's threads - it's just being repeated for every thread. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/c1kfSVDhKrJykfIRAmAeAJ9cvDKYQdOfqnScgN0HV68TTEb1wgCeNd8n TBF7g3cUQMYewTjv1tdgHtY= =5GT8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: # processes vs. #threads, and memory usage
On Thu, Sep 25, 2003 at 01:18:12PM -0700, Kevin wrote: Hello, When I start mysql, the processlist shows 10 processes: Ps aux | grep mysql | wc -l 10 But 'mytop' (handy command line utility to see processes) shows only the number of threads I'm using. Anyone know why its using 10 processes if it only has 1 thread? I'm not familiar with how 'ps' tries to report on threads (since mysql technically supposedly has only 1 process). Is it doing something wrong? MySQL has a few threads that don't interact directly with clients. Are you using InnoDB? It adds a few more. My main concern is for the reported memory usage. If ps is right, and it IS using that memory, what is it doing? 1 31454 mysql23M 2.1M 108M 23M 34819 S mysqld 2 31456 mysql23M 2.1M 108M 23M 34819 S mysqld .. continue until .. 29 31639 mysql23M 2.1M 108M 23M 34819 S mysqld 30 31709 mysql23M 2.1M 108M 23M 34819 S mysqld They're threads. The memory is shared among them. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 11 days, processed 433,239,956 queries (422/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql memory usage
Is there any way to see what MySQL is storing in memory? Like, for instance, what is stored in the query cache, or at least what tables have data stored in the query cache, and how much they have stored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql memory usage
On Thu, Sep 18, 2003 at 02:53:29PM -0400, Gabriel Ricard wrote: Is there any way to see what MySQL is storing in memory? Like, for instance, what is stored in the query cache, or at least what tables have data stored in the query cache, and how much they have stored? Nothing other than what SHOW STATUS tells you. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,953,956 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23.56 Memory Usage Problem
Greetings List, I'm running a dedicated MySQL server on a Dell PE 2550, dual proc. 4GB RAM. Its using the Dell PERC 3/Di Scsi RAID controller with 18GB disks. I running RH 7.3 with the latest updates(Kernel 2.4.20-19.7smp). The MySQL version is the rpm currently being provided by the RH updates. I have been trying to tune the MySql daemon for a week now to prevent swapping. No matter what I do, I still end up with about 60GB of used swap space. Any suggestions on what I'm doing wrong? TIA for any suggestions/help! Pertinent info is below: Regards, tom. ***free total used free sharedbuffers cached Mem: 38746163862912 11704 0 246203465232 -/+ buffers/cache: 3730603501556 Swap: 2040244 605521979692 ***mysql daemon info from top* 26013 root 9 0 1076 892 888 S 0.0 0.0 0:00 safe_mysqld 26046 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:10 mysqld 26048 mysql 8 0 258M 258M 1652 S 0.0 6.8 0:01 mysqld 26049 mysql 9 0 258M 258M 1652 S 0.0 6.8 3:19 mysqld 26228 mysql 9 0 258M 258M 1652 S 0.0 6.8 22:30 mysqld 27229 mysql 9 0 258M 258M 1652 S 0.0 6.8 1:08 mysqld 27634 mysql 9 0 258M 258M 1652 S 0.0 6.8 1:15 mysqld 27639 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:49 mysqld 28031 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:59 mysqld 28841 mysql 9 0 258M 258M 1652 S 0.0 6.8 28:49 mysqld 2593 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:00 mysqld 2725 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:00 mysqld ***my.cnf:*** datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking skip-innodb skip-bdb set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=2M set-variable= record_buffer=1M set-variable= record_rnd_buffer=1M set-variable= myisam_sort_buffer_size=32M set-variable= thread_cache=8 set-variable= max_connections=150 #set-variable= thread_concurrency=4 set-variable= wait_timeout=300 set-variable= max_binlog_cache_size=2M set-variable= max_binlog_size=2M set-variable= bdb_cache_size=0 set-variable= bdb_log_buffer_size=0 set-variable= innodb_additional_mem_pool_size=0 set-variable= innodb_buffer_pool_size=0 set-variable= innodb_log_buffer_size=0 set-variable= innodb_log_file_size=0 server-id = 1 tmpdir = /var/tmp/ [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ***mysqladmin variables*** +-+-- | Variable_name | Value +-+-- | back_log| 50 | basedir | /usr/ | bdb_cache_size | 20480 | bdb_log_buffer_size | 262144 | bdb_home| | bdb_max_lock| 1 | bdb_logdir | | bdb_shared_data | OFF | bdb_tmpdir | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (March 13, 2003) | binlog_cache_size | 32768 | character_set | latin1 | character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos | | estonia euc_kr gb2312 gbk german1 greek hebrew hp8 | | hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 | | win1250 win1251 win1251ukr ujis sjis tis620 | concurrent_insert | ON | connect_timeout | 5 | datadir | /var/lib/mysql/ | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 0 | have_bdb| DISABLED | have_gemini | NO | have_innodb | DISABLED | have_isam | YES | have_raid | NO | have_openssl| NO | init_file | | innodb_additional_mem_pool_size | 524288 | innodb_buffer_pool_size | 1048576 | innodb_data_file_path | | innodb_data_home_dir| | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 8 | innodb_flush_log_at_trx_commit | 0 | innodb_fast_shutdown| ON | innodb_flush_method | | innodb_lock_wait_timeout| 50 | innodb_log_arch_dir | | innodb_log_archive | OFF | innodb_log_buffer_size |
Re: MySQL 3.23.56 Memory Usage Problem
On Mon, Aug 18, 2003 at 12:10:16PM -0400, Tom Mattison wrote: : Greetings List, : : I'm running a dedicated MySQL server on a Dell PE 2550, dual proc. 4GB : RAM. Its using the Dell PERC 3/Di Scsi RAID controller with 18GB disks. I : running RH 7.3 with the latest updates(Kernel 2.4.20-19.7smp). The MySQL : version is the rpm currently being provided by the RH updates. I have been : trying to tune the MySql daemon for a week now to prevent swapping. No : matter what I do, I still end up with about 60GB of used swap space. : : Any suggestions on what I'm doing wrong? TIA for any suggestions/help! That's not MySQL causing the problem, that's Linux's memory management. Of course, in your data below, you have 60MB of used swap, not 60GB. If you want to track the issue with Redhat, check out bug 89226 on their bugzilla website. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3.23.56 Memory Usage Problem
Thank you for the info. That certainly sounds like my problem. Time to switch back to the older kernel see if things improve. Regards, tom. -Original Message- From: Philip Molter [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2003 12:19 PM To: Tom Mattison Cc: [EMAIL PROTECTED] Subject: Re: MySQL 3.23.56 Memory Usage Problem On Mon, Aug 18, 2003 at 12:10:16PM -0400, Tom Mattison wrote: : Greetings List, : : I'm running a dedicated MySQL server on a Dell PE 2550, dual proc. 4GB : RAM. Its using the Dell PERC 3/Di Scsi RAID controller with 18GB disks. I : running RH 7.3 with the latest updates(Kernel 2.4.20-19.7smp). The MySQL : version is the rpm currently being provided by the RH updates. I have been : trying to tune the MySql daemon for a week now to prevent swapping. No : matter what I do, I still end up with about 60GB of used swap space. : : Any suggestions on what I'm doing wrong? TIA for any suggestions/help! That's not MySQL causing the problem, that's Linux's memory management. Of course, in your data below, you have 60MB of used swap, not 60GB. If you want to track the issue with Redhat, check out bug 89226 on their bugzilla website. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [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 3.23.56 Memory Usage Problem
On Mon, Aug 18, 2003 at 12:10:16PM -0400, Tom Mattison wrote: Greetings List, I'm running a dedicated MySQL server on a Dell PE 2550, dual proc. 4GB RAM. Its using the Dell PERC 3/Di Scsi RAID controller with 18GB disks. I running RH 7.3 with the latest updates(Kernel 2.4.20-19.7smp). The MySQL version is the rpm currently being provided by the RH updates. I have been trying to tune the MySql daemon for a week now to prevent swapping. No matter what I do, I still end up with about 60GB of used swap space. Any suggestions on what I'm doing wrong? TIA for any suggestions/help! Pertinent info is below: Regards, tom. ***free total used free sharedbuffers cached Mem: 38746163862912 11704 0 246203465232 -/+ buffers/cache: 3730603501556 Swap: 2040244 605521979692 ***mysql daemon info from top* 26013 root 9 0 1076 892 888 S 0.0 0.0 0:00 safe_mysqld 26046 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:10 mysqld 26048 mysql 8 0 258M 258M 1652 S 0.0 6.8 0:01 mysqld 26049 mysql 9 0 258M 258M 1652 S 0.0 6.8 3:19 mysqld 26228 mysql 9 0 258M 258M 1652 S 0.0 6.8 22:30 mysqld 27229 mysql 9 0 258M 258M 1652 S 0.0 6.8 1:08 mysqld 27634 mysql 9 0 258M 258M 1652 S 0.0 6.8 1:15 mysqld 27639 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:49 mysqld 28031 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:59 mysqld 28841 mysql 9 0 258M 258M 1652 S 0.0 6.8 28:49 mysqld 2593 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:00 mysqld 2725 mysql 9 0 258M 258M 1652 S 0.0 6.8 0:00 mysqld It seems that mysqld itself has not been swapped out at all. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 17 days, processed 869,601,135 queries (591/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lowering memory usage?
Hi everyone, I was wondering if there's any way I can lower MySQL's memory useage? It often uses more than 5kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at C:/ and windows calls it cardnumber or something)... Thanks in advance... // DvDmanDT MSN: [EMAIL PROTECTED] Mail: [EMAIL PROTECTED]
memory usage
Hi, I have a question about the memory that mysql uses, Here is the info that top command displays: 7:39pm up 55 days, 2:51, 1 user, load average: 0.18, 0.08, 0.02 54 processes: 53 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.1% user, 10.1% system, 0.0% nice, 89.3% idle CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 3616400K av, 3592084K used, 24316K free, 0K shrd, 193468K buff Swap: 2096440K av,6300K used, 2090140K free 2276512K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 18538 mysql 15 0 936M 930M 2516 S 0.0 26.3 4:23 mysqld-max 19036 mysql 15 0 936M 930M 2516 S 0.0 26.3 4:51 mysqld-max 19037 mysql 20 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 19038 mysql 16 0 936M 930M 2516 S 0.0 26.3 0:01 mysqld-max 19039 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 19040 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:17 mysqld-max 19181 mysql 17 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 19182 mysql 15 0 936M 930M 2516 S 0.0 26.3 7:59 mysqld-max 19193 mysql 15 0 936M 930M 2516 S 0.0 26.3 1:03 mysqld-max 19196 mysql 15 0 936M 930M 2516 S 0.0 26.3 2:32 mysqld-max 16336 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 28450 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 28451 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 3004 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 7999 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 8161 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 8323 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 8474 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 27353 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 11775 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max 30268 mysql 15 0 936M 930M 2516 S 0.0 26.3 0:00 mysqld-max I don't know if all of these processes consume all my memory. Here is my.cnf file: default-table-type=innodb set-variable = key_buffer=850M set-variable = table_cache=1500 set-variable = sort_buffer=4M set-variable = record_buffer=4M set-variable = myisam_sort_buffer_size=750M innodb_data_home_dir = /var/lib/mysql innodb_log_group_home_dir = /var/lib/mysql innodb_log_arch_dir = /var/lib/mysql innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_buffer_pool_size=850M set-variable = innodb_additional_mem_pool_size=250M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M set-variable = innodb_file_io_threads=5 set-variable = innodb_lock_wait_timeout=50 Any suggestions?... Best Regards _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: memory usage
On Mon, Jun 23, 2003 at 07:21:25PM -0500, Miguel Perez wrote: Hi, I have a question about the memory that mysql uses, Here is the info that top command displays: 7:39pm up 55 days, 2:51, 1 user, load average: 0.18, 0.08, 0.02 54 processes: 53 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.1% user, 10.1% system, 0.0% nice, 89.3% idle CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 3616400K av, 3592084K used, 24316K free, 0K shrd, 193468K buff Swap: 2096440K av,6300K used, 2090140K free 2276512K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 18538 mysql 15 0 936M 930M 2516 S 0.0 26.3 4:23 mysqld-max [snip] I don't know if all of these processes consume all my memory. Clearly not. Look at the 193468K buff to see that Linux is using some of it to buffer disk I/O. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 20 days, processed 647,864,137 queries (361/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL memory usage
Hello, On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? Our keycache hitrate is at 99.6% so I think the key_buffer_size is big enough, but would increasing this setting help? We experimented with doubling this buffer, but mysql got slower from it. Or could someone have a look at our my.cnf, and see if anything could be better? Here's my.cnf: port= 3306 socket = /tmp/mysql.sock set-variable= key_buffer_size = 256M set-variable= max_allowed_packet = 1M #set-variable = thread_stack= 128K set-variable= max_connections = 300 #set-variable = query_buffer_size = 4M set-variable= record_buffer = 32M #set-variable = record_buffer = 64M set-variable= record_rnd_buffer = 32M #set-variable = sort_buffer = 8M set-variable= sort_buffer = 4M set-variable= join_buffer = 16M set-variable= table_cache = 768 What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. Kind regards, Rick Jansen - 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
MySQL memory usage
Hello, On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? Our keycache hitrate is at 99.6% so I think the key_buffer_size is big enough, but would increasing this setting help? We experimented with doubling this buffer, but mysql got slower from it. Or could someone have a look at our my.cnf, and see if anything could be better? Here's my.cnf: port= 3306 socket = /tmp/mysql.sock set-variable= key_buffer_size = 256M set-variable= max_allowed_packet = 1M #set-variable = thread_stack= 128K set-variable= max_connections = 300 #set-variable = query_buffer_size = 4M set-variable= record_buffer = 32M #set-variable = record_buffer = 64M set-variable= record_rnd_buffer = 32M #set-variable = sort_buffer = 8M set-variable= sort_buffer = 4M set-variable= join_buffer = 16M set-variable= table_cache = 768 What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. Kind regards, Rick Jansen - 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: MySQL memory usage
On Friday 07 Mar 2003 3:28 pm, Rick Jansen wrote: On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. What OS are you using? 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? That seems fine we are using about the same. Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? We are running on a Gig of RAm with the smae size of DB so I doubt it. What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. If you could provide me with your MySQL version and your OS I can advise a bit more. We had major problems with slow mysql up until a few days ago which we have now solved, depending on your set up I could help out. Cheers John Wards SportNetwork.net - 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: MySQL memory usage
At 15:36 7-3-03 +, John Wards wrote: What OS are you using? Linux 2.4.20. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? That seems fine we are using about the same. Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? We are running on a Gig of RAm with the smae size of DB so I doubt it. But if we have 2Gb of memory, instead of 1Gb, Linux could use more memory to cache the disk, so it could store more queries in cache, rather than to get them from the disk again, right? The more memory Linux uses for buffering, the less it has to get from disk.. What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. If you could provide me with your MySQL version and your OS I can advise a bit more. MySQL 3.23.55, Linux 2.4.20. We had major problems with slow mysql up until a few days ago which we have now solved, depending on your set up I could help out. Cheers John Wards SportNetwork.net - 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 Systems Administrator Rockingstone IT - 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: MySQL memory usage
Hmmm I would say other than upgrading your hardware try upgrading to mySQL 4. and turn on query caching. The difference it has made to our server is unbelivable! We have found no problems with it so far. John - 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
Memory usage of MySQL-Max on Rh 8.0 with InnnoDB
Hello, I am running MySQL-Max on RH8.0 on a Dell 1650, 2 proc with 512MB ram. I am only using InnoDB for databases (40gb) other than the mysql.*. I have the following relevant memory settings: set-variable= sort_buffer=2M set-variable= record_buffer=2M ## For redhat 8.0 set-variable= thread_stack=192k set-variable= myisam_sort_buffer_size=2M ... set-variable = innodb_log_buffer_size=30M set-variable = innodb_buffer_pool_size=396M set-variable = innodb_additional_mem_pool_size=2M I don't have anything other than the basic daemons running on this machine. Top reports: 71 processes: 69 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 6.0% user, 1.0% system, 0.0% nice, 91.0% idle CPU1 states: 55.1% user, 11.1% system, 0.0% nice, 32.0% idle Mem: 513296K av, 506796K used,6500K free, 0K shrd,7468K buff Swap: 1048568K av, 241628K used, 806940K free 159928K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 2772 mysql.pr 15 0 480M 302M 53104 S63.8 60.3 1:15 mysqld-max Vmstat reports: [EMAIL PROTECTED] prod]# vmstat 1 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0 376168 6580 3176 105724 2 31025 25910 0 0 99 0 1 0 376168 7160 3212 104216 1064 0 1064 5823 846 631 30 3 67 1 0 0 376168 6592 3192 101156 1688 0 1700 5820 988 929 30 4 67 1 0 0 376168 6516 2680 102644 848 16 2924 6312 769 627 29 8 63 This doesn't seem right. Why so much swap usage? Why am I swapping? The other processes on the box don't even come close to accounting for the swap usage, let alone the swapping. Any help here is greatly appreciated. Thanks Richard - 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
MySQL max memory usage on linux/intel
Apologies if this is off topic ... please let me know what a more appropriate forum is if so. Quick question: since there is normally a per-process limit of 4GB on 32-bit linux/intel platforms is there any point to installing more than 4gb in an sql server that is only running running 1 instance of the DB daemon? The total size of the DB is 30 GB and several tables are 4GB. -- -Chris Beck - Coradiant, Inc - Research Development - +1.514.908.6314- -- http://www.coradiant.com - Leaders in Web Performance Optimization -- --- This email represents my opinion, not that of Coradiant. --- We do not inherit the Earth from our Ancestors, we borrow it from our Children. -- Indian Proverb - 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: MySQL max memory usage on linux/intel
You can expect that your operating system will use the remaining memory as a file cache that will speed up your databases accesses. We don't have that much memory our dedicated database server, it has only 2 Gb. mysqld itself is using around 600Mb the rest is used by the OS mainly for file caching. Since the total size of all databases is around 1.5 Gb they almost fit in memory and the server is happily serving around 300 queries/s with 200 concurrent connections and almost no disk I/O (no reads, just a few write every 5 seconds). Hope this helps -- Joseph Bueno [EMAIL PROTECTED] wrote: Apologies if this is off topic ... please let me know what a more appropriate forum is if so. Quick question: since there is normally a per-process limit of 4GB on 32-bit linux/intel platforms is there any point to installing more than 4gb in an sql server that is only running running 1 instance of the DB daemon? The total size of the DB is 30 GB and several tables are 4GB. -- -Chris Beck - Coradiant, Inc - Research Development - +1.514.908.6314- -- http://www.coradiant.com - Leaders in Web Performance Optimization -- --- This email represents my opinion, not that of Coradiant. --- We do not inherit the Earth from our Ancestors, we borrow it from our Children. -- Indian Proverb - 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: MySQL max memory usage on linux/intel
How do I set the cache limit? Any suggestions on optimizing mySQL? At 07:05 PM 11/21/2002 +0100, Joseph Bueno wrote: You can expect that your operating system will use the remaining memory as a file cache that will speed up your databases accesses. We don't have that much memory our dedicated database server, it has only 2 Gb. mysqld itself is using around 600Mb the rest is used by the OS mainly for file caching. Since the total size of all databases is around 1.5 Gb they almost fit in memory and the server is happily serving around 300 queries/s with 200 concurrent connections and almost no disk I/O (no reads, just a few write every 5 seconds). Hope this helps -- Joseph Bueno [EMAIL PROTECTED] wrote: Apologies if this is off topic ... please let me know what a more appropriate forum is if so. Quick question: since there is normally a per-process limit of 4GB on 32-bit linux/intel platforms is there any point to installing more than 4gb in an sql server that is only running running 1 instance of the DB daemon? The total size of the DB is 30 GB and several tables are 4GB. -- -Chris Beck - Coradiant, Inc - Research Development - +1.514.908.6314- -- http://www.coradiant.com - Leaders in Web Performance Optimization -- --- This email represents my opinion, not that of Coradiant. --- We do not inherit the Earth from our Ancestors, we borrow it from our Children. -- Indian Proverb - 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
Thread Memory Usage
Hey folks, I've looked around, but haven't seen anything similar to _quite_ the problem I have. The symptoms are thus: memory usage for each mysqld process in top creeps up to around 13%, 274MB, and stays there, no matter what I set my config to. 79 processes: 76 sleeping, 3 running, 0 zombie, 0 stopped CPU0 states: 100.0% user, 0.1% system, 0.0% nice, 0.0% idle CPU1 states: 98.2% user, 1.2% system, 0.0% nice, 0.0% idle CPU2 states: 1.1% user, 2.0% system, 0.0% nice, 96.2% idle CPU3 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle Mem: 2064836K av, 2053068K used, 11768K free, 0K shrd, 15836K buff Swap: 2096440K av, 35312K used, 2061128K free 1429524K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 18339 hunt 25 0 267M 267M 696 R99.9 13.2 64:53 framework 985 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1015 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1016 mysql 18 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1017 mysql 16 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1018 mysql 17 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1019 mysql 20 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1044 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1045 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:09 mysqld 1046 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1047 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:09 mysqld 1196 mysql 15 0 274M 259M 14460 S 0.0 12.8 16:14 mysqld 1288 mysql 25 0 274M 259M 14460 S 0.0 12.8 29:06 mysqld 25425 mysql 25 0 274M 259M 14460 R99.9 12.8 26:10 mysqld 29645 mysql 15 0 274M 259M 14460 S 0.0 12.8 1:04 mysqld Now, I'm assuming some of that memory must be shared across processes(threads?) since 14 processes * 12.8% is more than 100%, and technically I still have 11.7M free. My config has this: set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 Now, I've tried dropping all of the variables, cutting them in half, then again, to no avail -- when I run a large query (via the DBI interface in perl) the Mem usage jumps up to 13% (for all mysqld) and never comes back down. Most notibly I've dropped the key_buffer to 64M and the table_cache to 32. My thought was the query cache, since I thought that I read on the main site that it will use about 13% per thread. But no, I have that disabled: | query_cache_limit | 1048576 | query_cache_size| 0 | query_cache_type| ON Size 0 supposedly means it is disabled -- or is this a bug? Is is query_cache_size 0' makes it think it is off but 'query_cache_type ON' makes it cache anyway? Regardless, flushing the tables and cache doesn't seem to do anything. I'm going to reduce the number of concurrent threads (it is set to 8 now) but that's not the fix I want -- any helpful hints? Actually, since it's set to 8, why do I get 14 processes? Two of them are parents to the other 12, but still? Thanks, Nick Elliott Using MySQL 4.0.4-beta-log Linux Kernel 2.4.18-17.7.xsmp - 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: Thread Memory Usage
On Thu, Nov 14, 2002 at 09:02:59AM -0500, Nicholas Elliott wrote: Hey folks, I've looked around, but haven't seen anything similar to _quite_ the problem I have. The symptoms are thus: memory usage for each mysqld process in top creeps up to around 13%, 274MB, and stays there, no matter what I set my config to. 79 processes: 76 sleeping, 3 running, 0 zombie, 0 stopped CPU0 states: 100.0% user, 0.1% system, 0.0% nice, 0.0% idle CPU1 states: 98.2% user, 1.2% system, 0.0% nice, 0.0% idle CPU2 states: 1.1% user, 2.0% system, 0.0% nice, 96.2% idle CPU3 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle Mem: 2064836K av, 2053068K used, 11768K free, 0K shrd, 15836K buff Swap: 2096440K av, 35312K used, 2061128K free 1429524K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 18339 hunt 25 0 267M 267M 696 R99.9 13.2 64:53 framework 985 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1015 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1016 mysql 18 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1017 mysql 16 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1018 mysql 17 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1019 mysql 20 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1044 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1045 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:09 mysqld 1046 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:00 mysqld 1047 mysql 15 0 274M 259M 14460 S 0.0 12.8 0:09 mysqld 1196 mysql 15 0 274M 259M 14460 S 0.0 12.8 16:14 mysqld 1288 mysql 25 0 274M 259M 14460 S 0.0 12.8 29:06 mysqld 25425 mysql 25 0 274M 259M 14460 R99.9 12.8 26:10 mysqld 29645 mysql 15 0 274M 259M 14460 S 0.0 12.8 1:04 mysqld Now, I'm assuming some of that memory must be shared across processes(threads?) since 14 processes * 12.8% is more than 100%, and technically I still have 11.7M free. Yes. My config has this: set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 Now, I've tried dropping all of the variables, cutting them in half, then again, to no avail -- when I run a large query (via the DBI interface in perl) the Mem usage jumps up to 13% (for all mysqld) and never comes back down. Most notibly I've dropped the key_buffer to 64M and the table_cache to 32. That tells me that you're my.cnf file isn't being read by MySQL. Or you're editing the wrong one. Or something similar. After you change the values and restart, run SHOW VARIABLES and make sure the values agree with what you expect. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 100 days, processed 2,122,549,010 queries (244/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
memory usage
I'm concerned about memory usage of mysql connections. I've been reading the mailing list archives but I'm still clueless. I need to buy a new server for a mysql database that will have at least 500 persistent connections from an Apache server with mod_perl. I'm checking the memory usage of the development server. I've used Apache::VMonitor to find out the apache shared memory issues. But I'm unable to see what's happening with the mysql connections. Here is the output of VMonitor: ## PID UIDSize Share VSize Rss TTY St Command 1 23902 mysql 21M 2.2M 71M 21M S mysqld 2 23904 mysql 21M 2.2M 71M 21M S mysqld 3 23905 mysql 21M 2.2M 71M 21M S mysqld 4 23906 mysql 21M 2.2M 71M 21M S mysqld Does this mean every connection uses 21M of memory, 2.2 MB shared with the rest of mysql clients ? Any hint for the memory required for this server ? thank you very much. - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: .Optimize mySQL memory usage ? 41MB threads??
Jon, So how do I optimize memory usage? Where to start ? If your server is compiled with debug=full, the command mysqladmin proc stat shows you total server memory use. Look in the manual at How MySQL uses Memory SHOW VARIABLES (for all vars that control server memory use) Perhaps the following little vignette we worked up may help ... The size of the non-InnoDB index buffer is set by key_buffer_size, which to improve performance for even moderately sized databases can be set to a value up to a quarter or more of available server RAM, but to avoid thrashing, should be set to not more than half. The FIFO table handler cache allots up to 64 entries on a per-open-table-per-connection basis. Issuing mysqladmin flush-tables closes all tables that are not in use, marks all other tables for closing when the corresponding thread ends, and frees most allocated memory. The maximum number of concurrent connections is set by max_connections. Each connection - uses a stack of thread_stack bytes, default 64k, - uses a connection buffer of net_buffer_length bytes, default 16k, - uses a result buffer (same size), and - opens each data and index file once, creating for each table a table buffer, buffers for each column, a row buffer 3 times the maximum row length, and if the table has a BLOB column then a BLOB buffer up to the size of the largest BLOB value. So suppose you expect ... - 200 maximum connections, - queries that access 10 tables max + 3 temp tables each, - average table buffer sizes of 32k, - average row lengths of 10k, - no BLOB columns, and - a quarter of all queries requiring sorts, then - the average number of open tables for all threads, (the MySQL variable table_cache) is 200(10 + 3) = 2600, - the server needs 2600*(64+16+16+3*10+32)k + 50(200/4)MB = 510 MB RAM for connection buffering. PB - 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
.Optimize mySQL memory usage ? 41MB threads ??
I have a small PHP website that gets a fair amount of traffic. It's a simple layout with two tables. I average about 50 concurrently open apache sessions and 40 open mysql connections. I am calling for persistent connections from the mySQL/PHP API. But here is the kicker. Each mySQL thread takes about 41mb!! :( Can anyone point me to resources on optimizing mySQL's memory usage under a moderate/heavy load? Thanks Jon - 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: .Optimize mySQL memory usage ? 41MB threads ??
In the last episode (Oct 07), Jon Shoberg said: I have a small PHP website that gets a fair amount of traffic. It's a simple layout with two tables. I average about 50 concurrently open apache sessions and 40 open mysql connections. I am calling for persistent connections from the mySQL/PHP API. But here is the kicker. Each mySQL thread takes about 41mb!! :( Can anyone point me to resources on optimizing mySQL's memory usage under a moderate/heavy load? Threads have no memory. You're getting misled by Linuxes threads implementation, which displays each thread as if it were a separate process. They actually share the same block of memory, which is why they're threads and not processes. -- Dan Nelson [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: .Optimize mySQL memory usage ? 41MB threads ??
Ok, So how do I optimize memory usage? Where to start ? -Jon -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:16 PM To: Jon Shoberg Cc: [EMAIL PROTECTED] Subject: Re: .Optimize mySQL memory usage ? 41MB threads ?? In the last episode (Oct 07), Jon Shoberg said: I have a small PHP website that gets a fair amount of traffic. It's a simple layout with two tables. I average about 50 concurrently open apache sessions and 40 open mysql connections. I am calling for persistent connections from the mySQL/PHP API. But here is the kicker. Each mySQL thread takes about 41mb!! :( Can anyone point me to resources on optimizing mySQL's memory usage under a moderate/heavy load? Threads have no memory. You're getting misled by Linuxes threads implementation, which displays each thread as if it were a separate process. They actually share the same block of memory, which is why they're threads and not processes. -- Dan Nelson [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: .Optimize mySQL memory usage ? 41MB threads ??
In the last episode (Oct 07), Jon Shoberg said: From: Dan Nelson [mailto:[EMAIL PROTECTED]] In the last episode (Oct 07), Jon Shoberg said: I have a small PHP website that gets a fair amount of traffic. It's a simple layout with two tables. I average about 50 concurrently open apache sessions and 40 open mysql connections. I am calling for persistent connections from the mySQL/PHP API. But here is the kicker. Each mySQL thread takes about 41mb!! :( Can anyone point me to resources on optimizing mySQL's memory usage under a moderate/heavy load? Threads have no memory. You're getting misled by Linuxes threads implementation, which displays each thread as if it were a separate process. They actually share the same block of memory, which is why they're threads and not processes. So how do I optimize memory usage? Where to start ? Have you read the manual? Chapter 5 pretty much covers everything. http://www.mysql.com/doc/en/MySQL_Optimisation.html http://www.mysql.com/doc/en/Memory_use.html -- Dan Nelson [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: .Optimize mySQL memory usage ? 41MB threads ??
On Mon, 7 Oct 2002, Jon Shoberg wrote: Ok, So how do I optimize memory usage? Where to start ? -Jon -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 9:16 PM To: Jon Shoberg Cc: [EMAIL PROTECTED] Subject: Re: .Optimize mySQL memory usage ? 41MB threads ?? In the last episode (Oct 07), Jon Shoberg said: I have a small PHP website that gets a fair amount of traffic. It's a simple layout with two tables. I average about 50 concurrently open apache sessions and 40 open mysql connections. I am calling for persistent connections from the mySQL/PHP API. But here is the kicker. Each mySQL thread takes about 41mb!! :( Can anyone point me to resources on optimizing mySQL's memory usage under a moderate/heavy load? Threads have no memory. You're getting misled by Linuxes threads implementation, which displays each thread as if it were a separate process. They actually share the same block of memory, which is why they're threads and not processes. add set-variable = key_buffer=128M to my.cnf file. actually 128M should be the sum of all the index (*.MYI) files. FYI - mysql only caches data read from indexes you can check innodb buffer usage or hit ratios by running the innodb_monitor. - 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: Growing memory usage/processes
In the last episode (Jul 03), Dave said: Hello all, I have mysql running on a 2.4.18 kernel: /usr/libexec/mysqld Ver 3.23.49 for redhat-linux-gnu on i386 and note that one started about 4 processes (threads?) began to handle the various signal/table tasks and such. After several random queries the process list grows accordingly. After a couple hours all processes which showed consumption of about 8MB of memory initially are now 25MB each and growing as queries are received. My question is...Why if these are threads does it require each thread to utilize so much memory? 4 threads using 10MB each is ok, 10 threads using 10MB each is ok. 20 threads using 25MB each is too much. No. Threads share the same address space. You are seeing one threaded application consuming 25MB. Linux's threads implentation creates a separate process for each thread and top has no idea they're really all one application. Linux is the only OS that does this, afaik, and you would not believe the number of times your question has been asked on this list. On Solaris and Tru64, at least, a threaded app shows up as one entry in top. -- Dan Nelson [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: Growing memory usage/processes
Thanks for the confirmation Dan, I will look a little closer but I could have sworn when I shutdown MySQL about 300MB of memory got freed. I had a suspicion the case was one process and ps/top could not distinguish...I just needed to hear confirmation of it I guess. -Dave In the last episode (Jul 03), Dave said: Hello all, I have mysql running on a 2.4.18 kernel: /usr/libexec/mysqld Ver 3.23.49 for redhat-linux-gnu on i386 and note that one started about 4 processes (threads?) began to handle the various signal/table tasks and such. After several random queries the process list grows accordingly. After a couple hours all processes which showed consumption of about 8MB of memory initially are now 25MB each and growing as queries are received. My question is...Why if these are threads does it require each thread to utilize so much memory? 4 threads using 10MB each is ok, 10 threads using 10MB each is ok. 20 threads using 25MB each is too much. No. Threads share the same address space. You are seeing one threaded application consuming 25MB. Linux's threads implentation creates a separate process for each thread and top has no idea they're really all one application. Linux is the only OS that does this, afaik, and you would not believe the number of times your question has been asked on this list. On Solaris and Tru64, at least, a threaded app shows up as one entry in top. -- Dan Nelson [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 - 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: Growing memory usage/processes
Hi, Hi, It depends also of how many threads are running at the same time + sort buffer / record buffer values. The memory consumption can be roughly calculated as key buffer + (sort buffer + record buffer) * number of thread. As you can see, only the key buffer is shared between all the threads. If you want to lower the number of sleeping thread, take a look at the wait_timeout variable in your my.cnf file. Regards, Jocelyn - Original Message - From: Dave [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 04, 2002 8:39 AM Subject: Re: Growing memory usage/processes Thanks for the confirmation Dan, I will look a little closer but I could have sworn when I shutdown MySQL about 300MB of memory got freed. I had a suspicion the case was one process and ps/top could not distinguish...I just needed to hear confirmation of it I guess. -Dave In the last episode (Jul 03), Dave said: Hello all, I have mysql running on a 2.4.18 kernel: /usr/libexec/mysqld Ver 3.23.49 for redhat-linux-gnu on i386 and note that one started about 4 processes (threads?) began to handle the various signal/table tasks and such. After several random queries the process list grows accordingly. After a couple hours all processes which showed consumption of about 8MB of memory initially are now 25MB each and growing as queries are received. My question is...Why if these are threads does it require each thread to utilize so much memory? 4 threads using 10MB each is ok, 10 threads using 10MB each is ok. 20 threads using 25MB each is too much. No. Threads share the same address space. You are seeing one threaded application consuming 25MB. Linux's threads implentation creates a separate process for each thread and top has no idea they're really all one application. Linux is the only OS that does this, afaik, and you would not believe the number of times your question has been asked on this list. On Solaris and Tru64, at least, a threaded app shows up as one entry in top. -- Dan Nelson [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 - 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
Growing memory usage/processes
Hello all, I have mysql running on a 2.4.18 kernel: /usr/libexec/mysqld Ver 3.23.49 for redhat-linux-gnu on i386 and note that one started about 4 processes (threads?) began to handle the various signal/table tasks and such. After several random queries the process list grows accordingly. After a couple hours all processes which showed consumption of about 8MB of memory initially are now 25MB each and growing as queries are received. My question is...Why if these are threads does it require each thread to utilize so much memory? 4 threads using 10MB each is ok, 10 threads using 10MB each is ok. 20 threads using 25MB each is too much. When I shutdown MySQL available memory increases according to the number of threads * size previously in use. Is this a problem or a feature? Is there a way to limit mysqld to only a certain number of maximum threads and a maximum size of memory always? I don't really like seeing this runaway with all my memory. On the other hand, CPU load is incredibly low. Inserting about 1.5 million records never hit more than about 30% load. The machine is a dual P3 1000Mhz with 1GB of RAM. Any ideas? Thanks! -Dave - 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: Problem with MySQL4.0.1 speed, cpu- and memory-usage
On Thu, Jan 24, 2002 at 12:06:14PM +0100, Marcus Mueller wrote: Hello, in order to be able to use the replication features of MySQL we recently gave version 4.0.1alpha a try, since these features are reportedly more reliable in this version Really? I haven't heard that about the replication code. In fact, I've seen the replication in 4.0.x being a bit funky compared to 3.23.xx. This is to be expected, since the replication code was re-written for 4.0. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 50 days, processed 1,430,073,829 queries (328/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
Problem with MySQL4.0.1 speed, cpu- and memory-usage
Hello, in order to be able to use the replication features of MySQL we recently gave version 4.0.1alpha a try, since these features are reportedly more reliable in this version - although it is still alpha. Unfortunately the disadvantages - at least on our machines - don't seem to make up for the advantages: we experienced a very high server load combined with higher memory-usage than usual, the average speed dropped to a level the tested databases became almost unusable. Let me explain our setup: we're running a site-internal messaging system that handles approx. 110.000 messages per day. The databases consists of a header and a content table, the first one being a fixed-length MyISAM-table with almost 40.000.000 records, the second one is a dynamic-length MyISAM-table with some 20.000.000 records. We don't use fancy stuff like InnoDB but only very basic SELECT (on indexed columns) and INSERT statements, not even JOINs. The whole thing runs on a 850MHz dual-pentium with 4GB memory under Linux 2.4.17 and RAID1. Until now we used MySQL 3.23.32 which in our tests remains the fastest version so far, all following versions (including 3.23.47 and 4.0.1alpha) remarkably dropped in speed. Now finally to my question: is there a way to speed up 4.0.1alpha (or any other version with reliable replication), maybe a different OS (BSD is reported to be more performant but we have no experience with that so far) could help? Are there any fine-tunings in the ./configure that may have a positive effect (we didn't notice big changes in speed and memory usage when playing with these options)? Usually we would just stick with 3.23.32 but, as mentioned above, we need the slightly more advanced and reliable replication features of its successors in order to be able to distribute read accesses to several slaves while the master does all the writing stuff. Any help/comments much appreciated Marcus - 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
help understanding memory usage
I am trying to understand how MySQL uses memory when executing a query on a large table. The server is running on RedHat 6.0 with pentium III 750 Mhz processor and 2 Gig of RAM. I have my key_buffer_size set to 1600MB. My table cache is set at 512. When I run top, I get the following out put: Mem: 4193280K av, 1275776K used, 2917504K free, 0K shrd, 0k buff Swap: 0K av,0K used, 0k free 0k cached PIDUSERPRINISIZERSSSHARESTATLIB%CPU %MEMTIMECOMMAND 5065mysql 0 0330M 330M1608 S 327M 0.08.0 0:02 mysqld 5067mysql 120330M 330M1608 S 327M 0.08.0 0:01 mysqld 5068mysql 0 0330M 330M1608 S 327M 0.08.0 0:03 mysqld 4398mysql 0 0330M 330M1608 S 327M 0.08.0 0:00 mysqld When I fisrt start mysqld, the SIZE, RSS, and LIB are much smaller and they slowly increase as I run the query on the large table. The table contains 47,000,000 plus records. I have the fields indexed and when I use the EXPLAIN command, it shows that MySQL is using the indexes properly. I am look for suggestions on how to make the query faster. I have read the suggestions in the manual and tried them, but nothing seems to improve the performance. Have I reached the a limitation of MySQL on my hardware setup? Thanks, Jeff - 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
Memory usage
Hello, I was wondering, how can I lookup how much memory MySQL is currently allowed to use, and how can I change this value? Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 - 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: Question regarding memory usage (UPDATE)
Just thought I'd share the results so far: As I mentioned earlier, I went ahead and upgraded the kernel from 2.2.16 to 2.4.4. The server has been running for nearly 36 hours since then, and has reached an RSS of 100M, well over what it ever reached before. And things are still running very smoothly. So thanks for your tip, the kernel upgrade seems to have been a perfect solution :) Jon On Mon, 7 May 2001, Rene Tegel wrote: As you'd expect, the server gets bogged down rather quickly at this point, serving new requests very slowly if at all. Restarting MySQL helps right away, buying me another 24 hours of stable uptime. If this is a linux box it could be a problem with linuxthreads. Try upgrading to kernel 2.4.4 - 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: Question regarding memory usage
On Mon, 7 May 2001 06:27:08 -0400 (EDT) Jon Valvatne [EMAIL PROTECTED] wrote: As you'd expect, the server gets bogged down rather quickly at this point, serving new requests very slowly if at all. Restarting MySQL helps right away, buying me another 24 hours of stable uptime. If this is a linux box it could be a problem with linuxthreads. Try upgrading to kernel 2.4.4 Details: From my.cnf: skip-locking skip-networking set-variable= max_connections=18 try raising max_connections set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=20M set-variable= thread_cache=8 set-variable= thread_concurrency=4 # Try number of CPU's*2 try raising thread concurrency. set-variable= query_buffer_size=16k set-variable= tmp_table_size=6M set-variable= delayed_insert_limit=15 set-variable= max_write_lock_count=1 - 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: Question regarding memory usage
Jon Valvatne wrote: Hello, I have a hopefully simple question here: My web site is dynamically serving 300k page views a day from a MySQL database, running on a dual P3/700 with 512 megs of ram. Considering the complexity of my queries and the data amount involved, I know I should expect the server to be overworked, but the weird thing is how the problem manifests itself: When I start up MySQL and Apache, things seem to run fine for a while, and they continue running fine for about 24 hours. Then, when MySQL memory usage has grown to around 70-80 megs, things start to slow down. Queries which previously were over in a few hundredths of a second start taking up to several seconds to complete, usually spending the extra time Sending data or Copying to temp table. As you'd expect, the server gets bogged down rather quickly at this point, serving new requests very slowly if at all. Restarting MySQL helps right away, buying me another 24 hours of stable uptime. What could cause this? I suspect it has something to do with the settings in my.cnf, but I've tried many different combinations without success. If someone could point me to the right variable(s) to tweak, that would be helpful in itself. Suggested values for my system would help even more. For now I can handle things by restarting MySQL every night, but that's not a good solution. Shouldn't MySQL, in theory, be able to reuse memory and other resources well enough for a system which stays stable for 24 hours to be expected to stay stable for a year? Could this be some sort of memory leak in either MySQL or Apache? Any help or advice would be much appreciated. Server details below. Thanks, Jon Valvatne Webmaster, AvidGamers.Com Details: Dual PIII/700 512MB RAM 9GB SCSI Drive MySQL 3.23.37 Apache 1.3.12 PHP 4.0.4pl1 From my.cnf: skip-locking skip-networking set-variable= max_connections=18 set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=20M set-variable= thread_cache=8 set-variable= thread_concurrency=4 # Try number of CPU's*2 set-variable= query_buffer_size=16k set-variable= tmp_table_size=6M set-variable= delayed_insert_limit=15 set-variable= max_write_lock_count=1 - 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 Hi, Are you sure that you need 200Mb of key_buffer cache ? Since your machine is obviously swapping a lot when it slows down (you can verify that with vmstat), I think you should try first to lower RAM usage. Use 'mysqladmin extended-status' and find out how much RAM you need for key_buffer (check 'Key_blocks_used' variable) and reduce key_buffer value. Hope this helps -- Joseph Bueno NetClub/Trader.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: Question regarding memory usage
That's the weird part; it doesn't seem to be swapping at all. When trying different combinations in my.cnf, I had key_buffer as low as 64M without any effect. Jon On Mon, 7 May 2001, Joseph Bueno wrote: Hi, Are you sure that you need 200Mb of key_buffer cache ? Since your machine is obviously swapping a lot when it slows down (you can verify that with vmstat), I think you should try first to lower RAM usage. Use 'mysqladmin extended-status' and find out how much RAM you need for key_buffer (check 'Key_blocks_used' variable) and reduce key_buffer value. Hope this helps -- Joseph Bueno NetClub/Trader.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: Question regarding memory usage
On Mon, 7 May 2001 07:40:26 -0400 (EDT) [EMAIL PROTECTED] wrote: Thanks for the quick reply. I set max_connections so low because even at peaks I never need more, and when the slowdowns happen, it seems to have an easier time recovering if there are 15 slow connections hanging than if there are hundreds. Are you saying you have 15 slow queries and thus only 3 connections available for your site to run at time server crashes? that's wrong and probably one of the reasons your site slows down. I guess you have some script(s) with 'corrupt' queries that take loads of time (on non-indexed fields or something) to complete. So mysql gets more and more slow queries which may take hours to complete, whilst slowing down your site. You should check which script(s) are responsible for this, and fix the bugs. I'll try the thread concurrency thing, thanks. I was under the impression this variable was only effective on a Solaris box, but I may be wrong? you might be quite right. i just noticed it was not the default.. I suspect you may be right about linuxthreads being the problem. Unfortunately, I'm on a managed hosting solution, and I'm not sure if I want to risk a kernel panic and countless hours of expensive support :) you always could try if same database/scripts generate same errors on another box.. Thanks again, Jon On Mon, 7 May 2001, Rene Tegel wrote: On Mon, 7 May 2001 06:27:08 -0400 (EDT) Jon Valvatne [EMAIL PROTECTED] wrote: As you'd expect, the server gets bogged down rather quickly at this point, serving new requests very slowly if at all. Restarting MySQL helps right away, buying me another 24 hours of stable uptime. If this is a linux box it could be a problem with linuxthreads. Try upgrading to kernel 2.4.4 Details: From my.cnf: skip-locking skip-networking set-variable= max_connections=18 try raising max_connections set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=20M set-variable= thread_cache=8 set-variable= thread_concurrency=4 # Try number of CPU's*2 try raising thread concurrency. set-variable= query_buffer_size=16k set-variable= tmp_table_size=6M set-variable= delayed_insert_limit=15 set-variable= max_write_lock_count=1 - 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 - 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: Question regarding memory usage
On Mon, 7 May 2001, Rene Tegel wrote: On Mon, 7 May 2001 07:40:26 -0400 (EDT) [EMAIL PROTECTED] wrote: Thanks for the quick reply. I set max_connections so low because even at peaks I never need more, and when the slowdowns happen, it seems to have an easier time recovering if there are 15 slow connections hanging than if there are hundreds. Are you saying you have 15 slow queries and thus only 3 connections available for your site to run at time server crashes? that's wrong and probably one of the reasons your site slows down. I guess you have some script(s) with 'corrupt' queries that take loads of time (on non-indexed fields or something) to complete. So mysql gets more and more slow queries which may take hours to complete, whilst slowing down your site. You should check which script(s) are responsible for this, and fix the bugs. I'm not really talking about the web site slowing down here (of course it does), I'm talking about the queries themselves slowing down. I do have some queries which could use optimization, but they're in no way corrupt. All queries are run regularily during the 24 hour period during which the server runs fine. Then suddenly the same queries start taking several seconds, sometimes even minutes. This leads to the processes building up and waiting for locks, but at that point the server is already screwed; the 18 processes are busy and if I had max_connections at 200, then 200 processes would be busy after not too long. This system is getting an average of 4 page requests every second, it's fairly obvious that the battle is lost once queries start taking more than a few seconds. I'll try the thread concurrency thing, thanks. I was under the impression this variable was only effective on a Solaris box, but I may be wrong? you might be quite right. i just noticed it was not the default.. I suspect you may be right about linuxthreads being the problem. Unfortunately, I'm on a managed hosting solution, and I'm not sure if I want to risk a kernel panic and countless hours of expensive support :) you always could try if same database/scripts generate same errors on another box.. Too late, I already took the risk and upgraded to 2.4.4. Went smoothly :) So now I'll just sit tight and see how it goes. Hopefully, the problems are gone. Thanks for the help, Jon - 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: Question regarding memory usage
Hi, From a system point of view, there are 3 main reasons for a slowdown: - CPU : Your machine is slow because the CPUs are at 100% and can't do anything more. - I/O : Your processes are waiting for data from the disk. - RAM : You don't have enough RAM so your machine is swapping and all your processes run much slower. According to your first message, third reason seemed the most obvious. If your machine is not swapping, then first two reasons are good candidates. You really should run 'vmstat' while your server is slow and try to figure out where the time is spent. Also, you should check Apache status. Have you looked at server-status output ? How many BusyServers ? Are there any IdleServers left ? If all your servers are busy, it may be due to some performance problem on your server (as discussed above) but it may also come from slow clients that are just eating all your connections; in this case, raising your MaxClients parameter in Apache should help (don't forget to raise max_connections in mysql.cnf too). Hope this helps -- Joseph Bueno NetClub/Trader.com Jon Valvatne wrote: That's the weird part; it doesn't seem to be swapping at all. When trying different combinations in my.cnf, I had key_buffer as low as 64M without any effect. Jon On Mon, 7 May 2001, Joseph Bueno wrote: Hi, Are you sure that you need 200Mb of key_buffer cache ? Since your machine is obviously swapping a lot when it slows down (you can verify that with vmstat), I think you should try first to lower RAM usage. Use 'mysqladmin extended-status' and find out how much RAM you need for key_buffer (check 'Key_blocks_used' variable) and reduce key_buffer value. Hope this helps -- Joseph Bueno NetClub/Trader.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 - 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: query memory usage
"ryc" [EMAIL PROTECTED] wrote: When performing a query that will return a large dataset (by large I mean 100k+ rows), is it more effecient (memory usage wise) to use the results as you fetch them or fetch all the results into an array, free the statement handle, and the process from array? What about performance wise? I am using perl w/ DBI, but I assume it would be the same if I were using the C api as well. Processing the records as they are being returned by the query is faster and more efficient than writing the records to an array and then processing the array. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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
