Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 8 '13 at 23:10

This question came from our site for professional and enthusiast programmers.

    
1) You need to run mysqltuner.pl against the MySQL Instance and present the output in your question. 2) You need to provide the most frequent queries 3) how much RAM on the DB Server 4) Show the output of this Query SELECT SUM(index_length)/POWER(1024,2) FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql') –  RolandoMySQLDBA Mar 9 '13 at 7:39
    
@Ypercube, going to suggest that the question you linked to gets closed instead of this one, since the comments are all on this question. –  Chris Travers Mar 9 '13 at 10:37
add comment

1 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.

share|improve this answer
    
Thanks for the effort Chris. The Db will not fit in RAM. Linode512 implies that it's a 512 MB RAM machine and the db is 200 MB when GZIP'ed and growing with 1 MB / month. –  Adergaard Mar 10 '13 at 18:43
    
Ok, however all your reads are hitting cache, suggesting that whatever is in cache what's being served. At some point you may need more RAM if the used set of data grows. –  Chris Travers Mar 11 '13 at 1:30
    
Updated my answer with more info –  Chris Travers Mar 11 '13 at 1:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.