Discover Centmin Mod today
Register Now

MariaDB MariaDB default optimization bug? - 134.8M per thread

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Jan 19, 2020.

  1. pamamolf

    pamamolf Premium Member Premium Member

    3,696
    357
    83
    May 31, 2014
    Ratings:
    +687
    Local Time:
    1:00 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    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 !!!!
     
    Last edited: Jan 19, 2020
  2. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:00 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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
    
     
  3. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:00 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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 git 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)
    
     
    Last edited: Jan 19, 2020
  4. pamamolf

    pamamolf Premium Member Premium Member

    3,696
    357
    83
    May 31, 2014
    Ratings:
    +687
    Local Time:
    1:00 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    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 :)
     
    Last edited: Jan 19, 2020
  5. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:00 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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
     
  6. pamamolf

    pamamolf Premium Member Premium Member

    3,696
    357
    83
    May 31, 2014
    Ratings:
    +687
    Local Time:
    1:00 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    ok thanks :)
     
  7. pamamolf

    pamamolf Premium Member Premium Member

    3,696
    357
    83
    May 31, 2014
    Ratings:
    +687
    Local Time:
    1:00 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    What about this?
     
    Last edited: Jan 19, 2020
  8. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:00 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    again it's web app's mysql usage specific as to what is optimal really :)