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