6

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results.

I used sysbench v0.5 for the tests:

prepare data

 sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
 --oltp-test-mode=complex --oltp-table-size=1000000 \
 --mysql-db=mydb --mysql-user=root --mysql-password=mypassword prepare

do the test

 sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
 --oltp-test-mode=complex --oltp-table-size=1000000 --oltp-read-only=off \
 --num-threads=6 --max-time=60 --max-requests=0 \
 --mysql-db=mydb --mysql-user=root --mysql-password=mypassword run

As you can see from the results below, master-master replication (percona with 3 machines) had the worst performance, then comes mySQL master-slave (2 machines) configuration and the fastest is mySQL as a single standalone server.

Is this the normal situation with replication solutions? It seemed so damn slow, 10x difference between the configurations looks abnormal to me. Maybe I am missing something... I was totally disappointed about Percona Galera Cluster, it has a reputation for being fast for innodb. Phew :)

Please check the information provided below and advise, thank you.

About the servers

Hardware

  • Intel® Xeon® E5-1650 v2 Hexa-Core
  • 64 GB ECC RAM
  • 2 x 240 GB 6 Gb/s SSD Datacenter Edition (Software-RAID 1)
  • 1 Gbit/s bandwidth

OS

  • Debian Wheezy
  • All packages updated/upgraded.

Connection etc.

  • Servers are at the same datacenter, all connected with a Gbit switch and have second ethernet cards, all configured for private networking between them.

  • Currently, there is no load on the servers.

Disk performance

first test

# hdparm -Tt /dev/sda

/dev/sda:
 Timing cached reads:   27166 MB in  2.00 seconds = 13599.63 MB/sec
 Timing buffered disk reads: 1488 MB in  3.00 seconds = 495.64 MB/sec

second test

# dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/output
 10240+0 records in
 10240+0 records out
 83886080 bytes (84 MB) copied, 0.0517404 s, 1.6 GB/s

/etc/my.cnf Configuration

  • Percona's wizard was used for the initial configurations.

  • I used the official manuals/websites and other reliable sources in order to learn and configure the replication.

  • InnoDB was used for default storage engine (the test table created by sysbench is also InnoDB)

Percona XtraDB v5.6 Galera Cluster: my.cnf for the first node

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[sst]
streamfmt=xbstream

[xtrabackup]
# compress
# compact
parallel=8
compress-threads=8
rebuild-threads=8

[mysqld]
wsrep_node_name=db1
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL
wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6
# This changes how |InnoDB| autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.1.4
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_cluster
# Authentication for SST method
wsrep_sst_auth="replicateuser:replicateuserpassword"

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# Rolandomysqldba's recommendation
innodb_thread_concurrency       = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

# MyISAM #
key_buffer_size                = 32M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max_allowed_packet              = 16M
max_connect_errors              = 1000000
skip_name_resolve
sysdate_is_now                  = 1
innodb                          = FORCE

# 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                = 0
query_cache_size                = 0
max_connections                 = 500
thread_cache_size               = 50
open_files_limit                = 65535
table_definition_cache          = 4096
table_open_cache                = 8K

# 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         = 42G

# LOGGING #
long_query_time                 = 5
log_error                       = /var/log/mysql/error.log
log_queries_not_using_indexes   = 1
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

Sysbench Test Results

mySQL v5.6.21 (Single)

transactions: 101001 (1683.29 per sec.)

mySQL v5.6.21 Replicated (Master + 1 Slave)

Master and Slave online

transactions: 10501  (174.95 per sec.)

Slave was offline, binlog sync was off

transactions: 11280  (187.86 per sec.)

Slave was offline

transactions: 10779  (179.55 per sec.)

With master to slave delay (1 hr)

transactions: 10595  (176.48 per sec.)

MariaDB v5.5 (Single)

transactions: 73683  (1228.00 per sec.)

Percona XtraDB v5.6 Galera Cluster (3 Master-Master, xtrabackup-v2)

Master (initial node)

transactions: 703    (11.65 per sec.)

Test on another node

transactions: 643    (10.67 per sec.)

Changing replication method to rsync

transactions: 652    (10.80 per sec.)
Improve this question

2 Answers 2

Reset to default
2

You should put everything on a level playing field. How ?

Without proper tuning, it is possible for older versions of MySQL to outrun and outgun new versions.

Before running SysBench on the three environments

  • Make sure all InnoDB settings are identical for all DB Servers
  • For the Master/Slave, run STOP SLAVE; on the Slave
  • For PXC (Percona XtraDB Cluster), shutdown two Masters

Compare the speeds of just standalone MySQL, Percona, and MariaDB.

ANALYSIS

If MySQL is best (Percona people, please don't throw rotten vegetables at me just yet. This is just conjecture), run START SLAVE;. Run SysBench on the Master/Slave. If the performance is significant slower, you may have to implement semisynchronous replication.

If PXC is best, you may need to tune the wsrep settings or the network itself.

If MariaDB is best, you could switch to MariaDB Cluster (if you have the Money) or setup Master/Slave with MariaDB. Run Sysbench. If the performance is significant slower, you may need to tune the wsrep settings or the network itself.

Why tune wsrep settings ? Keep in mind that Galera wsrep (WriteSet Replication) uses virtually synchronous commits and rollbacks. In other words, either all nodes commit or all nodes rollback. In this instance, the weakest link would have to be

  • how fast the communication between Nodes happens (especially true if the Nodes are in different data centers)
  • if any one node has underconfigured hardware settings
  • if any one node communicates slower than other node

Side Note : You should also make sure tune MySQL for multiple CPUs

UPDATE 2014-11-04 21:06 EST

Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):

This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.

SUGGESTION #1

For PXC, turn off one node. Run SysBench against a two node cluster. If the write performance is better than a three node cluster, then it is obvious that the communication between the nodes is the bottleneck.

SUGGESTION #2

I noticed you have a 42GB Buffer Pool, which is more than half the server's RAM. You need to partition the buffer pool by setting innodb_buffer_pool_instances to 2 or more. Otherwise, you can expect some swapping.

SUGGESTION #3

Your innodb_log_buffer_size is 8M by default. Try making it 256M to increase log write performance.

SUGGESTION #4

Your innodb_log_file_size is 512M. Try making it 2G to increase log write performance. If you apply this setting, then set innodb_log_buffer_size to 512M.

Improve this answer
4
  • 1
    you have so many good contributions to the community, thanks for taking the time to post the links.
    – cenk
    Commented Nov 4, 2014 at 21:10
  • Actually, I tested with the same settings! Is it usual that Percona XtraDB Cluster < MySQL Master Replication < Single MySQL? It was very slow. 10 transactions per second?!
    – cenk
    Commented Nov 4, 2014 at 21:12
  • Thank you for the suggestions, I am working on it and had raised the transactions to 1900 per sec. with a single server.
    – cenk
    Commented Nov 6, 2014 at 3:16
  • You should add whatever changes you made for the PXC server to MariaDB and MySQL and benchmark them to see if their numbers have correspondingly increased.
    – RolandoMySQLDBA
    Commented Nov 6, 2014 at 15:11
0

I will update my progress and other stuff I discovered below.

Checking /etc/init.d/mysql

mySQL starts with this

su - mysql -s /bin/sh -c "/usr/bin/mysqld_safe > /dev/null 2>&1 &"

The error log showed that 65535 open_files_limit directive was not successful. Simply, mysql user could not set its own limits.

Raising ulimit nofiles for all users (enabling open_files_limit)

Checking current allowance on Debian

su mysql -s /bin/sh -c "ulimit -a"

/etc/security/limits.conf

# Added these
* soft nofile 65535
* hard nofile 65535
* soft nproc 10240
* hard nproc 10240

/etc/pam.d/su

# Find and uncomment the following:
session    required   pam_limits.so

Sysctl Tuning on all servers

Added to /etc/sysctl.conf and did sysctl -p

#mo swap
vm.swappiness = 0
kernel.sysrq = 0

net.core.somaxconn = 65535
net.ipv4.conf.default.rp_filter=1

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_congestion_control = cubic
net.ipv4.tcp_keepalive_time = 900
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 15

# Increase system file descriptor limit
fs.file-max = 100000

# Discourage swap
vm.swappiness = 0

# Increase ephermeral IP ports
net.ipv4.ip_local_port_range = 1024 65535

# Increase Linux autotuning TCP buffer limits
# Set max to 16MB for 1GE and 32M (33554432) or 54M (56623104) for 10GE
# Don't set tcp_mem itself! Let the kernel scale it based on RAM.
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.rmem_default = 16777216
net.core.wmem_default = 16777216
net.core.optmem_max = 40960
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# Make room for more TIME_WAIT sockets due to more clients,
# and allow them to be reused if we run out of sockets
# Also increase the max packet backlog
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 30000
net.ipv4.tcp_max_tw_buckets = 2000000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 10

# Disable TCP slow start on idle connections
net.ipv4.tcp_slow_start_after_idle = 0

# If your servers talk UDP, also up these limits
net.ipv4.udp_rmem_min = 8192
net.ipv4.udp_wmem_min = 8192

# Disable source routing and redirects
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.all.accept_source_route = 0

# Log packets with impossible addresses for security
net.ipv4.conf.all.log_martians = 1

Current status

 transactions: 113132 (1885.45 per sec.)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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