Thread

469 is nothing…You also have to be aware that table_cache caches a table per mysql connection. So if you have 5 users accessing the same table at the same time, then mysql will cache that same table 5 times at different states. Hope that helps.

Since friday the load of server increase

[root@uniradioserver ~]# uptime
10:13:37 up 6 days, 12:45,  2 users,  load average: 6.66, 7.50, 7.96

[root@uniradioserver ~]# free -m
        total     used     free   shared   buffers   cached
Mem:      5253     2596     2656       0       0       0
-/+ buffers/cache:    2596     2656
Swap:        0       0       0
[root@uniradioserver ~]#

We didn’t anything in the website. 

I run again mytuner and this are the results

————General Statistics—————————————————————————
[—] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.54
[OK] Operating on 64-bit architecture

————Storage Engine Statistics—————————————————————-
[—] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[—] Data in MyISAM tables: 123M (Tables: 246)
[—] Data in InnoDB tables: 489M (Tables: 223)
[!!] Total fragmented tables: 228

————Security Recommendations —————————————————————-
[OK] All database users have passwords assigned

————Performance Metrics————————————————————————-
[—] Up for: 2d 20h 3m 46s (9M q [40.058 qps], 773K conn, TX: 5B, RX: 1B)
[—] Reads / Writes: 59% / 41%
[—] Total buffers: 1.3G global + 3.8M per thread (200 max threads)
[OK] Maximum possible memory usage: 2.0G (38% of installed RAM)
[OK] Slow queries: 0% (1K/9M)
[OK] Highest usage of available connections: 49% (99/200)
[OK] Key buffer size / total MyISAM indexes: 128.0M/24.5M
[OK] Key buffer hit rate: 100.0% (68M cached / 11K reads)
[!!] Query cache efficiency: 14.2% (595K cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 10% (280K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 384637
[OK] Temporary tables created on disk: 4% (29K on disk / 615K total)
[OK] Thread cache hit rate: 99% (1K created / 773K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 21% (911/4K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] InnoDB data size / buffer pool: 490.0M/1.0G

I run the mysqlreport and the results are these

MySQL 5.1.54         uptime 2 20:4:58     Mon Jun 18 10:12:21 2012

__ Key _________________________________________________________________
Buffer used   11.17M of 128.00M %Used:  8.72
  Current     34.50M         %Usage:  26.96
Write hit     88.86%
Read hit     99.98%

__ Questions ___________________________________________________________
Total       9.82M   40.1/s
  DMS       6.01M   24.5/s %Total:  61.19
  Com_       2.44M   10.0/s       24.87
  COM_QUIT   773.64k   3.2/s         7.88
  QC Hits   595.94k   2.4/s         6.07
  +Unknown       56   0.0/s         0.00
Slow 10 s     1.38k   0.0/s         0.01 %DMS:  0.02 Log: OFF
DMS         6.01M   24.5/s       61.19
  SELECT     3.60M   14.7/s       36.66       59.92
  UPDATE     2.40M   9.8/s       24.48       40.00
  INSERT     3.86k   0.0/s         0.04       0.06
  DELETE     1.14k   0.0/s         0.01       0.02
  REPLACE       38   0.0/s         0.00       0.00
Com_         2.44M   10.0/s       24.87
  set_option 917.23k   3.7/s         9.34
  begin     518.13k   2.1/s         5.28
  commit     518.13k   2.1/s         5.28

__ SELECT and Sort _____________________________________________________
Scan         3.64M   14.9/s %SELECT: 101.21
Range       9.13k   0.0/s         0.25
Full join   385.00k   1.6/s       10.69
Range check       0     0/s         0.00
Full rng join     0     0/s         0.00
Sort scan     2.77M   11.3/s
Sort range   33.92k   0.1/s
Sort mrg pass 281.11k   1.1/s

__ Query Cache _________________________________________________________
Memory usage   15.99M of 128.00M %Used:  12.49
Block Fragmnt 32.26%
Hits       595.94k   2.4/s
Inserts     878.51k   3.6/s
Insrt:Prune 878.51k:1   3.6/s
Hit:Insert   0.68:1

__ Table Locks _________________________________________________________
Waited       29.84k   0.1/s %Total:  0.41
Immediate     7.32M   29.9/s

__ Tables ______________________________________________________________
Open         1201 of 2048

__ Tables ______________________________________________________________
Open         1201 of 2048

__ Tables ______________________________________________________________
Open         1201 of 2048

It looks like mysql is doing a good job, the problem persists in your IO. You really need to install iosats so you can check the io perfomance. Most vps providers will limit your IO and that’s the number one resources that mysql needs, or any database server. You can also try to optimize your tables, that should help a bit, but not by much.

Also make sure you join buffer size is not bigger than 1M, mysqltuner is a great tool, but it doesn’t give a good recommendation on the join_buffer_size. Your table cache looks good, like I explain in my previous post, table_cache is depended on the number of tables in your DB and your max_connection setting.

enable slow query logs and check which querys as slow, might be that you have 1 query blocking the rest
L: