Date: 04/15/08 (MySQL Communtiy)    Keywords: mysql, sql

   I'm getting ready to upgrade one of our core DB's to 5.0.54 from 5.022, but I figure I am still a week away from that time and in the interim, the machine in question is somewhat spooking/scaring me.   It's been setup to use no more then 6GB of memory and verified to be this way by a couple different "tuning" scripts... yet top is showing the process taking 5.7GB of resident ram and another 2.3GB of swap.  So far there hasn't been any performance issues besides it killing the machine this last sunday from a complete lack of free memory (resident or swap).    

   Has anyone else experienced a similar situation where a MySQL setup using more memory then its been allocated or am I missing something here?     I know one of the BIG problems we have is that in development I had planned to use triggers to handle keeping a series of "materialized" view tables syncronized.  After Insert/Update abbreviated copies of records would be inserted/updated to three sub-tables ( pending 5.1 going stable and the availability of table partitioning)... so in the interim there is a cronjob of doom that updates the other tables based on a record time-stamp field.  Also another department used to have a 2.3GB pdf InnoDB table, but I had them downgrade that to a MyISAM table as they really didn't need transactions for what they were using it for. 



skip-bdb

#old_passwords=1
skip-locking #DJW - Why/who the hell set this?  This is set by default last I checked.

key_buffer = 64M #20080325 - Benchmark for 1 month
max_allowed_packet = 64M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

thread_cache_size = 8
thread_concurrency = 8

#DJW - changed from 32
query_cache_size = 64M #Decrement to 48MB by 20080416
query_cache_limit = 3M

tmp_table_size=64M
max_heap_table_size=64M

back_log = 100
max_connect_errors = 10000
join_buffer_size=1M
open-files = 20000

interactive_timeout = 600
wait_timeout = 600

#DJW - 20080324 from 100
#DJW - 20080413 FROM 210 - DO NOT TAKE THIS HIGHER, RHEL5 doesn't appear to handle out of memory events ( normally would kill processes) gracefully
max_connections=180

################################################################################################
####INNODB flags
################################################################################################
innodb_buffer_pool_size = 4500MB
innodb_log_file_size = 500MB
innodb_log_buffer_size = 32MB
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=12
innodb_flush_method=O_DIRECT
#DJW - todo research this for usefulness
#transaction-isolation=READ-COMITTED
innodb_file_per_table 

         

     

Source: http://community.livejournal.com/mysql/127486.html