mysql - Very slow response even with a high number of available connection

Question

I'm running this server for data mining purposes. It runs several compute intensive data mining applications parallely and does simultaneous access to the MySQL server.

Here are the configuarations.

Server config: 8 core Intel Xeon, 16gb RAM, 500 GB SAS drive

MySQL my.cnf

[client]
#password   = [your_password]
port        = 3306
socket      = /var/lib/mysql/mysql.sock

[mysqld]
# generic configuration options
port                          = 3306
socket                        = /var/lib/mysql/mysql.sock
datadir                       = /database/mysql
log_bin                       = OFF
expire-logs-days              = 3
pid-file                      = /database/mysql/localhost.localdomain.pid
back_log                      = 50
max_connections               = 3000
max_connect_errors            = 100
table_open_cache              = 2048
max_allowed_packet            = 16M
binlog_cache_size             = 1M
max_heap_table_size           = 64M
read_buffer_size              = 128M
read_rnd_buffer_size          = 32M
sort_buffer_size              = 32M
join_buffer_size              = 8M
thread_cache_size             = 8
thread_concurrency            = 4
query_cache_size              = 64M
query_cache_limit             = 2M
ft_min_word_len               = 4
default-storage-engine        = innodb
thread_stack                  = 192K
transaction_isolation         = REPEATABLE-READ
tmp_table_size                = 64M
log-bin                       = mysql-bin
binlog_format                 = mixed
server-id                     = 1
key_buffer_size               = 32M
bulk_insert_buffer_size       = 64M
myisam_sort_buffer_size       = 128M
myisam_max_sort_file_size     = 10G
myisam_repair_threads         = 1
myisam_recover
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size       = 4G
innodb_data_file_path         = ibdata1:10M:autoextend
#innodb_data_home_dir         = <directory>
innodb_write_io_threads       = 8
innodb_read_io_threads        = 8
#innodb_force_recovery        = 6
innodb_thread_concurrency     = 0
innodb_flush_log_at_trx_commit= 2
#innodb_fast_shutdown
innodb_log_buffer_size        = 8M
innodb_log_file_size          = 1G
innodb_log_files_in_group     = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct    = 90
#innodb_flush_method          = O_DSYNC
innodb_lock_wait_timeout      = 120

[mysqldump
quick
max_allowed_packet            = 16M

[mysql]
auto-rehash

[myisamchk]
key_buffer_size               = 512M
sort_buffer_size              = 512M
read_buffer                   = 8M
write_buffer                  = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit              = 8192

There are only 2 users who access this server which includes me. On peak hour I get this

mysql > show processlist
...
120 rows in set

Which shows that around 120 connections are established to the mysql server during peak computation hours. MySQL consumes around 9.5gb of memory and uses 98-99% CPU which I can still live with. But during this time a front end site build with php/javascript takes around 1 - 2 min to load which is because mysql responds very slowly during these hours. While normally it takes somewhere around 890ms to 4 seconds.

I want to know how to further optimize the mysql server configuration. Currently as can be seen from the posted my.cnf , buffer pool is at 4GB and max number of connections are set at 3000 . All the tables are Innodb with proper indexes, but in my case transaction safety is not a issue the main and the only issue is performance. The data mining applications uses MySQL C API Connector and each has around 24 parallel threads running which equals to 24 simultaneous connections to MySQL

How can I further optimize the mysql server configuration so that I may get a reasonable response time of around 10 - 15 seconds for front end access . Please let me know if there is any way to optimize this further.

Answer 1

You really should dedicate another server just for data mining and set up replication between your MySQL servers. Data mining application should use transactions to union multiple small queries into blocks. This way your site will not wait for other queries to be executed and synchronization would be made in background without visible lag.

Another options is to cache as much as possible and hope that user will not request data that is not in cache when heavy hours are.

But I prefer to do both of this things so you'll have 100% reliable service.