ryanjohnson
New Member
Can someone help me with "my.cnf" configuration?
I had been playing around with this for the past few days and I cannot figure out what to do with it.
My server keeps using swap space every 6-12 hours and I'm not sure how to configure this.
Code: [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockmax_connections = 500skip-lockingkey_buffer = 256Mmyisam_sort_buffer_size = 32Mjoin_buffer_size = 1Mread_buffer_size = 1Msort_buffer_size = 2Mtable_cache = 1024thread_cache_size = 16Minteractive_timeout = 25wait_timeout = 1000connect_timeout = 10max_allowed_packet = 16Mmax_connect_errors = 10query_cache_limit = 1Mquery_cache_size = 32Mquery_cache_type = 1tmp_table_size = 16Mmax_heap_table_size = 16M#skip-innodbuser=mysqllocal-infile=0# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidopen_files_limit = 8192[isamchk]key_buffer = 256Msort_buffer = 256Mread_buffer = 16Mwrite_buffer = 16M[myisamchk]key_buffer = 256Msort_buffer = 256Mread_buffer = 16Mwrite_buffer = 16M[mysqlhotcopy]interactive-timeoutskip-networking#log-slow-queries = /var/log/mysql-slow.log lower the key_buffer and sort_buffer down to something like 128M or lower...
If its only for one site you don't need to have it that high Hi there,
Your max_connections also seems high. I would suggest you lower that value to something like 150. MySQL will set aside enough RAM to make use of those connections, so when you have the value set too high, you are doing more harm than good.
Regards,
Chris It's actually for 3 sites, but only one of them is really the resource hog.
These are the changes I made and I'll let you guys know how it works out in a few hours. Let me know if I missed anything, thanks!
Quote: [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 150
skip-locking
key_buffer = 128M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16M
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 16M
max_heap_table_size = 16M
#skip-innodb
user=mysql
local-infile=0
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[isamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
skip-networking
#log-slow-queries = /var/log/mysql-slow.log
---------- Post added at 12:21 AM ---------- Previous post was at 12:06 AM ----------
I just noticed a few processes being marked as "defunct", but i'm not really sure how bad this can be.
Defunct means a process that has quit aka a zombie process.
Have you tried updating your kernel and software to a up2date software and kernel? Even with the new my.cnf settings, the server started to use swap space again.
Could it be something else that maybe wrong?
Quote: Originally Posted by JamesVaporH
Defunct means a process that has quit aka a zombie process.
Have you tried updating your kernel and software to a up2date software and kernel? I'm not even sure how to do that. What's the command to upgrade on centos? just run a full update
Code: yum update
I had been playing around with this for the past few days and I cannot figure out what to do with it.
My server keeps using swap space every 6-12 hours and I'm not sure how to configure this.
Code: [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockmax_connections = 500skip-lockingkey_buffer = 256Mmyisam_sort_buffer_size = 32Mjoin_buffer_size = 1Mread_buffer_size = 1Msort_buffer_size = 2Mtable_cache = 1024thread_cache_size = 16Minteractive_timeout = 25wait_timeout = 1000connect_timeout = 10max_allowed_packet = 16Mmax_connect_errors = 10query_cache_limit = 1Mquery_cache_size = 32Mquery_cache_type = 1tmp_table_size = 16Mmax_heap_table_size = 16M#skip-innodbuser=mysqllocal-infile=0# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidopen_files_limit = 8192[isamchk]key_buffer = 256Msort_buffer = 256Mread_buffer = 16Mwrite_buffer = 16M[myisamchk]key_buffer = 256Msort_buffer = 256Mread_buffer = 16Mwrite_buffer = 16M[mysqlhotcopy]interactive-timeoutskip-networking#log-slow-queries = /var/log/mysql-slow.log lower the key_buffer and sort_buffer down to something like 128M or lower...
If its only for one site you don't need to have it that high Hi there,
Your max_connections also seems high. I would suggest you lower that value to something like 150. MySQL will set aside enough RAM to make use of those connections, so when you have the value set too high, you are doing more harm than good.
Regards,
Chris It's actually for 3 sites, but only one of them is really the resource hog.
These are the changes I made and I'll let you guys know how it works out in a few hours. Let me know if I missed anything, thanks!
Quote: [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 150
skip-locking
key_buffer = 128M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16M
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 16M
max_heap_table_size = 16M
#skip-innodb
user=mysql
local-infile=0
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[isamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
skip-networking
#log-slow-queries = /var/log/mysql-slow.log
---------- Post added at 12:21 AM ---------- Previous post was at 12:06 AM ----------
I just noticed a few processes being marked as "defunct", but i'm not really sure how bad this can be.
Have you tried updating your kernel and software to a up2date software and kernel? Even with the new my.cnf settings, the server started to use swap space again.
Could it be something else that maybe wrong?
Quote: Originally Posted by JamesVaporH
Defunct means a process that has quit aka a zombie process.Have you tried updating your kernel and software to a up2date software and kernel? I'm not even sure how to do that. What's the command to upgrade on centos? just run a full update
Code: yum update