| MySQL grows until it crashes [message #3387] |
Sat, 09 August 2008 04:34  |
|
I have a 64-bit server (with 64-bit OS and MySQL) with 4 gigs of RAM and 2 gigs of swap. We run MySQL 5.0.22 (installed from the CentOS 5.0 RPM). Nothing else runs on the server.
We have a problem where MySQL eats up more and more RAM until it starts swapping, then it eats up the swap until the OS kills it because there is nothing left to be allocated. It takes weeks sometimes for it to creep to the limit, but performance drops over time as well, due to swapping.
The database is extremely write-heavy, uses InnoDB almost exclusively (some logs are stored in MyISAM), and is about 300 gigabytes in size (~90 billion rows total).
MySQL is configured as follows:
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_size=2048M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=4M
innodb_log_file_size=512M
innodb_thread_concurrency=8
key_buffer_size=32M
max-connect-errors=10000000
table_cache=1024
thread_cache=32
transaction-isolation=READ-COMMITTED
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=8M
MySQL has been up for about six days right now and is using 5770MB of memory. Even with shared libraries and other such memory use, I can't figure out how it is using that much memory based on that configuration. How can I fix my configuration? Per the InnoDB tuning guide on the blog, I should be able to set the buffer pool size to be 70% of total RAM, which would cause MySQL to crash within hours.
I'd like to work on performance tuning, but as long as MySQL is crashing, I don't know how I can.
I appreciate any help. I'm sure I'm missing something simple.
|
|
|
|
| Re: MySQL grows until it crashes [message #3393 is a reply to message #3388 ] |
Sat, 09 August 2008 13:07  |
|
MySQL was installed using yum (for configuration management between a handful of servers, I'd rather not compile from source):
yum install mysql-server
It's directly from the CentOS Extras.
That is the bulk of /etc/my.cnf. The whole file is as follows:
[mysqld]
datadir=/var3/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
safe-show-database
key_buffer_size=32M
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=512M
innodb_log_buffer_size=4M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
max-connect-errors=10000000
table_cache=1024
thread_cache=32
transaction-isolation=READ-COMMITTED
query_cache_size=32M
read_buffer_size=2M
server-id=3
sort_buffer_size=8M
log-bin=/home/junk/mysql/mysql-bin
tmpdir=/var3/tmp
report-host=johnson.junk.ws
max_connections=60
[mysql.server]
user=mysql
basedir=/var3/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[Updated on: Sat, 09 August 2008 13:07]
|
|
|
| Re: MySQL grows until it crashes [message #3395 is a reply to message #3387 ] |
Sun, 10 August 2008 05:27 |
 Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
OK so it's vanilla MySQL and the config looks fine.
50% for InnoDB is normal, and your per connection settings appear to be OK.
What kind of swappiness setting do you use?
http://kerneltrap.org/node/3000
I honestly doubt the swappiness is the problem, it's sounds like a residual memory leak due to something in your setup.
If it was me I'd be inclined to do a fresh OS install etc, but with 300 Gb that's going to hurt.
Martin Gallagher | Speeple: The latest news
|
|
|
| Re: MySQL grows until it crashes [message #3447 is a reply to message #3395 ] |
Mon, 18 August 2008 23:29 |
|
Yeah, I looked into the swappiness setting a long while ago. It's set at the default (60) now. It doesn't affect performance much since MySQL is the only thing running anyway.
We have the out of memory error come up because the server doesn't have enough swap (we have 2 gigs of swap for 4 gigs of RAM, which isn't ideal... if I had a chance to go back and smack the datacenter tech, I probably would). But even still, I just can't figure out why MySQL is using so much RAM.
I guess I'll just wait until December when we get new servers anyway. *sigh* Maybe I'll look into PostgreSQL 
|
|
|
Members
Search
Help
Register
Login
Home
