Discover Centmin Mod today
Register Now

MariaDB Mysqltuner

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Revenge, Mar 22, 2016.

  1. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    Finally, mysqltuner 1.6.7 recommends disabling the Query Cache. It's better later than never.
    Code:
    [OK] Query cache is disabled by default due to mutex contention.

     
    • Like Like x 1
  2. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Why is better to have a cache disabled?
     
  3. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    • Informative Informative x 3
  4. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Do you have it in a real world scenario on a huge forum with many concurrent users and it works better disabled than have it enabled?
     
    Last edited: Mar 22, 2016
  5. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    Yes, in a board with many concurrent requests that i maintain, the performance is better and load is lower.
    You will notice this more in a forum like this one, where the query results are always changing, so the cache needs also to be changed ;)
     
    • Informative Informative x 1
  6. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok i will disable it and try to see if it is better :)
     
    Last edited: Mar 22, 2016
  7. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    It seems that is working nice :)
     
    Last edited: Mar 22, 2016
  8. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    Just another thing for us to understand better. This post that im making, will go to the Buffer Pool. This post also change the result set of a lot of queries in the Query_Cache. For example, the querie responsible for the New Posts button.
    The Query Cache don't have a cache of this new post, so they will ask the buffer pool and then it will need to update every query that this new post will mess with.
    Now imagine a forum with a lot of new posts every minute, and the Query Cache always competing with the buffer pool because of that ;)

    From my knowledge, the Query Cache is good in sites where almost there is no updates or inserts to the Database. For example a Wordpress Blog with comments disabled.
     
    • Informative Informative x 2
  9. negative

    negative Active Member

    260
    26
    28
    Apr 11, 2015
    Ratings:
    +59
    Local Time:
    10:41 PM
    1.9.10
    10.1.11
    I've disabled the query_cache (it was 512M) and i ll see what will happen.
     
  10. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    In version 1.6.11 of Mysqltuner, its again recommending to turn on Query Cache if MariaDB version is superior than 10.1.2.

    You can check it here: possible wrong suggestion in newer versions: [!!] Query cache should be disabled by default due to mutex contention. · Issue #190 · major/MySQLTuner-perl · GitHub

    I checked some benchmarks, and it really seems MariaDB have done very big improvements regarding the Query Cache in 10.1.

    [​IMG]
    [​IMG]
    While in Mysql 5.7 the performance is degraded when we enable Query Cache, that does not happen with MariaDB, in fact its the opposite.

    MariaDB 10.1 vs MySQL 5.7: Real-World Performances - Softizy Blog
     
    • Informative Informative x 3
  11. negative

    negative Active Member

    260
    26
    28
    Apr 11, 2015
    Ratings:
    +59
    Local Time:
    10:41 PM
    1.9.10
    10.1.11
    I was re-enabled it after 2 days of disabled. Query cache is increasing the performans as i see the real time loading of website. Now, tests are correcting that.
     
    • Informative Informative x 1
  12. eva2000

    eva2000 Administrator Staff Member

    37,305
    8,152
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,548
    Local Time:
    6:41 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    thanks @negative interesting benchmarks !
     
  13. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    @Revenge

    How did you do the test and how do you get the nice graphs?
     
  14. eva2000

    eva2000 Administrator Staff Member

    37,305
    8,152
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,548
    Local Time:
    6:41 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    • Like Like x 1
  15. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    I was wondering how they get that nice graphs at the end :)
     
  16. eva2000

    eva2000 Administrator Staff Member

    37,305
    8,152
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,548
    Local Time:
    6:41 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    excel or google sheet charts works nice :)
     
  17. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes but don't know how to :p
     
  18. eva2000

    eva2000 Administrator Staff Member

    37,305
    8,152
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,548
    Local Time:
    6:41 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    google for tutorials ;)
     
  19. pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    10:41 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    @eva2000 @Revenge @negative @Matt @RoldanLT @Sunka @Xon

    After almost 2 years of this topic i am wondering for MariaDB 10.1.30 if you still use query cache or not?

    Mysqltunner recommendation still:
    From my experience (as a noob :) ) i think it is a bit faster if it is enabled....

    But i notice that the size of that cache is never enough :(

    Having a database using Innodb around 35GB and even if i set at the insane number of 5GB that query cache i can see as used all of it after x days :( Increasing it up to more insane values like 10GB and so on doesn't make sense i think.....

    But i notice also that when that cache is full the performance is going down....

    So what you do for MariaDB 10.1 (default centminmod one) ?

    What about MariaDB 10.2 and the query cache?

    Thank you
     
  20. Revenge

    Revenge Active Member

    430
    91
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +323
    Local Time:
    8:41 PM
    1.9.x
    10.1.x
    I keep it disabled.
    Having a Query Cache of 5Gb will not be good for performance, as if some change happens, it will need to update many queries inside that big 5Gb of cache.

    Normally, 128Mb is the maximum we should set the Query Cache. But the best thing is probably just to disable it.

    Mysqltuner now says in multiprocessor:
    PS: Query Cache will be removed from MySQL 8.0.

     
    Last edited: Jan 15, 2018
    • Informative Informative x 2
..