Azure memory management

Question

This is probably an Azure question but i can't get a sensible response from MS.

Did anything change on Azure's memory management for MariaDB and MySQL in the last few months?

I'd been running 5 MariaDB servers on Azure for 2 years or so with no problems. Then about 2 months ago all 5 started reaching 100% memory and crashing. MS could only tell me it was "workload" and to increase (i.e. double) the vCores/memory.

The workload hasn't changed in those 2 years but I did that and the servers ran ok. But at twice the expense.

MariaDB is being retired on Azure anyway so i brought forward my migration to FLex MySQL servers.

I'm finding that these MySQL servers are also hitting 100% memory every few hours... but at least they don't crash; they just kick users out and reduce memory usage and carry on. Nevertheless i'd like to keep this to a minimum.

I believe the usual formulae for calculating memory usage is "different" in Azure; certain parameters in Azure (e.g. max_connections and max_allowed_packet) default to extremely high values that would normally be discouraged. I've reduced them but no change in memory usage.

Slow Queries isn't telling me anything interesting.

There may be lots of smallish improvements to be made to some queries but the fact remains that this was perfectly stable for 2 years... then suddenly across 5 servers there's now a problem.

Each server has about 80 - 100 customer databases of identical structure. About 110 tables and 30 views in each database. Triggers for insert, update and delete on about half the tables. Databases range is size from 50mb to 100GB. About 150 concurrent connections on each server at any one time.

Any thoughts?

Answer 1

Update for anyone else struggling on Azure....

.... although we're encouraged to stick with Azure's default server settings they aren't limiting MySQL/MariaDB to any realistic memory maximum.

using this

SELECT (
(
(@@innodb_buffer_pool_size) +
@@innodb_log_buffer_size +
@@key_buffer_size +
( @@max_connections *
( @@binlog_cache_size +
@@binlog_stmt_cache_size +
@@bulk_insert_buffer_size +
@@join_buffer_size +
(@@max_allowed_packet) +
@@read_buffer_size +
@@read_rnd_buffer_size +
@@sort_buffer_size +
@@thread_stack +
@@tmp_table_size
)
) +
+
( @@open_files_limit * 1024)
) / 1024 / 1024 / 1024) AS max_memory_GB;

the default settings give > 150GB of memory.

By reducing max_connections, Max_allowed_packet and innodb_buffer_pool_size i was able to set the max memory to < 32GB and things are now stable.