Learn about Centmin Mod LEMP Stack today
Register Now

Issue: The rate of opening tables is high.

Discussion in 'System Administration' started by rdan, Jun 11, 2014.

  1. rdan

    rdan Premium Member Premium Member

    4,308
    1,044
    113
    May 25, 2014
    Ratings:
    +1,504
    Local Time:
    9:45 AM
    Mainline
    10.2
    Recommendation:
    Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.

    Justification:
    Opened table rate: 23.29 per hour, this value should be less than 10 per hour

    Used variable / formula:
    Opened_tables / Uptime

    Test:
    value*60*60 > 10


    How can I determine all my opened tables?
    For me to be able to adjust this correctly, Thanks !
     
  2. eva2000

    eva2000 Administrator Staff Member

    36,396
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,304
    Local Time:
    11:45 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    where you getting the recommendation from ? phpmyadmin ?

    they're only guidelines so don't follow to the letter

    my mysqlmymonlite.sh script can grab alot of stats for such https://community.centminmod.com/threads/mysqlmymonlite-sh-addon.65/

    i.e. for this forum's MariaDB MySQL mysqlreport from mysqlmymonlite.sh

    Code:
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.2 mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Jun 10 18:15:01 UTC 2014
    
    ----------------------------
    mysqlreport output
    ----------------------------
    
    MariaDB 5.5.38-MariaDB      uptime 1 0:37:19    Tue Jun 10 18:15:02 2014
    
    __ Key _________________________________________________________________
    Buffer used         0 of   16.00M   %Used:   0.00
      Current       2.92M              %Usage:  18.24
    Write hit       0.00%
    Read hit        0.00%
    
    __ Questions ___________________________________________________________
    Total         308.93k       3.5/s
      Com_        731.42k       8.3/s  %Total: 236.76
      -Unknown    655.19k       7.4/s          212.08
      DMS         201.29k       2.3/s           65.16
      COM_QUIT     31.42k       0.4/s           10.17
    Slow 500 ms         0         0/s            0.00  %DMS:   0.00 Log: OFF
    DMS           201.29k       2.3/s           65.16
      SELECT      157.89k       1.8/s           51.11         78.44
      INSERT       35.12k       0.4/s           11.37         17.45
      DELETE        6.12k       0.1/s            1.98          3.04
      UPDATE        2.16k       0.0/s            0.70          1.07
      REPLACE           0         0/s            0.00          0.00
    Com_          731.42k       8.3/s          236.76
      stmt_prepar 200.70k       2.3/s           64.97
      stmt_execut 200.70k       2.3/s           64.97
      stmt_close  177.88k       2.0/s           57.58
    
    __ Rows ________________________________________________________________
    Rows           38.75M     437.2/s
      Using idx     3.63M      40.9/s  %Index:   9.35
    Rows/question  125.45
    
    __ SELECT and Sort _____________________________________________________
    Scan           42.60k       0.5/s %SELECT:  26.98
    Range          14.79k       0.2/s            9.37
    Full join         378       0.0/s            0.24
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan      30.30k       0.3/s
    Sort range      5.20k       0.1/s
    Sort mrg pass       7       0.0/s
    
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate     400.34k       4.5/s
    
    __ Tables ______________________________________________________________
    Open              419 of  10240    %Cache:   4.09
    Opened            518       0.0/s
    
    __ Connections _________________________________________________________
    Max used            5 of    350      %Max:   1.43
    Total          31.56k       0.4/s
    
    __ Created Temp ________________________________________________________
    Disk table      2.10k       0.0/s   %Disk:  49.73
    Table           4.23k       0.0/s    Size: 256.0M
    File               20       0.0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      2
    Created            54       0.0/s
    Slow                0         0/s
    Threadpool          7 of    500     %Used:   1.40
      Running           4 of    500  %Running:   0.80
      Idle              3 of    500     %Idle:   0.60
    
    __ Aborted _____________________________________________________________
    Clients           276       0.0/s
    Connects            0         0/s
    
    __ Bytes _______________________________________________________________
    Sent            1.17G     13.2k/s
    Received      115.47M      1.3k/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage          87.33M of  255.98M  %Usage:  34.11
    Read hit       99.99%
    Pages
      Free         10.79k              %Total:  65.89
      Data          5.42k                       33.06  %Drty:   0.00
      Misc            173                        1.06
      Latched           0                        0.00
    Reads          46.60M     525.8/s
      From disk     4.94k       0.1/s   %Disk:   0.01
      Ahead Rnd         0         0/s
    Writes        284.11k       3.2/s
    Flushes        36.51k       0.4/s
    Wait Free           0         0/s   %Wait:   0.00
    
    __ InnoDB Lock _________________________________________________________
    Waits               0         0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads         5.16k       0.1/s
      Writes       54.15k       0.6/s
      fsync        17.38k       0.2/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created         601       0.0/s
      Read          4.96k       0.1/s
      Written      36.51k       0.4/s
    
    Rows
      Deleted       7.71k       0.1/s
      Inserted     47.01k       0.5/s
      Read         37.00M     417.4/s
      Updated       7.53k       0.1/s
    
    __ Aria Pagecache ______________________________________________________
    Buffer used     1.72M of    8.00M   %Used:  21.48
      Current     328.00k              %Usage:   4.00
    Write hit     100.00%
    Read hit       91.45%
    
    Report Complete:
    Tue Jun 10 18:15:02 UTC 2014
    ----------------------------
     
    • Like Like x 1
  3. rdan

    rdan Premium Member Premium Member

    4,308
    1,044
    113
    May 25, 2014
    Ratings:
    +1,504
    Local Time:
    9:45 AM
    Mainline
    10.2
    And this one also:
    Code:
    [!!] Joins performed without indexes: 501
    I don't know how to make this status satisfy :D

    Currently, my join_buffer_size = 2.0M
     
  4. rdan

    rdan Premium Member Premium Member

    4,308
    1,044
    113
    May 25, 2014
    Ratings:
    +1,504
    Local Time:
    9:45 AM
    Mainline
    10.2
    OK Thanks :)
    I will run the script now. And hope understand the output :)
     
  5. eva2000

    eva2000 Administrator Staff Member

    36,396
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,304
    Local Time:
    11:45 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Well optimisation advice isn't part of Centmin Mod offering, but you can generally ignore such a low value like 501 IF it isn't causing any slow query and/or performance issues.

    If that is from mysqltuner, might want to run the modified mysqltuner version included with mysqlmymonlite.sh too via command
    Code:
    ./mysqlmymonlite.sh mysqltuner
    sample output of modified version http://mysqlmymon.com/#mysqltuner
     
..