MariaDB MariaDB default optimization bug? - 134.8M per thread

pamamolf · Jan 19, 2020

Please fill in any relevant information that applies to you:

CentOS Version: CentOS 7 64bit
Centmin Mod Version Installed: 123.09beta01
Nginx Version Installed: 1.17.x
PHP Version Installed: 7.2.x
MariaDB MySQL Version Installed: 10.2.xx
When was last time updated Centmin Mod code base ? : just now..

Hello

I just did an installation on a dedicated server:

Intel(R) Xeon(R) CPU E3-1245 V2
32GB ram

Code:

yum -y update; curl -O https://centminmod.com/betainstaller72.sh && chmod 0700 betainstaller72.sh && bash betainstaller72.sh

at the custom config i had:

Code:

MARIADB_INSTALLTENTWO='y'

and when i check using mysqltunner i got this:

[--] Total buffers: 4.6G global + 134.8M per thread (500 max threads)

it was as default 1.000 and i lower it to 500 ....

but i don't see anything wrong and i didn't adjust anything related....

I thought that maybe it was a bug in the installer so i tested using:

Code:

yum -y update; curl -O https://centminmod.com/betainstaller73.sh && chmod 0700 betainstaller73.sh && bash betainstaller73.sh

without the:

Code:

MARIADB_INSTALLTENTWO='y'

and i got the same

mycnf :

Code:

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
skip-name-resolve
#old_passwords
back_log = 1024
max_connections = 500
key_buffer_size = 256M
myisam_sort_buffer_size = 768M
myisam_max_sort_file_size = 8192M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_definition_cache = 10240
table_open_cache = 10240
thread_cache_size = 384
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
query_cache_limit = 1536K
query_cache_size = 256M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 4
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 10000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 2048M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=2

innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 20
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 900
innodb_io_capacity_max = 1500
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 1024M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 1024M
aria_sort_buffer_size = 256M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 1024M
sort_buffer = 256M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.2]
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

# page compression
# 10.1+ https://mariadb.com/kb/en/mariadb/compression/
#innodb_compression_algorithm=zlib
#innodb_use_fallocate=0
#innodb_use_mtflush=0
#innodb_mtflush_threads=4

Maybe it's a bug at Mysqltunner?

i used:

Code:

wget http://mysqltuner.pl/ -O mysqltuner.pl

Thank you !!!!

eva2000 · Jan 19, 2020

interesting mysqltuner probably changed the formula for max memory

mysqltuner 1.7.14 on one of my servers shows

Code (Text):

[OK] Maximum reached memory usage: 10.8G (34.64% of installed RAM)
[OK] Maximum possible memory usage: 17.4G (55.83% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

mysqltuner 1.7.19 on same server shows

Code (Text):

[OK] Maximum reached memory usage: 10.9G (35.04% of installed RAM)
[!!] Maximum possible memory usage: 142.4G (456.02% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

eva2000 · Jan 19, 2020

Looks like it's due to this change in mysqltuner.pl Include max_allowed_packet in thread memory size by jfcoz · Pull Request #464 · major/MySQLTuner-perl inclusion of max_allowed_packet size in memory calculation

so my.cnf templates I use may need revising but my modified mysqltuner.pl explains max memory usage warning a bit better - basically max memory usage is based on the assumption you reach max_connections - mysql concurrent connections = Max_used_connections status variable reported. But that is just a theory unless you real world usage reaches that threshold. You may run your server for a whole year with 500 max_connections variable set but only ever hit 150 Max_used_connections = mysql concurrent connections so your theoretical max memory usage would be 150/500 of the max reported by mysqltuner.

Also per thread memory buffers now includes connection and result buffer size max theoretical memory size. Connection and result buffers start out at net_buffer_length set size and can grow to max_allowed_packet size but that depends on queries/web app's mysql usage so you could in real world never reach max_allowed_packet size. So like max_connections you will never hit that max memory usage warning level as you never hit mysql concurrent connection limit and/or never reach connection/result buffers that hit max_allowed_packet size.

So mysqltuner.pl reported max reached memory usage would be more accurate of your memory usage than max possible memory usage based on mysqltuner.pl's assumptions if mysqltuner.pl usage reflects a long enough duration of mysql run time/uptime.

That is why mysqltuner can only be used as a rough guide and real long term monitoring and analysis is needed for your specific web app(s)'s usage requirements.

You can adjust your /etc/my.cnf below and see - but that is a rough guide that doesn't take into account your actual real world usage and web app requirements

Code (Text):

sed -i 's|max_allowed_packet = .*|max_allowed_packet = 16M|g' /etc/my.cnf
sed -i 's|max_connections = .*|max_connections = 500|g' /etc/my.cnf
mysqlrestart

You may run into max_allowed_packet mysql errors depending on your web app, so again real world vs theory and required monitoring and analysis of your specific work loads. It's more likely you'd hit max_allowed_packet errors than running up to your theoretical max mysql concurrent connection and/or max connection/result buffer possible buffer max values.

edit: example of where real world vs mysqltuner.pl theoretical max memory usage

On a server with 32GB memory I pushed Centmin Mod MariaDB 10.3 server to 1000 concurrent mysql connections and then read the mysqltuner.pl Performance Metrics section

Code (Text):

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1m 14s (2M q [28K qps], 2K conn, TX: 385M, RX: 270M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 86.7G
[--] Other process memory: 0B
[--] Total buffers: 10.8G global + 51.8M per thread (1500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 61.4G (196.52% of installed RAM)
[!!] Maximum possible memory usage: 86.7G (277.47% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 66% (1000/1500)
[OK] Aborted connections: 0.00%  (0/2026)

It says max reached memory = 61.4GB with max possible memory usage at 86.7GB which is 196% and 277% of installed memory.

I ran sar memory stats at 1 second interval recording memory usage during the MySQL load to record system memory usage. It shows at idle had kbmemfree of ~2,300,116 KB free memory with only 51.85% committed memory taking into account memory + swap disk. Load started around 2:40:03 AM server time and dropped kbmemfree from ~2,300,116 KB to ~1,084,208 KB at 2:40:08 AM and committed memory jumped from 51.85% to 54.63%.

Code (Text):

02:39:44 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
02:39:45 AM   2300116  30452288     92.98         4  22828268  19153132     51.85  15161376  10320588       188
02:39:46 AM   2300116  30452288     92.98         4  22828268  19153132     51.85  15161412  10320588       188
02:39:47 AM   2300116  30452288     92.98         4  22828268  19153132     51.85  15161412  10320588       188
02:39:48 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:49 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:50 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:51 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:52 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:53 AM   2298232  30454172     92.98         4  22828268  19153132     51.85  15161376  10320588       192
02:39:54 AM   2101976  30650428     93.58         4  22828276  19379884     52.46  15355968  10320592      3692
02:39:55 AM   2006248  30746156     93.87         4  22828304  19390204     52.49  15453384  10320256     57756
02:39:56 AM   1894372  30858032     94.22         4  22828304  19403128     52.52  15564216  10319812      2344
02:39:57 AM   1822804  30929600     94.43         4  22828304  19398820     52.51  15639892  10319476     51552
02:39:58 AM   1720748  31031656     94.75         4  22828316  19422052     52.57  15742764  10318960        12
02:39:59 AM   1742288  31010116     94.68         4  22828308  19181744     51.92  15719476  10318748       224
02:40:00 AM   1743168  31009236     94.68         4  22828304  19181744     51.92  15719192  10318740       224
02:40:01 AM   1742852  31009552     94.68         4  22828304  19181744     51.92  15719192  10318740       224
02:40:02 AM   1314696  31437708     95.99         4  22828300  20034988     54.23  16123044  10318732       224
02:40:03 AM   1159532  31592872     96.46         4  22828116  20116204     54.45  16274296  10318536       308
02:40:04 AM   1127684  31624720     96.56         4  22828116  20134764     54.50  16305624  10318536       308
02:40:05 AM   1111436  31640968     96.61         4  22828116  20153580     54.55  16320388  10318536       308
02:40:06 AM   1100516  31651888     96.64         4  22828116  20153580     54.55  16331812  10318536       308
02:40:07 AM   1100472  31651932     96.64         4  22828116  20153580     54.55  16332152  10318536       308
02:40:08 AM   1084208  31668196     96.69         4  22828120  20182540     54.63  16341472  10318532       332
02:40:09 AM   1092568  31659836     96.66         4  22828120  20153708     54.55  16332284  10318532       336
02:40:10 AM   1092560  31659844     96.66         4  22828120  20153708     54.55  16332284  10318532       336
02:40:11 AM   1092600  31659804     96.66         4  22828120  20153708     54.55  16332288  10318532       336
02:40:12 AM   1089536  31662868     96.67         4  22828120  20153708     54.55  16335504  10318532       336
02:40:13 AM   1089512  31662892     96.67         4  22828184  20153708     54.55  16335644  10318544       400
02:40:14 AM   1089520  31662884     96.67         4  22828184  20153708     54.55  16335644  10318544       400
02:40:15 AM   1089536  31662868     96.67         4  22828188  20153708     54.55  16335648  10318548       228
02:40:16 AM   1089536  31662868     96.67         4  22828188  20153708     54.55  16335648  10318600       228
02:40:17 AM   1089520  31662884     96.67         4  22828188  20153708     54.55  16335648  10318600       228
02:40:18 AM   1089440  31662964     96.67         4  22828188  20153708     54.55  16335648  10318600       228
02:40:19 AM   1089480  31662924     96.67         4  22828188  20153708     54.55  16335656  10318596       228
02:40:20 AM   1089488  31662916     96.67         4  22828188  20153708     54.55  16335668  10318596       220
02:40:21 AM   1089448  31662956     96.67         4  22828188  20153708     54.55  16335668  10318596       220
02:40:22 AM   1089456  31662948     96.67         4  22828192  20153708     54.55  16335668  10318600       224
02:40:23 AM   1089464  31662940     96.67         4  22828252  20153708     54.55  16335668  10318656       284
02:40:24 AM   1089424  31662980     96.67         4  22828252  20153708     54.55  16335672  10318656       284
02:40:25 AM   1089432  31662972     96.67         4  22828252  20153708     54.55  16335672  10318660       276
02:40:26 AM   1089432  31662972     96.67         4  22828252  20153708     54.55  16335672  10318660       276
02:40:27 AM   1089408  31662996     96.67         4  22828252  20153708     54.55  16335672  10318660       276
02:40:28 AM   1089412  31662992     96.67         4  22828252  20153708     54.55  16335648  10318660       280
02:40:29 AM   1089420  31662984     96.67         4  22828252  20153708     54.55  16335672  10318660       280
02:40:30 AM   1089428  31662976     96.67         4  22828252  20153708     54.55  16335676  10318660       240
02:40:31 AM   1089412  31662992     96.67         4  22828256  20153708     54.55  16335676  10318664       244
02:40:32 AM   1089412  31662992     96.67         4  22828260  20153708     54.55  16335676  10318664       248
02:40:33 AM   1525156  31227248     95.34         4  22828328  19182312     51.92  15925076  10318732        56

free memory command showed barely any swap usage despite mysqltuner.pl reporting max reached memory usage being 61.4GB on this 32GB (31.2GB) memory installed server.

Code (Text):

free -mlt
              total        used        free      shared  buff/cache   available
Mem:          31984        7091        1492         760       23401       23748
Low:          31984       30492        1492
High:             0           0           0
Swap:          4091           2        4089
Total:        36076        7093        5582

pushing it further at 1,495 mysql concurrent connections drops free memory down but still not to swap but to around 253,264 KB kbfreemem

Code (Text):

02:59:15 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
02:59:16 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957848  10315240        16
02:59:17 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957848  10315240        16
02:59:18 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:19 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:20 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:21 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:22 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:23 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957852  10315240        16
02:59:24 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957900  10315240        16
02:59:25 AM   1499928  31252476     95.42         4  22829768  19213908     52.01  15957900  10315240        16
02:59:26 AM   1497700  31254704     95.43         4  22829768  19217392     52.02  15960236  10315240        16
02:59:27 AM   1290492  31461912     96.06         4  22829768  19413500     52.55  16169196  10315228     31748
02:59:28 AM   1093280  31659124     96.66         4  22829628  19426164     52.58  16366580  10315244     23324
02:59:29 AM    927356  31825048     97.17         4  22829632  19469476     52.70  16529940  10315240     20904
02:59:30 AM    773072  31979332     97.64         4  22829632  19490568     52.76  16683480  10315240       456
02:59:31 AM    628804  32123600     98.08         4  22829636  19512108     52.82  16826388  10315244       452
02:59:32 AM    684876  32067528     97.91         4  22829644  19230740     52.06  16770552  10315256        92
02:59:33 AM    685404  32067000     97.91         4  22829632  19230740     52.06  16768524  10315240        64
02:59:34 AM    685264  32067140     97.91         4  22829632  19230740     52.06  16768524  10315240        64
02:59:35 AM    217132  32535272     99.34         0  22579088  20615104     55.80  17120612  10401376        64
02:59:36 AM    219932  32532472     99.33         0  22526324  20624368     55.83  17100832  10419228       132
02:59:37 AM    228616  32523788     99.30         0  22491156  20682224     55.98  17081560  10428920       132
02:59:38 AM    217396  32535008     99.34         0  22491156  20691440     56.01  17092420  10428920       132
02:59:39 AM    240848  32511556     99.26         0  22456396  20710512     56.06  17060088  10438300       132
02:59:40 AM    226340  32526064     99.31         0  22456396  20710512     56.06  17074812  10438300       132
02:59:41 AM    225696  32526708     99.31         0  22456396  20710512     56.06  17075536  10438300       120
02:59:42 AM    225680  32526724     99.31         0  22456396  20710512     56.06  17075572  10438300       120
02:59:43 AM    225704  32526700     99.31         0  22456400  20710512     56.06  17075584  10438300       124
02:59:44 AM    225768  32526636     99.31         0  22456400  20710512     56.06  17075588  10438300       124
02:59:45 AM    222232  32530172     99.32         0  22456464  20710512     56.06  17078964  10438344       188
02:59:46 AM    222256  32530148     99.32         0  22456464  20710512     56.06  17079004  10438344       188
02:59:47 AM    222088  32530316     99.32         0  22456464  20710512     56.06  17079008  10438344       180
02:59:48 AM    253744  32498660     99.23         0  22421424  20710576     56.06  17035076  10447400       184
02:59:49 AM    253608  32498796     99.23         0  22421660  20710576     56.06  17034992  10447548       188
02:59:50 AM    253896  32498508     99.22         0  22421660  20710576     56.06  17035028  10447548       188
02:59:51 AM    253712  32498692     99.23         0  22421660  20710576     56.06  17035080  10447548       188
02:59:52 AM    253740  32498664     99.23         0  22421660  20710576     56.06  17035104  10447548       188
02:59:53 AM    253780  32498624     99.23         0  22421660  20710576     56.06  17035104  10447548       188
02:59:54 AM    253804  32498600     99.23         0  22421660  20710576     56.06  17035104  10447548       188
02:59:55 AM    253772  32498632     99.23         0  22421664  20710576     56.06  17035104  10447552       192
02:59:56 AM    253748  32498656     99.23         0  22421724  20710576     56.06  17035124  10447612       252
02:59:57 AM    253772  32498632     99.23         0  22421724  20710576     56.06  17035124  10447612       252
02:59:58 AM    253660  32498744     99.23         0  22421724  20710576     56.06  17035124  10447612       252
02:59:59 AM    253632  32498772     99.23         0  22421724  20710576     56.06  17035124  10447612       252
03:00:00 AM    253672  32498732     99.23         0  22421724  20710576     56.06  17035124  10447612       252
03:00:01 AM    253624  32498780     99.23         0  22421724  20710576     56.06  17035124  10447612       252
03:00:02 AM    253452  32498952     99.23         0  22421728  20710740     56.06  17035276  10447612       240
03:00:03 AM    253320  32499084     99.23         0  22421736  20710740     56.06  17035132  10447616       256
03:00:04 AM    253336  32499068     99.23         0  22421736  20710740     56.06  17035132  10447620       256
03:00:05 AM    253264  32499140     99.23         0  22421740  20710740     56.06  17035136  10447620       260
03:00:06 AM    923984  31828420     97.18         0  22421804  19234228     52.07  16403012  10447684       324
03:00:07 AM   1014248  31738156     96.90         0  22421808  19231908     52.06  16313740  10447688        32
03:00:08 AM   1006016  31746388     96.93         0  22421792  19284544     52.20  16323744  10447688        48

Code (Text):

free -mlt       
              total        used        free      shared  buff/cache   available
Mem:          31984        7998         993         760       22992       22840
Low:          31984       30991         993
High:             0           0           0
Swap:          4091           2        4089
Total:        36076        8000        5083

while mysqltuner.pl now says max reached memory is 114.2GB or 365% of installed memory and max possible memory usage is 114.5GB or 366% of installed memory

Code (Text):

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5m 32s (4M q [12K qps], 5K conn, TX: 768M, RX: 541M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 114.5G
[--] Other process memory: 0B
[--] Total buffers: 10.8G global + 70.8M per thread (1500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 114.2G (365.47% of installed RAM)
[!!] Maximum possible memory usage: 114.5G (366.58% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/4M)
[!!] Highest connection usage: 99%  (1495/1500)

That is while real MySQL usage and monitoring analysis is important and why you shouldn't rely on advice provided by mysqltuner.pl without understanding what they advice and it's calculations are based on.

pamamolf · Jan 19, 2020

Thanks for the explanation

Should i adjust manually also the:
Code:
net_buffer_length = 16384
back_log = 1024
?

Also i think as max_allowed_packet size is not loaded upfront and get dynamically what mysql wants it may be good to not lower that value at your optimization routine as i think it was already set well and never got any issues with it.

Of course you lower it a bit and may be again ok but in my opinion the 32MB may be the minimum...

But you know better than me

eva2000 · Jan 19, 2020

net_buffer_length is already the default value anyway and back_log should be fine but only your real world monitoring/analysis of your web app's usage will tell the true story

pamamolf · Jan 19, 2020

ok thanks

pamamolf · Jan 19, 2020

It's more likely you'd hit max_allowed_packet errors than running up to your theoretical max mysql concurrent connection and/or max connection/result buffer possible buffer max values.
Click to expand...

What about this?

Also i think as max_allowed_packet size is not loaded upfront and get dynamically what mysql wants it may be good to not lower that value at your optimization routine as i think it was already set well and never got any issues with it.
Click to expand...

eva2000 · Jan 19, 2020

pamamolf said: ↑

What about this?
Click to expand...

again it's web app's mysql usage specific as to what is optimal really

Log in / Register

Forums

Join the community today

MariaDB MariaDB default optimization bug? - 134.8M per thread

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Join the community today

MariaDB MariaDB default optimization bug? - 134.8M per thread

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

.