Do I need tweak something for this MySQL setup?

Question

I'm running a fairly popular website with the following stats and setup.

stats: ~250,000 pageviews per day, rendering ~47 queries / second to MySQL setup: the MySQL instance is running alone on a Linode512 on Debian 6.0. Nothing else runs there, for performance reasons. Using MyISAM and the ini for "large" mysql.

I see the following figures right now from MYSQLREPORT.

Please advice. The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not. I'm not very good at understanding the inner workings of MYSQL. Also, I can't figure out why the QC is being hit so low i.e. why isn't it using all of the QC.

-- Key --
Buffer used   226.52M of 256.00M  %Used:  88.49
  Current     256.00M            
 Write hit      64.93%
 Read hit      100.00%

-- Questions --
Total         139.38M    47.0/s
  QC Hits      51.28M    17.3/s  %Total:  36.79
  DMS          41.99M    14.2/s           30.12
  Com-         30.75M    10.4/s           22.06
  COM-QUIT     15.36M     5.2/s           11.02
  +Unknown     10.68k     0.0/s            0.01
Slow 10 s         127     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS            41.99M    14.2/s           30.12
  SELECT       35.92M    12.1/s           25.77         85.56
  UPDATE        3.15M     1.1/s            2.26          7.50
  INSERT        2.66M     0.9/s            1.91          6.33
  DELETE      256.47k     0.1/s            0.18          0.61
  REPLACE           0       0/s            0.00          0.00
Com-           30.75M    10.4/s           22.06
  set-option   15.38M     5.2/s           11.03
  change-db    15.36M     5.2/s           11.02
  show-fields   2.70k     0.0/s            0.00

-- SELECT and Sort --
Scan            1.21M     0.4/s %SELECT:   3.38
Range         175.99k     0.1/s            0.49
Full join          24     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       2.51M     0.8/s
Sort range      4.04M     1.4/s
Sort mrg pass 177.95k     0.1/s

-- Query Cache --
Memory usage    2.78M of  16.00M  %Used:  17.38
Block Fragmnt  23.23%
Hits           51.28M    17.3/s
Inserts        33.35M    11.2/s
Insrt:Prune   68.97:1    11.1/s
Hit:Insert     1.54:1

-- Table Locks --
Waited          1.19M     0.4/s  %Total:   1.74
Immediate      67.07M    22.6/s

-- Tables --
Open              227 of  256    %Cache:  88.67
Opened          1.16k     0.0/s

-- Connections --
Max used           27 of  151      %Max:  17.88
Total          15.36M     5.2/s

-- Created Temp --
Disk table      2.16M     0.7/s
Table           2.30M     0.8/s    Size:  16.0M
File          327.26k     0.1/s

-- Threads --
Running             7 of    7
Cached              1 of    8      %Hit:  99.88
Created        19.02k     0.0/s
Slow                0       0/s

-- Aborted --
Clients             4     0.0/s
Connects           26     0.0/s

-- Bytes --
Sent          958.38M   323.0/s
Received      954.98M   321.9/s`

Btw, I tried posting this question in the DBA forums of stack exchange prior to here but it's like a grave in the other forums. Appreciate the help.

Answer 1

Your questions come down to, basically, "Do I have a problem" and you go on to say at the moment no but not sure about the future.

The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not.

I don't think anyone can suggest that there is or is not a problem here based on the fact that performance is currently ok but you are worried about long-term performance concerns. Without knowing what sort of growth you are expecting nobody can give more than a guess.

So I am going to go a different approach and suggest that you need to start with a framework for determining whether you need to start thinking about optimizing/upgrading. This seems the best way at this current point.

1. Your database looks like it fits in RAM. Is it growing? How fast? How big is it? When will it exceed 250M in size? When will it exceed your server's RAM?

2. How many concurrent connections do you typically have? How fast is this growing?

Once you have this sort of data (and you really need to start with hard numbers here!) then it should be pretty straight-forward to determine whether you need to be thinking about capacity or performance tuning. However as long as performance is good right now, then you have time to gather data, evaluate, and plan.

UPDATE BASED ON FEEDBACK

So the db doesn't fit in RAM. It is apparently a virtualized server with only 512MB of RAM. I don't think you need a larger system yet but it is worth keeping your eyes on the performance numbers.

The cache is going to be the MySQL page cache. This is used to store frequently used parts of the database in memory. Note that ALL of your select and update queries are currently hitting the cache. Your inserts are not as expected. This means you have enough ram reserved for the MySQL page cache to hold everything being queried in RAM. While I had assumed it was the full db, actually unqueried portions of the db would not need to fit in RAM for these numbers.

You have a few options. You could decide to get more RAM (and run tuner programs as @RolandoMySQLDBA suggests) when your cache utilization hits 90% or so. You could upgrade things now with the idea you may likely need it. Or you could be more conservative and wait for some read queries not to hit the cache on a consistent basis. It depends on what you see as necessary performance.