/ Register

Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB Mysql tuning help

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

Tags:
Previous Thread Next Thread
Loading...
Page 1 of 2
  1. Jul 6, 2014 #1
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    On my dedicated server, I only have one XenForo forum and mysql tuner suggest this:
    Any words from mysql expert? :)
    Thanks!
     
  2. Jul 6, 2014 #2
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    @eva2000 I hope you leave your precious advice here :angelic:
     
  3. Jul 6, 2014 #3
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    mysqltuner isn't always correct ;)

    leave at <2M
     
  4. Jul 6, 2014 #4
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    Having innodb=ON and default-storage-engine = InnoDB on my.cnf is already duplicate?
    Thanks Eva.
     
  5. Jul 6, 2014 #5
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    How will I know what are the maximum possible value for table_open_cache on my server?
     
  6. Jul 6, 2014 #6
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  7. Jul 6, 2014 #7
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    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. Jul 6, 2014 #8
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    Which one should I retain?
    Thanks!
     
  9. Jul 6, 2014 #9
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    remove innodb=ON
    you have any monitoring service that connects to your mysql server i.e. for monitoring/charts ?
     
  10. Jul 6, 2014 #10
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Jul 6, 2014 #11
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    Ow, enabling skip-name-resolve bring down my forum, even if sing 127.0.0.1 or localhost :(
     
  12. Jul 6, 2014 #12
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    did you specifically add a mysql grant for forum mysqluser@'127.0.0.1' ?
     
  13. Jul 6, 2014 #13
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    No, I don't have, only nodequery but I think it doesn't connect to mysql.
     
  14. Jul 6, 2014 #14
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    Yes I have:
    upload_2014-7-6_3-52-14.png
     
  15. Jul 6, 2014 #15
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    well i know for my own munin and Linode Longview stats monitoring needed a specific mysql user to connect to my mysql instance
     
  16. Jul 6, 2014 #16
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    10.2
    I think nodequery don't have mysql monitoring.
     
  17. Jul 6, 2014 #17
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  18. Jul 6, 2014 #18
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    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. Jul 6, 2014 #19
    eva2000

    eva2000 Administrator Staff Member

    53,487
    12,130
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,671
    Local Time:
    2:47 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    host field = 127.0.0.1
     
  20. Jul 6, 2014 #20
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    12:47 AM
    Mainline
    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?
     
Previous Thread Next Thread
Loading...
Page 1 of 2

.