wsrep_cert_deps_distance as a performance indicator

[Kari Lehtinen]

Hi,

We have had some performance issue with two node ( + 1 garbd) XtraDB Cluster (Percona-XtraDB-Cluster-server-5.5.29-23.7.2.389.rhel6.x86_64). Servers are dedicated virtual machines with 2 vCPU / 16GB of RAM. We have had even under a small load some stalls now and then. I have noticed that usually before cluster starts to slow down IOwait jumps pretty high. First I tried to increase wsrep_slave_threads to 8 but without noticeable effect. Now increased value to 32 and so far I haven't seen big jumps in iowait values and I noticed that
wsrep_cert_deps_distance value jumped from 50-500 to almost 8k. So is possible that increasing wsrep_slave_threads to 32 has provided significant performance boost and does wsrep_cert_deps_distance -value tell overall performance of the cluster ?

-Kari

[Justin Ryan]

According to the documentation (http://www.codership.com/wiki/doku.php?id=galera_status_0.8), wsrep_cert_deps_distance is the:

Average distance between highest and lowest seqno that can be possibly applied in parallel (potential degree of parallelization).

so it is not an indication of overall cluster performance, but a hint on how many replication threads to use. 

If you've noticed high i/o wait, then your stalls are likely the result of your disk being overwhelmed. Look at the output of `iostat -x 1` --  if you see lots of reads, a larger innodb_buffer_pool_size (up to a reasonable percentage of physical memory) could cut down on some disk activity. Otherwise, for your workload you'll need faster disks, more disks in your RAID array, and/or to separate out logfiles and datafiles onto separate spindles. 

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Kari Lehtinen]

Thanks for your reply. Yes, it's probably more disk issue than any other. Here's some numbers from iostat during JMeter test I run http://paste.opensuse.org/87972260. I'm not an expert in storage systems, but those numbers doesn't look so good does it ?

[Ovais Tariq]

Hi Kari,

I have looked at the iostat output and the numbers look really bad, there is a very high amount of queuing of IO requests going on, because the storage subsystem is not able to keep up with the amounts of writes its receiving. You are definitely IO bound and the workload is mainly writes, I don't see any reads. You probably need to tune the MySQL configuration. You can share it here if you want me to look at it.

Best,

On Thu, Jun 20, 2013 at 3:38 PM, Kari Lehtinen <kar...@gmail.com> wrote:

Thanks for your reply. Yes, it's probably more disk issue than any other. Here's some numbers from iostat during JMeter test I run http://paste.opensuse.org/87972260. I'm not an expert in storage systems, but those numbers doesn't look so good does it ?

--

You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Ovais Tariq, Principal Support Engineer, Percona

http://www.percona.com | http://www.mysqlperformanceblog.com
Phone               : +1 (888) 401-3401 Ext. 552
24/7 Emergency : +1 888 401 3401 ext 911
Skype               : ovaistariq

Training : http://www.percona.com/training/
Support  : http://www.percona.com/mysql-support/

Percona Live London MySQL Conference 2013
http://www.percona.com/live/london-2013/

[Kari Lehtinen]

Thanks Ovais,

Here's my.cnf.

[mysqld]
log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
datadir=/srv/mysql/mysql_data
user=mysql
max_allowed_packet = 100M
default_storage_engine=InnoDB

log_slave_updates = 1
binlog_format=ROW
query_cache_size=0
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_slave_threads=32
wsrep_cluster_name=mycluster
wsrep_sst_method=rsync
wsrep_node_name=web01
wsrep_replicate_myisam=1
wsrep_provider_options="gcache.size=4G"

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 4M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1


-Kari

[Alex Yurchenko]

This is your problem. Set it to 2.

You also mentioned that your servers are dedicated VMs with 16Gb RAM.
In that case it may also help to some extent to set buffer pool size to
12Gb.

> -Kari
>
> On Friday, June 21, 2013 11:24:40 AM UTC+3, Ovais Tariq wrote:
>> Hi Kari,
>> I have looked at the iostat output and the numbers look really bad,
>> there
>> is a very high amount of queuing of IO requests going on, because the
>> storage subsystem is not able to keep up with the amounts of writes
>> its
>> receiving. You are definitely IO bound and the workload is mainly
>> writes, I
>> don't see any reads. You probably need to tune the MySQL
>> configuration. You
>> can share it here if you want me to look at it.
>> Best,
>>

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Kari Lehtinen]

Thanks Alex, I'll try that. Currently I'm getting normal readings without any IO lag and I have contacted our service provider to check from their side.

-Kari

[Ovais Tariq]

Kari,

I have also noted that you are probably using MyISAM:

wsrep_replicate_myisam=1

That might not be a good idea if you care about your data.

Best,

--

You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Kari Lehtinen]

Ovais,

Currently we use only InnoDB, so that's kinda legacy config.

Thanks,
Kari

[Ovais Tariq]

Kari,

Since you are all InnoDB, so you should consider increasing the buffer pool size, 4G is a low value here, you can increase it to 8G pretty safely, and that should have a notable increase in performance if the dataset size is bigger than 4G. The variable innodb_buffer_pool_size is the single most important variable when it comes to InnoDB performance.

Best,

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.