0

I'm using debian 8 Jessie and MariaDB. My mysqltuner seems to indicate as MariaDB can use 142% of RAM :

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM 
[--] Data in InnoDB tables: 2G (Tables: 79)
[--] Data in MyISAM tables: 96M (Tables: 146)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 34

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 16h 44m 38s (73M q [502.853 qps], 196K conn, TX: 572B, RX: 14B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 17.3G global + 56.2M per thread (500 max threads)
[!!] Maximum possible memory usage: 44.8G (142% of installed RAM)
[OK] Slow queries: 0% (2K/73M)
[OK] Highest usage of available connections: 28% (141/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/32.6M
[OK] Key buffer hit rate: 100.0% (132M cached / 53K reads)
[OK] Query cache efficiency: 44.9% (50M cached / 113M selects)
[!!] Query cache prunes per day: 260596
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 21% (6K on disk / 28K total)
[OK] Thread cache hit rate: 99% (141 created / 196K connections)
[OK] Table cache hit rate: 72% (500 open / 692 opened)
[OK] Open file limit used: 17% (429/2K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)
[OK] InnoDB buffer pool / data size: 16.0G/2.4G
[!!] InnoDB log waits: 30

My innodb_buffer_pool_size is at 16Go, for 32Go RAM that should be ok, I don't know what to do for optimize this.

The thing is, my memory general usage in the server is always under 89% (plus for caching). MariaDB is actually using 50,6% of RAM.

This the principal variables in my.cnf I adjusted which could have an effect on this:

max_connections = 100
max_heap_table_size = 64M
read_buffer_size = 4M
read_rnd_buffer_size = 32M
sort_buffer_size = 8M
query_cache_size = 256M
query_cache_limit = 4M
query_cache_type = 1
query_cache_strip_comments =1
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
nnodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 16G
thread_cache_size   = 4M
max_connections     = 500
join_buffer_size    = 12M
interactive_timeout = 30
wait_timeout        = 30
open_files_limit    = 800
innodb_file_per_table
key_buffer_size     = 1G
table_open_cache    = 500
innodb_log_file_size    = 256M

Is something incorrect in my configuration ?

Improve this question
5
  • The only problem I see is that you are placing confidence in the output of mysqltuner. You're wasting your time and probably doing more harm than good with excessive tweaking. Read the pull quote: dba.stackexchange.com/a/45570/11651.
    – Michael - sqlbot
    Sep 24, 2017 at 2:34
  • Hm no I don't think i'm wasting my time, my mysql is not optimize as it should and this is reflected in the performance of our service which welcomes several thousand visitors every day. Wilson Hauck and SEO DEVS help my fine and it's better now, and I understand why. So I will keep in touch in the next few days about any adjustments I will probably make then.
    – poka
    Sep 24, 2017 at 2:42
  • @poka Would it be convenient to repost a current MySQLTuner report? Hope you are doing well.
    – Wilson Hauck
    Nov 10, 2017 at 15:42
  • @WilsonHauck of course I will post my MySQLTuner again inthe next few days, because i'm actualy migrate my server so can't do that now.
    – poka
    Nov 13, 2017 at 15:09
  • @poka Hoping your migration went well and your system is running well. Stay in touch, please.
    – Wilson Hauck
    Mar 15, 2018 at 1:00

2 Answers 2

Reset to default
1

Additional reduction in memory footprint can be achieved by REMOVING from your my.cnf the following to allow defaults to serve your system.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size

Consider changing or adding the following in my.cnf

key_buffer_size = 164M   # you already have 132m cached per MySqlTuner
thread_cache_size = 100  # CAP suggested in 8.0 to avoid overload

There were 141 threads created to serve 196K connections in your 1 day 16 hrs uptime. This will reduce high cpu utilization for creating/breaking down connections.

Keeping your innodb_buffer_pool_size at 16G for now is fine since you only have 2.4G of data per MySQLTuner.

The other items suggested by SEO DEVS are super.

Use mysqlcalculator.com for a quick check on approximate RAM utilization

Improve this answer
8
  • Great thank you! Maximum possible memory usage: 17.2G (54% of installed RAM). But now phpmyadmin advise to increase sort_buffer_size (which is removed form my.cnf now) and mysqltuner alert that there are not tempory tables enought: [!!] Sorts requiring temporary tables: 22% (1K temp sorts / 5K sorts) But I will wait one or two days before interpret realy theses informations.
    – poka
    Sep 24, 2017 at 1:05
  • @poka Wait is advised. Sorts requiring temporary tables means 22% had to use disk, not a problem. If you put back your 8M sort_buffer_size, you will add 250 (connections) x 6M = 1.5G to your RAM used footprint. Keep as is, until a problem exists. Some suggestions are not good, whether from MySQLTuner or phpmyadmin ( or others ).
    – Wilson Hauck
    Sep 24, 2017 at 12:47
  • @poka Have your last 2 weeks been more successful? Please replace the MySQLTuner with a current complete report so we can see your progress.
    – Wilson Hauck
    Oct 5, 2017 at 23:57
  • @poka Please also repost your complete my.cnf with date, when you have time.
    – Wilson Hauck
    Oct 6, 2017 at 0:06
  • Of course, this is the result of MySQLTuner and the tuned of my my.cnf just now: gist.github.com/anonymous/73f23650b401f4defeb36f371c9c05bb So it's not perfect but it works pretty good. I don't understand why there is 261 logs waiting...
    – poka
    Oct 7, 2017 at 13:05
1

You have over done your setup and the sql database is set for a possible overload, also you have a duplicate max-connection. For a 32gb server the my.cnf should look more like this. change your settings to match the ones below.

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections        = 250
table_cache            = 4K
wait_timeout            = 1200

query_cache_limit       = 1M
query_cache_size        = 128M
join_buffer_size        = 2M
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = 26G
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

Hope this helps

Improve this answer
3
  • Ok so I edited the value as you suggest, but kept the other values you didn't mention, and the possible memory usage went down to 134%, so better but too big. So instead of innodb_buffer_pool_size = 26G, I keep 16G and memory usage went down so to 99%. So it's better, but not ok, I think that should be less.
    – poka
    Sep 23, 2017 at 23:27
  • innodb_buffer_pool_size should be 80% of your available ram. the best way to get past a possible over usage is to lower max connections. Your system ram is used in this way. global buffers + (thread buffers X max_connections) = here is a list of global buffers and thread buffers. global buffers are: key_buffer, innodb_buffer_pool, innodb_log_buffer, innodb_additional_mem_pool, net_buffer Thread buffers are: sort_buffer, myisam_sort_buffer,read_buffer, join_buffer, read_rnd_buffer. hope this better helps you understand the ram usage. its a sum that need to be worked out before hand.
    – SEO DEVS
    Sep 24, 2017 at 8:40
  • @seo devs mariadb refman indicates " can be set up to 80% of the total memory " - considering 2G is in service at this time, a 16G innodb_buffer_pool_size will be adequate for some time until more data is stored in innodb.
    – Wilson Hauck
    Sep 25, 2017 at 13:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .