Want more timely Centmin Mod News Updates?
Become a Member

MariaDB May Increasing the query_cache size over 128M reduce performance?

Discussion in 'MariaDB & General MySQL news & discussions' started by negative, Feb 3, 2016.

  1. negative

    negative Member

    212
    20
    18
    Apr 11, 2015
    Ratings:
    +48
    Local Time:
    7:05 AM
    1.9.10
    10.1.11
    Hello
    I'm optimizing the my mysql (mariadb 10.1.11) server and mysqltuner.pl offering non-stop the increase query_cache_size variable.

    For now, it is 512M but it still offer. I did 768M and same, so should i increase to about 1G ?

    I' m using mysql service separately so my database server is different from Web. It works just for database.

    My server has 48GB DDR3 1333 MHZ ECC Ram and 2 x Intel Xeon X5650 Cpu (total 24 core - 12 mb cache). Also, 4X300 GB SAS HDD @ Raid 10

    So, i can increase all of my limits for run mysql optimized and server must be use all sources for mysql. But, mysqltuner says "Increasing the query_cache size over 128M may reduce performance" What mean this ?

    Thanks.
     
  2. eva2000

    eva2000 Administrator Staff Member

    28,988
    6,579
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,767
    Local Time:
    2:05 PM
    Nginx 1.13.x
    MariaDB 5.5
    mysqltuner.pl is only a general guide and some suggestions are outdated or have proviso notes for certain situations. query_cache_size and memory usage are 2 such examples of such. Best to read up on query_cache and InnoDB table notes ;)
     
  3. negative

    negative Member

    212
    20
    18
    Apr 11, 2015
    Ratings:
    +48
    Local Time:
    7:05 AM
    1.9.10
    10.1.11
    Do you advice the query_cache parameter up top 1GB so ? It is no problem anymore ?
    If i have max connections 500 and query cache is 1GB so max usable memory 50GB and it is my total RAM. I think it calculating like this :)

    P.S: All tables are innodb

    innodb_buffer_pool_size = 16G
    innodb_buffer_pool_instances = 16
    key_buffer_size = 256M
    query_cache_size = 1G

    I'm not sure from that on mysql optimization, caching is always good ? Caches may slow down the database server too if we look some articles.
     
  4. eva2000

    eva2000 Administrator Staff Member

    28,988
    6,579
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,767
    Local Time:
    2:05 PM
    Nginx 1.13.x
    MariaDB 5.5
    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    MySQL optimisation is a continuous process of monitoring and fine tuning for optimal settings for your web app usage requirements. It's why my paid server optimisations span a minimum of at least 4 days :)
     
  5. negative

    negative Member

    212
    20
    18
    Apr 11, 2015
    Ratings:
    +48
    Local Time:
    7:05 AM
    1.9.10
    10.1.11
    Thank you, i agree that. It is no problem.

    I' m still waiting just an answer from you.
    Caching is always good in mysql side ? Or sometimes, queries should call in real time is better ?
     
  6. eva2000

    eva2000 Administrator Staff Member

    28,988
    6,579
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,767
    Local Time:
    2:05 PM
    Nginx 1.13.x
    MariaDB 5.5
    caching only good if it improves mysql performance so you need to monitor, measure and benchmark changes to see if they improve or regress performance
     
    • Like Like x 1