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]
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]
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
