0

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?

1 Answer 1

1

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.

3
  • Clarification on SLEEP() -- if inside a transaction, SLEEP lock other threads from touching things already locked in this transaction. However, SHOW is not affected.
    – Rick James
    Commented Nov 3, 2017 at 22:00
  • So just to confirm, its ok for me to run the select sleep() in production without any impact on service during the process of calculating the gcache? Commented Nov 4, 2017 at 4:01
  • @TheGeorgia Yes in this case you can run it without impact on service.
    – HamoonDBA
    Commented Nov 4, 2017 at 4:46

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.