MySQL – Calculating Galera gcache for PXC

MySQLpercona

I was trying ti figure out how to calculate the right gcache for my server and came across the following post discussing just how to do that:

https://www.percona.com/blog/2014/09/08/calculate-correct-size-percona-xtradb-clusters-gcache/

In the post, the talk about doing the following to get the values needed for the calculation:

show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';
select sleep(60);
show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';

My question is, will running the select sleep(60) in production block all reads on MySQL (by keeping the mysqld thread busy)? How can i calculate the best gcache value in production?

Best Answer

The answer to your first question is No

When you execute Sleep function, it only block current session and not related to another sessions. So all other sessions work properly

But about your second question.

Note: gcache used to store only writesets in circular buffer style.

Usually a service have a Peak time that users have huge interactive to it.

So at the peak time of the system execute those queries that you said in your question.

After that use this formula to find writesets data bytes per minute

(second wsrep_received_bytes – first wsrep_received_bytes) + (second wsrep_replicated_bytes – first wsrep_replicated_bytes)

Well, now you can decide How much time you want to keep changes to server.

For example if writesets data is 10 MB per minutes and you plan to keep changes data to 24 hours you need to set gcache size to 14400 MB.

Note: In my opinion for more certainty add 20% more to writesets data bytes per minute. In this case for example consider 12 MB instead of 10 MB.

Related Question