Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

enter image description here

[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 ~]#
share|improve this question
    
Do you have a specific question? –  Max Vernon Nov 4 '13 at 3:42
    
My question is what and why I should change in my database configuration to use more resources of my machine. –  Jason Nov 4 '13 at 4:43

1 Answer 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.

share|improve this answer
    
Thank you! I'll add those updates now. Also I set 15000 just to prevent connection errors –  Jason Nov 4 '13 at 7:36
    
Hmm.. somehow it seems like it made my server slower. Before I was making around 500-900 inserts per second.. now I have 150-400 –  Jason Nov 4 '13 at 7:41
    
Enabling double-write buffer (safer) means slower, too. –  Shlomi Noach Nov 5 '13 at 8:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.