Squeezing Maximum Out Of My Server I have a dedicated server only built to hold MySQL databases.

Currently I have a database that has 173 million records and increasing by 5-15+ million records very day.

My question is what and why I should change in my database configuration to use more resources of my machine.

Machine:

2x Intel E5506 @ 2.14 Ghz
2 x 1000GB SATA 3 RAID 1
24 GB RAM


Software:

CentOS 6.4
MySQL Server 5.5


MySQL configuration file:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 10M
max-connections                = 15000
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 18G

# INNODB MULTITHREADED #
innodb_thread_concurrency      = 0
innodb_concurrency_tickets     = 10
innodb_thread_sleep_delay      = 5000
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_replication_delay       = 5000
innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc          = ON
innodb_doublewrite             = OFF

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log


I have noticed that my queries are getting slower.

Some update queries take about 48-50 seconds.

Here's how it looks:



[root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log
# Time: 131113 17:09:48
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 61.013294  Lock_time: 0.000409 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380588;
UPDATE b SET nf=0, lt=0, ud=1384380501, a='Trademarks' 
  WHERE parent_id=36019263 AND link=3780235;
# Time: 131113 17:09:49
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 18.145112  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380589;
SELECT id FROM b WHERE parent_id=62921670 AND link=4808881;
[root@db-1 ~]#
To start with, more interesting than the number of records is the size on disk, which we can then compare to your available memory.

Consider the following settings:

innodb_flush_log_at_trx_commit = 1(2)?


This variable appears twice in your cnf file. You probably meant to give it the value 2, which make for less I/O. But you may loose 1 second's worth of transactions upon failure.

You also have:

innodb_doublewrite             = OFF


Which is risky. True, it will make for less I/O, but may also lead to corruption upon failure. I only set this on servers I can spare and do not mind losing.

Set

query-cache-type               = 0


Enough has been written on the query cache that I'll not repeat here; it is an old technology and does not scale to today's machines.

Do yourself a favour and set

long_query_time = 1


Since the 10 second default is way too high to catch the bad guys.

I know nothing about the usage of your DB, but I'm guessing here that

max-connections                = 15000


is waaaay out of proportion. Will you really have 15000 concurrent connections? If you will, then a 18G for innodb_buffer_pool_size is way too high. Connections have their own memory.

All the above off the top of my head. Certainly need more information about your use case.