Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB Mysql tuning help

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by RoldanLT, Jul 6, 2014.

Tags:
  1. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    On my dedicated server, I only have one XenForo forum and mysql tuner suggest this:
    Any words from mysql expert? :)
    Thanks!
     
  2. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    @eva2000 I hope you leave your precious advice here :angelic:
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    mysqltuner isn't always correct ;)

    leave at <2M
     
    • Like Like x 1
  4. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    Having innodb=ON and default-storage-engine = InnoDB on my.cnf is already duplicate?
    Thanks Eva.
     
  5. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    How will I know what are the maximum possible value for table_open_cache on my server?
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    Google baby https://mariadb.com/kb/en/mariadb/m...system-variables/optimizing-table_open_cache/ ;)

    this can also been checked via mysqlreport in mysqlmymonlite.sh addon https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/

    yeah duplicated
     
    • Like Like x 1
  7. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    So I got this warning on mysql log:
    Code:
    140706  3:34:25 [Warning] 'user' entry 'root@server.mydomain.com' ignored in --skip-name-resolve mode.
    140706  3:34:25 [Warning] 'proxies_priv' entry '@% root@server.mydomain.com' ignored in --skip-name-resolve mode.
    
    I know it's related to skip-name-resolve being disabled, but I'm not using my hostname as db host.
    where can I correct that warning?

    On my xenforo config, I also change localhost to '127.0.01'.
     
  8. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    Which one should I retain?
    Thanks!
     
  9. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    remove innodb=ON
    you have any monitoring service that connects to your mysql server i.e. for monitoring/charts ?
     
    • Like Like x 1
  10. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    For opened_tables, this can also been checked via mysqlreport in mysqlmymonlite.sh addon https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/. See specifically https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/#post-131

    You can see that in my forum stats page for MySQL (only visible to registered forum members ;) ) at https://community.centminmod.com/pages/mstats_mysql/ and https://community.centminmod.com/pages/mstats_mysqlreport/

    see the Tables category for Open and Opened tables and rate over time

    mysqlreport.png
     
    Last edited: Jul 6, 2014
  11. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    Ow, enabling skip-name-resolve bring down my forum, even if sing 127.0.0.1 or localhost :(
     
  12. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    did you specifically add a mysql grant for forum mysqluser@'127.0.0.1' ?
     
  13. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    No, I don't have, only nodequery but I think it doesn't connect to mysql.
     
  14. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    Yes I have:
    upload_2014-7-6_3-52-14.png
     
  15. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    well i know for my own munin and Linode Longview stats monitoring needed a specific mysql user to connect to my mysql instance
     
  16. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    I think nodequery don't have mysql monitoring.
     
  17. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    when did you add the specific user to 127.0.0.1 ? did you flush privileges for it to take effect or restart mysql server ?

    and oh you should setup specific mysql user with limited privileges and not use root ;)

    nice guide for that in mysql db and user setup section at http://centminmod.com/nginx_configure_ipboard.html
     
    • Like Like x 1
  18. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    I just created another user on phpmyadmin by:
    1. Opening my database
    2. Privileges
    3. Add User
    4. On "host" field, I just use any or %.
    Is that fine?
    skip-name-resolve works fine now I think.
    No errors anymore.
     
  19. eva2000

    eva2000 Administrator Staff Member

    30,630
    6,862
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,307
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    host field = 127.0.0.1
     
    • Like Like x 1
  20. RoldanLT

    RoldanLT Well-Known Member

    3,940
    962
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,315
    Local Time:
    4:17 AM
    1.11
    10.2
    Regarding this warning, On my phpmyadmin , I just notice this: upload_2014-7-6_4-35-23.png
    root user has privileges using my hostname, should I delete this?