SeAT - 💬-general - Page 5

But no tuning of fs either

# Before
#join_buffer_size=2M
#innodb_buffer_pool_size=2G

# After
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
join_buffer_size = 256K
aria_pagecache_buffer_size = 256M

One of the aims of v6 will also be postgres support... So I wonder how we could go about comparing it vs mariadb. I hear so mamy people telling me it's better faster more efficient etc, but never able to quantify it on any current projects. So I want to get some numbers

kinda looks like it stopped ... but it's running alright

see also 😂

and

I think step 0 is some very basic performance tuning 🙂

lol

sorry for the graph spam

I ran this to get an idea of how things were doing:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';

I got 98.95% hit ratio before, 99.893% after ...

So I am not that client with running a db. I wonder if we can generify a tuning guide so all can benefit.

I'll try this later when able

there's a couple of things that should be standard, imo

e.g: innodbflushlogattrxcommit = 2 joinbuffer_size = 256K

SHOW GLOBAL VARIABLES 
WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'innodb_flush_log_at_trx_commit',
    'innodb_log_file_size',
    'innodb_log_buffer_size',
    'join_buffer_size',
    'aria_pagecache_buffer_size'
);

this might be worth running first 🙂

before changing anything

sql
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| aria_pagecache_buffer_size     | 134217728 |
| innodb_buffer_pool_size        | 134217728 |
| innodb_flush_log_at_trx_commit | 1         |
| innodb_log_buffer_size         | 16777216  |
| innodb_log_file_size           | 100663296 |
| join_buffer_size               | 262144    |
+--------------------------------+-----------+
6 rows in set (0.000 sec)

sql
MariaDB [seat]> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0            |
| Innodb_buffer_pool_read_ahead         | 19457263     |
| Innodb_buffer_pool_read_ahead_evicted | 3410665      |
| Innodb_buffer_pool_read_requests      | 324161205755 |
| Innodb_buffer_pool_reads              | 5294688522   |
+---------------------------------------+--------------+
5 rows in set (0.002 sec)
MariaDB [seat]> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 86       |
| Created_tmp_tables      | 42968561 |
+-------------------------+----------+
2 rows in set (0.001 sec)

I haven't looked into what the numbers mean vs what is good or not

That is from an instance of approx 1200 active tokens, no real heavy traders that I know of. Default docker installation. 2m bucket updates. 67 day uptime.

It would be a lot less if I could replace my assembly of smaller drives with a couple larger ones... But that's not going to happen this decade

Woah...

Insane energy costs?

It's time of use based, but averages at $0.44/kwh

Peak times are now approaching $0.70/kwh

I now avoid a lot of it to be fair with some solar and a battery so it's no longer costing me that.. But capital investment in that system was also not small

What the fuck

Also the usage charge is on top of the flat $2.10 per day as well.. And I also count myself a bit lucky for being in not the most expensive parts of the country either (Australia)

Physical server running seat...

yikes... have you done any math on your effective per kWh rate including solar?

first I'd log into the mariadb instance and run this to see how things are looking:

SHOW GLOBAL VARIABLES 
WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'innodb_flush_log_at_trx_commit',
    'innodb_log_file_size',
    'innodb_log_buffer_size',
    'join_buffer_size',
    'aria_pagecache_buffer_size'
);
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';

then do free -m on the host

https://github.com/major/MySQLTuner-perl

You need to apply a basic modicum of sanity to its recommendations, but otherwise this is nearly the gold standard

+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| aria_pagecache_buffer_size     | 134217728 |
| innodb_buffer_pool_size        | 134217728 |
| innodb_flush_log_at_trx_commit | 1         |
| innodb_log_buffer_size         | 16777216  |
| innodb_log_file_size           | 100663296 |
| join_buffer_size               | 262144    |
+--------------------------------+-----------+
6 rows in set (0.001 sec)

MariaDB [seat]> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 21884112   |
| Innodb_buffer_pool_read_ahead_evicted | 0          |
| Innodb_buffer_pool_read_requests      | 3300533632 |
| Innodb_buffer_pool_reads              | 57062426   |
+---------------------------------------+------------+
5 rows in set (0.000 sec)

MariaDB [seat]> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 695    |
| Created_tmp_tables      | 188907 |
+-------------------------+--------+
2 rows in set (0.001 sec)

free -m
               total        used        free      shared  buff/cache   available
Mem:            7941        2386         281          38        5617        5554
Swap:           4095           0        4095

you can easily set the innodbbufferpool_size to 2GB at least 😄

I have no idea how to do that.. I'm not a DBA..

not sure what the cleanest way is on a docker install

add something like

yaml
    mem_limit: 5g
    command:
      - --innodb-buffer-pool-size=2G

to the docker-compose.mariadb.yml

prevents the container from eating the entire server (mem_limit) and then you just pass in the command args like that to set the tunnings

@user_154092528386310154 thanks for the info. I will give a go.. question about the "mem_limit: 5g". is that a limit for what?

I mean atm the VM us 8Gb of ram.. should i mem_limit to like 4g instead?

Looks like the buffer pool size is already set..

it restricts the container from getting more than 5g of ram. Set it to what ever is reasonable for your setup, and then you have to do the backwards math to get the buffer pool size

I just guessed for you 🙂

also, setting that environment variable doesnt do anything

I mean, it sets the variable in the container, but its not consumed afaik

OH! thats annoying.. if set like that i would assume it would just work like that.. i guess not..

so i would add it like this?

and btw.. this would only limit for the mariadb container?

Yes to both

Thank for the help. Would you know to verify if the change was successful?

Shows up here https://discord.com/channels/821361165791133716/821361165791133719/1538604949754085406

less load? 😄

iowait goes to basically zero here

SHOW GLOBAL VARIABLES
    -> WHERE Variable_name IN (
    ->     'innodb_buffer_pool_size',
    ->     'innodb_flush_log_at_trx_commit',
    ->     'innodb_log_file_size',
    ->     'innodb_log_buffer_size',
    ->     'join_buffer_size',
    ->     'aria_pagecache_buffer_size'
    -> );
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| aria_pagecache_buffer_size     | 134217728  |
| innodb_buffer_pool_size        | 2147483648 |
| innodb_flush_log_at_trx_commit | 1          |
| innodb_log_buffer_size         | 16777216   |
| innodb_log_file_size           | 100663296  |
| join_buffer_size               | 262144     |
+--------------------------------+------------+


SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0         |
| Innodb_buffer_pool_read_ahead         | 21106     |
| Innodb_buffer_pool_read_ahead_evicted | 0         |
| Innodb_buffer_pool_read_requests      | 679653318 |
| Innodb_buffer_pool_reads              | 65388     |
+---------------------------------------+-----------+


SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 210   |
| Created_tmp_tables      | 40558 |
+-------------------------+-------+

I dont know if its good or bad....

Your innodb buffer pool is more efficient

https://klipy.com/gifs/funny-4536

what tweaking that on my server did

I have a bunch of other dbs that I need to work through though

seems to have helped a little bit?

🙂

noticable on the graphs

haha damn

almost zero'd out the disk reads

some of the big reports went from over ten minutes, to under a minute