[support] Reducing Number of Queries

[Brian Choc]

Greetings,
I've been asked for help regarding a site on a shared hosting provider
(MediaTemple).  Recently the owner received an email indicating
"..Today our automated systems checked your database activity again
and determined your activity continues to exceed the MySQL SmartPool
v.2 resources included with your Grid-Server..."

Caching is on (normal) as is block caching and the other options
available in the performance menu.  I've turned up the minimum cache
lifetime to an hour -- there is a fair amount of traffic but the
content is relatively static.  I've never had the "problem" of too
much traffic before, and I am not sure what tools are available to
attempt to minimize this, nor am I positive when how to evaluate when
resources are at their limit and it's simply best to upgrade the
server resources ... so I'm wondering if anyone would have any
suggestions or tools or tips on this?

I'll paste the included MySQL report below, in case that information
is helpful.  Thanks,
Brian




Your MySQL Report is shown below:

MySQL 4.1.25-Debian_mt1  uptime 2 0:0:33        Fri Dec 19 23:06:39 2008



__ Key _________________________________________________________________

Buffer used   816.00k of  24.00M  %Used:   3.32

  Current       3.50M            %Usage:  14.59

Write hit      49.36%

Read hit       99.98%



__ Questions ___________________________________________________________

Total           7.09M    41.0/s

  QC Hits       6.04M    34.9/s  %Total:  85.12

  DMS         632.06k     3.7/s            8.91

  Com_        262.45k     1.5/s            3.70

  COM_QUIT    161.04k     0.9/s            2.27

  -Unknown        120     0.0/s            0.00

Slow              236     0.0/s            0.00  %DMS:   0.04

DMS           632.06k     3.7/s            8.91

  SELECT      478.10k     2.8/s            6.74         75.64

  DELETE       52.29k     0.3/s            0.74          8.27

  UPDATE       51.03k     0.3/s            0.72          8.07

  INSERT       50.65k     0.3/s            0.71          8.01

  REPLACE           0       0/s            0.00          0.00

Com_          262.45k     1.5/s            3.70

  set_option  261.82k     1.5/s            3.69

  check           316     0.0/s            0.00

  show_proces     288     0.0/s            0.00



__ SELECT and Sort _____________________________________________________

Scan           65.59k     0.4/s %SELECT:  13.72

Range          86.67k     0.5/s           18.13

Full join           0       0/s            0.00

Range check         0       0/s            0.00

Full rng join       0       0/s            0.00

Sort scan       1.72k     0.0/s

Sort range    174.90k     1.0/s

Sort mrg pass       0       0/s



__ Query Cache _________________________________________________________

Memory usage    7.08M of  12.00M  %Used:  58.97

Block Fragmnt  15.76%

Hits            6.04M    34.9/s

Inserts       478.10k     2.8/s

Insrt:Prune    1.55:1     1.0/s

Hit:Insert    12.63:1



__ Table Locks _________________________________________________________

Waited             48     0.0/s  %Total:   0.00

Immediate       1.18M     6.8/s



__ Tables ______________________________________________________________

Open              331 of 1024    %Cache:  32.32

Opened            337     0.0/s



__ Connections _________________________________________________________

Max used           34 of   60      %Max:  56.67

Total         161.11k     0.9/s



__ Created Temp ________________________________________________________

Disk table         59     0.0/s

Table           1.36k     0.0/s

File                4     0.0/s



__ Threads _____________________________________________________________

Running             1 of    1

Cached              3 of    4      %Hit:  99.83

Created           271     0.0/s

Slow                0       0/s



__ Aborted _____________________________________________________________

Clients           133     0.0/s

Connects           92     0.0/s



__ Bytes _______________________________________________________________

Sent          724.69M    4.2k/s

Received        1.46G    8.4k/s