/ Register

Get the most out of your Centmin Mod LEMP stack
Become a Member

Issue: The rate of opening tables is high.

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

Tags:
Previous Thread Next Thread
Loading...
  1. Jun 11, 2014 #1
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    8:36 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. Jun 11, 2014 #2
    eva2000

    eva2000 Administrator Staff Member

    53,614
    12,139
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,683
    Local Time:
    10:36 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
----------------------------
     
  3. Jun 11, 2014 #3
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    8:36 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. Jun 11, 2014 #4
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    8:36 AM
    Mainline
    10.2
    OK Thanks :)
    I will run the script now. And hope understand the output :)
     
  5. Jun 11, 2014 #5
    eva2000

    eva2000 Administrator Staff Member

    53,614
    12,139
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,683
    Local Time:
    10:36 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
Previous Thread Next Thread
Loading...

.