galera memory usage

[Daniel Black]

Has anyone put together a formula for memory usage of galera based off settings and/or status, like the SQL comments https://dev.mysql.com/doc/refman/5.5/en/memory-use.html

I'm making a munin plugin to graph the theoretical maximum usage of mysql/mariadb + galera so when people change settings we can get alerts if it works out to over the sane amount of physical memory available.

--
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.

[alexey.y...@galeracluster.com]

Daniel,

What Galera uses memory at least for:

1) certification index. Essentially it is in-memory key-value store. And
its size depends on how many key-value pairs you store there and how big
are the values.
2) writeset creation. It is more like a temp table, I guess - up to 4-8
megs it goes in RAM, then spills to disk. And that of course depends on
the number of concurrent writing transactions and the size of these
transactions.

So how much memory galera will use depends solely on the usage pattern
(and std::unordered_map implementation). There is absolutely no single
setting (like innodb_buffer_pool_size) that defines how much memory
galera may reserve for anything, or that would limit its memory use.

[Daniel Black]

----- Original Message -----
> Daniel,
>
> What Galera uses memory at least for:
>
> 1) certification index. Essentially it is in-memory key-value store.
> And its size depends on how many key-value pairs you store there and how
> big are the values.
> 2) writeset creation. It is more like a temp table, I guess - up to
> 4-8 megs it goes in RAM, then spills to disk. And that of course depends
> on the number of concurrent writing transactions and the size of these
> transactions.
>
> So how much memory galera will use depends solely on the usage pattern
> (and std::unordered_map implementation). There is absolutely no single
> setting (like innodb_buffer_pool_size) that defines how much memory
> galera may reserve for anything, or that would limit its memory use.

ok and kind of scary. I can see how normal operation should keep this operation to a fairly sane level.

Unless I've misunderstood the operation, fc_limit will place some soft limits around the certification_index but will ultimately depend on the key/value size per writeset.

Seems as though galera_recv will run a fatal error rather than a certification failure if a memory allocation failure occurs? Sure mysql will complain a lot louder (or let the OS complain) if it fails to allocate memory, however however it normally has limits, that if set correctly, will soft fail before that happens. Is a cert failure a better approach?

[alexey.y...@galeracluster.com]

On 2014-06-20 06:41, Daniel Black wrote:
> ----- Original Message -----
>> Daniel,
>>
>> What Galera uses memory at least for:
>>
>> 1) certification index. Essentially it is in-memory key-value store.
>> And its size depends on how many key-value pairs you store there and
>> how
>> big are the values.
>> 2) writeset creation. It is more like a temp table, I guess - up to
>> 4-8 megs it goes in RAM, then spills to disk. And that of course
>> depends
>> on the number of concurrent writing transactions and the size of these
>> transactions.
>>
>> So how much memory galera will use depends solely on the usage pattern
>> (and std::unordered_map implementation). There is absolutely no single
>> setting (like innodb_buffer_pool_size) that defines how much memory
>> galera may reserve for anything, or that would limit its memory use.
>
> ok and kind of scary. I can see how normal operation should keep this
> operation to a fairly sane level.
>
> Unless I've misunderstood the operation, fc_limit will place some soft
> limits around the certification_index but will ultimately depend on
> the key/value size per writeset.

Yes, and in practice you usually have few huge writesets, and one is
enough to explode the memory usage.

Also certification index cleanup is triggered based on the index size,
so fc_limit will hardly have much effect.

> Seems as though galera_recv will run a fatal error rather than a
> certification failure if a memory allocation failure occurs? Sure
> mysql will complain a lot louder (or let the OS complain) if it fails
> to allocate memory, however however it normally has limits, that if
> set correctly, will soft fail before that happens. Is a cert failure a
> better approach?

OOM is a local condition. We can't be certain that other nodes also ran
into it and declare certification failure on the same writeset.

Ok, there is one parameter that ultimately limits Galera RAM usage - it
is the writeset size limit. It is 2Gb and configurable in Galera 3 to be
smaller. However it is still hard to predict how much memory the key set
will expand to. It is usually several times of its size on disk (due to
non-trivial index structure). Galera 3 has fixed key sizes, so it may be
possible to establish the practical upper limit on certification index
size by generating transactions with the biggest number of keys (e.g.
inserting/deleting into a single PK column table). This will however
differ from OS to OS.