MySQL Optimisation , its hangs , very very slow performance

cnsweb

Registered
Jan 5, 2014
1
0
1
cPanel Access Level
Root Administrator
My server is of 24GB Ram , 8 Core Processor

MY.CNF details please suggest best optimisation for this


Code:
[mysqld]
innodb_file_per_table=1
bind-address=127.0.0.1


open_files_limit=12424
[mysqld]
local-infile=0
datadir=/var/lib/mysql
skip-locking
#skip-innodb
#skip-networking
skip-name-resolve
skip-host-cache
safe-show-database
innodb_buffer_pool_size = 1G
#innodb_flush_method= O_DIRECT
innodb_additional_mem_pool_size= 1G
innodb_log_file_size= 5M
innodb_thread_concurrency= 8
innodb_flush_log_at_trx_commit= 0
innodb_log_buffer_size= 1M
innodb_file_per_table =1
query_cache_limit=1G
query_cache_size=1024M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=2000
max_connections=3000
interactive_timeout=360
wait_timeout=360
connect_timeout=360
thread_cache_size=128
table_cache=2000
key_buffer=3072M ## 128MB for every 1GB of RAM
join_buffer=1G
max_connect_errors=360
max_allowed_packet=1G

table_cache=4096
record_buffer=1G

sort_buffer_size=64M ## 1MB for every 1GB of RAM
read_buffer_size=64M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=128M  ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
collation-server=latin1_general_ci
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
 
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
 
[mysqldump]
quick
max_allowed_packet=1G
 
[mysql]
no-auto-rehash
#safe-updates
 
[isamchk]
key_buffer=5G
sort_buffer=5G
read_buffer=5G
write_buffer=5G
 
[myisamchk]
key_buffer=5G
sort_buffer=5G
read_buffer=5G
write_buffer=5G
 
[mysqlhotcopy]
interactive-timeout
 

cPanelPeter

Technical Analyst III
Staff member
Sep 23, 2013
575
20
143
cPanel Access Level
Root Administrator
Twitter
Hello,

Have you tried running mysqltuner.pl
yet?

If not, you should run that first and it will make the appropriate recommendations for you.