title: mysql - Very slow response even with a high number of available connection
body: <p>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.</p>
  
  <p>Here are the configuarations.</p>
  
  <p>Server config:  8 core Intel Xeon, 16gb RAM, 500 GB SAS drive</p>
  
  <p>MySQL my.cnf</p>
  
  <pre><code>[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         = &lt;directory&gt;
  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
  </code></pre>
  
  <p>There are only 2 users who access this server which includes me. On peak hour I get this</p>
  
  <pre><code>mysql &gt; show processlist
  ...
  120 rows in set
  </code></pre>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>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.</p>