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 -- Cut costs, Fax smart. Use iPrint2Fax worldwide and save! - iPrint2Fax software is FREE, no fax modem required. - FREE iPrint2Fax to a single email address. - iPrint2Fax to PSTN based Fax (Up to 95% Savings) - iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails in the time it takes to send just one! ========================================== FREE software download available at www.iPrint2Fax.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
