Kevin, The Mysql documentation suggests you use no more than 75% or 80% of physical memory to allocate for key_buffer. As Heikki and Jeremy confirmed for me this week, sort_buffer and record_buffer are the ones that grow per thread - Heikki suggested 1Mb for each of those. Using swap as RAM (as Kyle suggests) is not a good idea for any process, as it will certainly slow your system.
John Kyle Hayes wrote: > If you only have 2GB of RAM and you are allocating 5120M (=5GB) for the > key_buffer, you have a problem. That should make your system swap like > mad and everything slow to a crawl. Also, note that some of these config > options may effect per-thread allocation. I don't know which ones off the > top of my head, but I think the manual makes it clear. > > Things to remember: MySQL stores index data in the key_buffer. It does > not store data there. MySQL does not cache data. If you tell it to > allocate more memory than the system has, you'll use up all memory for the > index, allocate swap for it (really slow), and leave no space for the OS > to cache data. We usually allocate between 30 and 50% of the available > DRAM for the index and leave the remaining things much smaller. This lets > the OS do some caching on its own. If you take all the memory for the > indexes of MySQL, I am amazed that it was able to run with any speed at > all. What it looks like is that you allocated your 2G of memory and then > wandered quickly into the weeds. > > Swap != RAM. > > Best, > Kyle > > On Friday 18 January 2002 07:37, Franklin, Kevin wrote: > [snip] > >>The behavior suggests that we are running out of memory / swap, but we >>have over 2 gig of memory and 10 gig of swap free. >> > > If you hit swap, you hit the wall in performance and go splat. We run PCs > with this much RAM. RAM=Performance with MySQL. Even for Sun's RAM is > pretty cheap. > > >>Our server settings are: >>key_buffer=5120M >> > > Danger Will Robinson! This is larger than your RAM! > > >>max_allowed_packet=1M >>table_cache=1024 >>sort_buffer=6M >> > > This is pretty big and allocated on a thread by thread basis I think. > > >>record_buffer=4M >> > > This might be allocated on a thread by thread basis too, but I can't > remember. Hmm, looks like it is. Do you run absolutely huge queries? Do > you really need 10MB _per thread_? > > >>thread_cache=12 >>thread_concurrency=12 >> > > You have a lot of processors? If so, you don't have much RAM. > > >>myisam_sort_buffer_size=512M >> > > This is somewhat high given that you've already use all available RAM for > indexes more than twice over. > > >>The server tends to crash upon reaching a total memory usage of around 4 >>GB >> > > I am surprised that it responds at all after it uses up RAM and starts to > swap. You must have a good disk subsystem. > > >>Here is the output from the error log. Of particular interest to us is >>the negative key_buffer_size quoted. The same value (-4096) appears >>with each crash. Is there some sort of memory limit imposed on the >>server or do you have suggestions for debugging this problem? >> > > I think that you rolled a 32-bit integer somewhere. Try setting > key_buffer to 1G. This could be a bug in MySQL. See below. This might > mean that it is a 32-bit executable. > > >>mysqld got signal 11; >> > > That's not good. Is your ulimit set to 2G for MySQL. Is MySQL actually a > 64-bit executable? Perhaps it is a 32-bit executable. > > >>key_buffer_size=-4096 >> > > ^^^^^^^^ ???? This doesn't look good. > > >>record_buffer=4190208 >>sort_buffer=6291448 >>max_used_connections=308 >>max_connections=1024 >>threads_connected=309 >>It is possible that mysqld could use up to >>key_buffer_size + (record_buffer + sort_buffer)*max_connections = >>2093044 K bytes of memory >> > > Read this line carefully. I think the server is trying to tell you > something. Note that it is really using -4096 as the key_buffer_size in > the calculation above. Note that the math seems to be wrong too since you > set record_buffer to 4M and sort_buffer to 6M, you should be allocating > 10M per thread. That's a lot of RAM for a system with only 2G. > > >>Hope that's ok, if not, decrease some variables in the equation >> > > I think it has the right idea here. > > Best, > Kyle > > --------------------------------------------------------------------- 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
