Welcome to Centmin Mod Community
Register Now

Issue: The rate of opening tables is high.

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

  1. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    1:59 PM
    1.11
    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

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    3:59 PM
    Nginx 1.13.x
    MariaDB 5.5
    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. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    1:59 PM
    1.11
    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. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    1:59 PM
    1.11
    10.2
    OK Thanks :)
    I will run the script now. And hope understand the output :)
     
  5. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    3:59 PM
    Nginx 1.13.x
    MariaDB 5.5
    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