# What will be the best way to load tables in memory to speed up access.

**URL:** <https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085>\
**Category:** Sphinx & Full-Text Search\
**Created:** [May 10, 2010, 12:30pm UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085 "2010-05-10T12:30:02Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ucables](https://avatars.discourse-cdn.com/v4/letter/u/e56c9b/32.png) [@ucables](https://forums.percona.com/u/ucables)\
**Post date:** [May 10, 2010, 12:30pm UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/1 "2010-05-10T12:30:02Z")

</div>

i have much traffic at my site.  
Queries are always different, so query cache not works.  
my products table is not modified never, so i think the best way should be to use a memory engine for this table, but it doesnt support fulltext indexes, can you tell me any other way to optimize my search?  
i use key\_buffer\_size = 3G, to have indexed in memory.

but its still slow.

What will be the best way to load tables in memory to speed up access.

i can hold tables in memory without problems but how can i do it using myisam tables?

I would like to optimize search time, because my server load is very high.

here i have some info about index use and configuration:

my.cnf is:

mysql\> show status like “key%”;  
±-----------------------±------------+  
| Variable\_name | Value |  
±-----------------------±------------+  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 0 |  
| Key\_blocks\_used | 6698 |  
| Key\_read\_requests | 24950073645 |  
| Key\_reads | 527042599 |  
| Key\_write\_requests | 36710021 |  
| Key\_writes | 2662162 |  
±-----------------------±------------+  
7 rows in set (0.00 sec)

this is my.cnf:

[mysqld]  
init\_connect=‘SET collation\_connection = utf8\_general\_ci’  
init\_connect=‘SET NAMES utf8’  
ft\_min\_word\_len=3  
key\_buffer\_size=1500M  
open-files-limit=20000  
query\_cache\_size= 64M  
max\_connections = 256  
safe-show-database  
skip-locking  
key\_buffer = 8M  
max\_allowed\_packet = 1M  
table\_cache = 512  
sort\_buffer\_size = 8M  
read\_buffer\_size = 8M  
read\_rnd\_buffer\_size = 2M  
myisam\_sort\_buffer\_size =8M  
thread\_cache\_size = 8  
delay\_key\_write = ALL  
low\_priority\_updates=1  
concurrent\_insert=2  
thread\_concurrency = 8  
wait\_timeout = 90

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [May 10, 2010, 9:53pm UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/2 "2010-05-10T21:53:22Z")

</div>

I see

key\_buffer\_size=1500M  
key\_buffer = 8M # That’s the same thing, so 8M wins

and you say key\_buffer\_size = 3G. What size is it really? Check SHOW VARIABLES.

---

<div class="post-metadata">

**Author:** ![ucables](https://avatars.discourse-cdn.com/v4/letter/u/e56c9b/32.png) [@ucables](https://forums.percona.com/u/ucables)\
**Post date:** [May 11, 2010, 3:13am UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/3 "2010-05-11T03:13:18Z")

</div>

sorry it was before i already changed both to 3G with same load problem.

key\_buffer\_size=3G  
key\_buffer = 3G

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [May 11, 2010, 8:45am UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/4 "2010-05-11T08:45:46Z")

</div>

You should get a whole-server picture of what is going on and try to understand where the time is being consumed by these queries. I have a few suggestions.

- Get system-wide stats from iostat, vmstat
- Get incremental samples of SHOW GLOBAL STATUS  
(try mysqladmin -ri10 -c3)
- Use the Percona builds and set long\_query\_time = 0 and analyze your slow query log with mk-query-digest.

---

<div class="post-metadata">

**Author:** ![ucables](https://avatars.discourse-cdn.com/v4/letter/u/e56c9b/32.png) [@ucables](https://forums.percona.com/u/ucables)\
**Post date:** [May 11, 2010, 10:45am UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/5 "2010-05-11T10:45:06Z")

</div>

At: Tue May 11 16:50:54 CEST 2010

> > vmstat  
> > procs -----------memory---------- —swap-- -----io---- --system-- ----cpu----  
> > r b swpd free buff cache si so bi bo in cs us sy id wa  
> > 12 0 2392 845552 453864 3154372 0 0 169 261 19 3 63 13 23 1  
> > vmstat  
> > procs -----------memory---------- —swap-- -----io---- --system-- ----cpu----  
> > r b swpd free buff cache si so bi bo in cs us sy id wa  
> > 8 0 2392 930496 453944 3130560 0 0 169 261 19 3 63 13 23 1

mysql\> show global status;  
±----------------------------------±------------+  
| Variable\_name | Value |  
±----------------------------------±------------+  
| Aborted\_clients | 5 |  
| Aborted\_connects | 55 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 99971545 |  
| Bytes\_sent | 18622374756 |  
| Com\_admin\_commands | 47 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 2 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_call\_procedure | 0 |  
| Com\_change\_db | 94884 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 8 |  
| Com\_create\_user | 0 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 692 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 8 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 0 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 7581 |  
| Com\_insert\_select | 12 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 119 |  
| Com\_optimize | 0 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 1159 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 139435 |  
| Com\_set\_option | 50335 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 0 |  
| Com\_show\_charsets | 0 |  
| Com\_show\_collations | 0 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 1 |  
| Com\_show\_databases | 49 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 53 |  
| Com\_show\_grants | 0 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 0 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 0 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 55 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 12 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 767 |  
| Com\_show\_triggers | 1 |  
| Com\_show\_variables | 46 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 7 |  
| Com\_unlock\_tables | 119 |  
| Com\_update | 5560 |  
| Com\_update\_multi | 105 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 49731 |  
| Created\_tmp\_disk\_tables | 4208 |  
| Created\_tmp\_files | 3987 |  
| Created\_tmp\_tables | 46252 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 0 |  
| Handler\_delete | 868 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 8909 |  
| Handler\_read\_key | 742373871 |  
| Handler\_read\_next | 2382135880 |  
| Handler\_read\_prev | 8136086 |  
| Handler\_read\_rnd | 355547889 |  
| Handler\_read\_rnd\_next | 2413572547 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 1170313 |  
| Handler\_write | 61208946 |  
| Innodb\_buffer\_pool\_pages\_data | 508 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 4 |  
| Innodb\_buffer\_pool\_pages\_total | 512 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 40 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 0 |  
| Innodb\_buffer\_pool\_read\_requests | 39103 |  
| Innodb\_buffer\_pool\_reads | 638 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 1 |  
| Innodb\_data\_fsyncs | 7 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 18075648 |  
| Innodb\_data\_reads | 749 |  
| Innodb\_data\_writes | 7 |  
| Innodb\_data\_written | 35328 |  
| Innodb\_dblwr\_pages\_written | 1 |  
| Innodb\_dblwr\_writes | 1 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 0 |  
| Innodb\_log\_writes | 2 |  
| Innodb\_os\_log\_fsyncs | 5 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 1024 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 0 |  
| Innodb\_pages\_read | 970 |  
| Innodb\_pages\_written | 1 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 0 |  
| Innodb\_row\_lock\_time\_avg | 0 |  
| Innodb\_row\_lock\_time\_max | 0 |  
| Innodb\_row\_lock\_waits | 0 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 0 |  
| Innodb\_rows\_read | 84 |  
| Innodb\_rows\_updated | 0 |  
| Key\_blocks\_not\_flushed | 6625 |  
| Key\_blocks\_unused | 2044648 |  
| Key\_blocks\_used | 569490 |  
| Key\_read\_requests | 3264619774 |  
| Key\_reads | 943150 |  
| Key\_write\_requests | 6950219 |  
| Key\_writes | 650 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 27 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 1989 |  
| Open\_streams | 0 |  
| Open\_tables | 1024 |  
| Opened\_tables | 4451 |  
| Prepared\_stmt\_count | 0 |  
| Qcache\_free\_blocks | 597 |  
| Qcache\_free\_memory | 3652608 |  
| Qcache\_hits | 266788 |  
| Qcache\_inserts | 132381 |  
| Qcache\_lowmem\_prunes | 81801 |  
| Qcache\_not\_cached | 7009 |  
| Qcache\_queries\_in\_cache | 5754 |  
| Qcache\_total\_blocks | 20227 |  
| Queries | 617491 |  
| Questions | 617491 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 11216 |  
| Select\_full\_range\_join | 12 |  
| Select\_range | 19471 |  
| Select\_range\_check | 0 |  
| Select\_scan | 24624 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 117 |  
| Sort\_merge\_passes | 1988 |  
| Sort\_range | 10967 |  
| Sort\_rows | 835675948 |  
| Sort\_scan | 54534 |  
| Table\_locks\_immediate | 267863 |  
| Table\_locks\_waited | 108 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 8 |  
| Threads\_connected | 10 |  
| Threads\_created | 113 |  
| Threads\_running | 2 |  
| Uptime | 13419 |  
±----------------------------------±------------+

* * *

two hours later when load was more high:

> > iostat  
> > Linux 2.6.9-89.0.23.ELsmp ([host.cables2.com](http://host.cables2.com)) 05/11/2010

avg-cpu: %user %nice %sys %iowait %idle  
63.28 0.14 12.64 1.07 22.87

Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtn  
sda 68.69 1291.20 2726.62 208168664 439588784  
sda1 0.00 0.01 0.00 1438 16  
sda2 3.01 63.74 106.55 10277030 17178904  
sda3 0.01 0.03 0.99 4108 160384  
sda4 0.00 0.00 0.00 2 0  
sda5 4.43 9.28 59.25 1495710 9551640  
sda6 0.00 0.01 0.00 1080 0  
sda7 27.97 21.58 2203.70 3478602 355282696  
sda8 33.28 1196.55 356.13 192909470 57415144  
sdb 7.38 1335.18 1342.68 215258746 216468440  
sdb1 7.38 1335.16 1342.68 215256754 216468440

> > vmstat  
> > procs -----------memory---------- —swap-- -----io---- --system-- ----cpu----  
> > r b swpd free buff cache si so bi bo in cs us sy id wa  
> > 4 1 2268 1498932 495832 3552380 0 0 164 254 25 25 63 13 23 1

±----------------------------------±------------+  
| Variable\_name | Value |  
±----------------------------------±------------+  
| Aborted\_clients | 10 |  
| Aborted\_connects | 74 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 143470260 |  
| Bytes\_sent | 31297602171 |  
| Com\_admin\_commands | 100 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 2 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_call\_procedure | 0 |  
| Com\_change\_db | 137892 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 11 |  
| Com\_create\_user | 0 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 968 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 11 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 0 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 11221 |  
| Com\_insert\_select | 17 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 189 |  
| Com\_optimize | 0 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 1531 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 200869 |  
| Com\_set\_option | 68391 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 0 |  
| Com\_show\_charsets | 0 |  
| Com\_show\_collations | 0 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 1 |  
| Com\_show\_databases | 49 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 68 |  
| Com\_show\_grants | 0 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 0 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 0 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 71 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 13 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 807 |  
| Com\_show\_triggers | 1 |  
| Com\_show\_variables | 54 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 10 |  
| Com\_unlock\_tables | 189 |  
| Com\_update | 7586 |  
| Com\_update\_multi | 157 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 67749 |  
| Created\_tmp\_disk\_tables | 6159 |  
| Created\_tmp\_files | 6549 |  
| Created\_tmp\_tables | 70386 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 0 |  
| Handler\_delete | 1150 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 11885 |  
| Handler\_read\_key | 1230112521 |  
| Handler\_read\_next | 3582840711 |  
| Handler\_read\_prev | 11228610 |  
| Handler\_read\_rnd | 592431175 |  
| Handler\_read\_rnd\_next | 3505318198 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 1568855 |  
| Handler\_write | 92039292 |  
| Innodb\_buffer\_pool\_pages\_data | 508 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 4 |  
| Innodb\_buffer\_pool\_pages\_total | 512 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 40 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 0 |  
| Innodb\_buffer\_pool\_read\_requests | 39105 |  
| Innodb\_buffer\_pool\_reads | 638 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 1 |  
| Innodb\_data\_fsyncs | 7 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 18075648 |  
| Innodb\_data\_reads | 749 |  
| Innodb\_data\_writes | 7 |  
| Innodb\_data\_written | 35328 |  
| Innodb\_dblwr\_pages\_written | 1 |  
| Innodb\_dblwr\_writes | 1 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 0 |  
| Innodb\_log\_writes | 2 |  
| Innodb\_os\_log\_fsyncs | 5 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 1024 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 0 |  
| Innodb\_pages\_read | 970 |  
| Innodb\_pages\_written | 1 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 0 |  
| Innodb\_row\_lock\_time\_avg | 0 |  
| Innodb\_row\_lock\_time\_max | 0 |  
| Innodb\_row\_lock\_waits | 0 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 0 |  
| Innodb\_rows\_read | 84 |  
| Innodb\_rows\_updated | 0 |  
| Key\_blocks\_not\_flushed | 9954 |  
| Key\_blocks\_unused | 2026169 |  
| Key\_blocks\_used | 569490 |  
| Key\_read\_requests | 5352686668 |  
| Key\_reads | 961226 |  
| Key\_write\_requests | 10127220 |  
| Key\_writes | 662 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 37 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 1944 |  
| Open\_streams | 0 |  
| Open\_tables | 1024 |  
| Opened\_tables | 4666 |  
| Prepared\_stmt\_count | 0 |  
| Qcache\_free\_blocks | 133 |  
| Qcache\_free\_memory | 129525296 |  
| Qcache\_hits | 385534 |  
| Qcache\_inserts | 192162 |  
| Qcache\_lowmem\_prunes | 134526 |  
| Qcache\_not\_cached | 8348 |  
| Qcache\_queries\_in\_cache | 1906 |  
| Qcache\_total\_blocks | 6129 |  
| Queries | 883341 |  
| Questions | 883341 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 16947 |  
| Select\_full\_range\_join | 22 |  
| Select\_range | 28834 |  
| Select\_range\_check | 0 |  
| Select\_scan | 35614 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 192 |  
| Sort\_merge\_passes | 3266 |  
| Sort\_range | 14222 |  
| Sort\_rows | 1379382967 |  
| Sort\_scan | 82246 |  
| Table\_locks\_immediate | 388285 |  
| Table\_locks\_waited | 124 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 12 |  
| Threads\_connected | 7 |  
| Threads\_created | 285 |  
| Threads\_running | 5 |  
| Uptime | 18190 |  
±----------------------------------±------------+  
225 rows in set (0.00 sec)

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [May 11, 2010, 12:27pm UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/6 "2010-05-11T12:27:21Z")

</div>

vmstat and iostat are not very useful the way you ran them. You need to run them for several iterations. The first line you see is just averages since boot. Try

vmstat 5 5  
iostat -dx 5 5

Also please use something like mext (from Aspersa project) to digest the show status so I don’t have to do math on it. mysqladmin -ri5 -c5 is better.

---

<div class="post-metadata">

**Author:** ![ucables](https://avatars.discourse-cdn.com/v4/letter/u/e56c9b/32.png) [@ucables](https://forums.percona.com/u/ucables)\
**Post date:** [May 11, 2010, 12:46pm UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/7 "2010-05-11T12:46:43Z")

</div>

account \>\> vmstat 5 5procs -----------memory---------- —swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa11 0 2212 1210384 470724 4443416 0 0 159 246 32 12 64 13 22 115 0 2212 1375816 470744 4306240 0 0 42 274 1404 1798 84 16 0 011 0 2212 1256208 470800 4358136 0 0 538 182 2063 2394 82 18 0 0 7 0 2212 1473584 470876 4277548 0 0 522 198 2423 801 71 11 18 012 0 2212 1386752 470900 4283304 0 0 26 266 1566 2529 84 13 2 0account \>\> iostat -dx 5 5Linux 2.6.9-89.0.23.ELsmp ([host.cables2.com](http://host.cables2.com)) 05/11/2010Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 2.24 290.06 26.53 40.91 1261.46 2649.01 630.73 1324.51 57.98 2.45 36.27 1.50 10.12sda1 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 39.95 0.00 3.43 3.43 0.00sda2 0.04 11.33 0.99 1.98 61.30 106.46 30.65 53.23 56.56 0.06 21.33 3.50 1.04sda3 0.00 0.11 0.00 0.01 0.02 0.95 0.01 0.48 88.17 0.00 15.58 8.16 0.01sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2.00 0.00 15.00 15.00 0.00sda5 0.02 3.59 0.61 3.85 9.30 59.59 4.65 29.80 15.45 0.05 12.07 3.62 1.61sda6 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 54.60 0.00 6.85 6.85 0.00sda7 0.03 238.18 0.54 26.39 21.18 2116.63 10.59 1058.32 79.39 1.59 59.11 0.66 1.77sda8 2.14 36.85 24.40 8.67 1169.64 365.37 584.82 182.69 46.41 0.74 22.28 2.50 8.28sdb 0.27 159.56 5.57 1.52 1281.42 1288.62 640.71 644.31 362.68 1.44 203.18 4.01 2.84sdb1 0.27 159.56 5.57 1.52 1281.40 1288.62 640.70 644.31 362.68 1.44 203.18 4.01 2.84Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 54.18 7.37 54.58 82.87 870.12 41.43 435.06 15.38 4.63 74.82 1.79 11.12sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 10.36 0.00 1.00 0.00 90.84 0.00 45.42 91.20 0.00 4.60 2.80 0.28sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 2.59 0.60 2.79 11.16 43.03 5.58 21.51 16.00 0.05 13.94 13.18 4.46sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 3.78 0.80 0.40 6.37 33.47 3.19 16.73 33.33 0.00 1.17 1.17 0.14sda8 0.00 37.45 5.98 50.40 65.34 702.79 32.67 351.39 13.63 4.58 81.28 1.76 9.92sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.20 63.53 5.41 16.03 80.16 638.08 40.08 319.04 33.50 0.36 16.90 2.41 5.17sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 12.22 0.00 8.22 0.00 163.53 0.00 81.76 19.90 0.10 12.71 1.32 1.08sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 0.80 0.40 1.00 4.81 14.43 2.40 7.21 13.71 0.01 4.14 2.43 0.34sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 5.01 2.20 3.61 17.64 68.94 8.82 34.47 14.90 0.21 36.41 3.76 2.18sda8 0.20 45.49 2.81 3.21 57.72 391.18 28.86 195.59 74.67 0.04 6.73 4.23 2.55sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 51.79 1.00 9.36 19.12 489.24 9.56 244.62 49.08 0.06 5.40 2.25 2.33sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 16.53 0.00 1.39 0.00 143.43 0.00 71.71 102.86 0.00 3.14 2.14 0.30sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 9.56 0.60 6.18 11.16 125.90 5.58 62.95 20.24 0.04 6.56 2.18 1.47sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 6.77 0.00 0.60 0.00 58.96 0.00 29.48 98.67 0.00 0.67 0.33 0.02sda8 0.00 18.92 0.40 1.20 7.97 160.96 3.98 80.48 106.00 0.01 4.25 3.38 0.54sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %utilsda 0.00 45.20 2.20 5.80 134.40 408.00 67.20 204.00 67.80 0.05 5.70 3.05 2.44sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 10.00 0.00 1.80 0.00 94.40 0.00 47.20 52.44 0.01 5.00 2.22 0.40sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda5 0.00 0.80 0.00 1.00 0.00 14.40 0.00 7.20 14.40 0.00 3.60 2.20 0.22sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda7 0.00 5.80 0.00 0.40 0.00 49.60 0.00 24.80 124.00 0.00 0.00 0.00 0.00sda8 0.00 28.60 2.20 2.60 134.40 249.60 67.20 124.80 80.00 0.03 6.88 3.79 1.82sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00Aborted\_clients 37 0 0 0 0Aborted\_connects 103 0 0 0 0Binlog\_cache\_disk\_use 0 0 0 0 0Binlog\_cache\_use 0 0 0 0 0Bytes\_received 210921284 41622 41219 31101 59423Bytes\_sent 53189657487 13789200 1796858 1137202 802390Com\_admin\_commands 314 0 0 0 0Com\_alter\_db 0 0 0 0 0Com\_alter\_table 5 0 0 0 0Com\_analyze 0 0 0 0 0Com\_backup\_table 0 0 0 0 0Com\_begin 0 0 0 0 0Com\_call\_procedure 0 0 0 0 0Com\_change\_db 201073 44 45 50 28Com\_change\_master 0 0 0 0 0Com\_check 13 0 0 0 0Com\_checksum 0 0 0 0 0Com\_commit 0 0 0 0 0Com\_create\_db 0 0 0 0 0Com\_create\_function 0 0 0 0 0Com\_create\_index 0 0 0 0 0Com\_create\_table 16 0 0 0 0Com\_create\_user 0 0 0 0 0Com\_dealloc\_sql 0 0 0 0 0Com\_delete 1417 0 0 0 0Com\_delete\_multi 0 0 0 0 0Com\_do 0 0 0 0 0Com\_drop\_db 0 0 0 0 0Com\_drop\_function 0 0 0 0 0Com\_drop\_index 0 0 0 0 0Com\_drop\_table 15 0 0 0 0Com\_drop\_user 0 0 0 0 0Com\_execute\_sql 0 0 0 0 0Com\_flush 0 0 0 0 0Com\_grant 0 0 0 0 0Com\_ha\_close 0 0 0 0 0Com\_ha\_open 0 0 0 0 0Com\_ha\_read 0 0 0 0 0Com\_help 0 0 0 0 0Com\_insert 16095 2 1 10 2Com\_insert\_select 27 0 0 0 0Com\_kill 0 0 0 0 0Com\_load 0 0 0 0 0Com\_load\_master\_data 0 0 0 0 0Com\_load\_master\_table 0 0 0 0 0Com\_lock\_tables 317 0 0 0 0Com\_optimize 0 0 0 0 0Com\_preload\_keys 0 0 0 0 0Com\_prepare\_sql 0 0 0 0 0Compression 0 0 0 0 0Com\_purge 0 0 0 0 0Com\_purge\_before\_date 0 0 0 0 0Com\_rename\_table 0 0 0 0 0Com\_repair 0 0 0 0 0Com\_replace 2178 0 0 0 3Com\_replace\_select 0 0 0 0 0Com\_reset 0 0 0 0 0Com\_restore\_table 0 0 0 0 0Com\_revoke 0 0 0 0 0Com\_revoke\_all 0 0 0 0 0Com\_rollback 0 0 0 0 0Com\_savepoint 0 0 0 0 0Com\_select 300160 60 49 52 55Com\_set\_option 95232 14 14 22 14Com\_show\_binlog\_events 0 0 0 0 0Com\_show\_binlogs 0 0 0 0 0Com\_show\_charsets 0 0 0 0 0Com\_show\_collations 0 0 0 0 0Com\_show\_column\_types 0 0 0 0 0Com\_show\_create\_db 0 0 0 0 0Com\_show\_create\_table 3 0 0 0 0Com\_show\_databases 49 0 0 0 0Com\_show\_errors 0 0 0 0 0Com\_show\_fields 132 0 0 0 0Com\_show\_grants 0 0 0 0 0Com\_show\_innodb\_status 0 0 0 0 0Com\_show\_keys 0 0 0 0 0Com\_show\_logs 0 0 0 0 0Com\_show\_master\_status 0 0 0 0 0Com\_show\_ndb\_status 0 0 0 0 0Com\_show\_new\_master 0 0 0 0 0Com\_show\_open\_tables 0 0 0 0 0Com\_show\_privileges 0 0 0 0 0Com\_show\_processlist 98 0 0 0 0Com\_show\_slave\_hosts 0 0 0 0 0Com\_show\_slave\_status 0 0 0 0 0Com\_show\_status 52 1 1 1 1Com\_show\_storage\_engines 0 0 0 0 0Com\_show\_tables 872 0 0 0 0Com\_show\_triggers 2 0 0 0 0Com\_show\_variables 74 0 0 0 0Com\_show\_warnings 0 0 0 0 0Com\_slave\_start 0 0 0 0 0Com\_slave\_stop 0 0 0 0 0Com\_stmt\_close 0 0 0 0 0Com\_stmt\_execute 0 0 0 0 0Com\_stmt\_fetch 0 0 0 0 0Com\_stmt\_prepare 0 0 0 0 0Com\_stmt\_reset 0 0 0 0 0Com\_stmt\_send\_long\_data 0 0 0 0 0Com\_truncate 14 0 0 0 0Com\_unlock\_tables 316 0 0 0 0Com\_update 10882 3 2 0 1Com\_update\_multi 210 0 0 0 0Com\_xa\_commit 0 0 0 0 0Com\_xa\_end 0 0 0 0 0Com\_xa\_prepare 0 0 0 0 0Com\_xa\_recover 0 0 0 0 0Com\_xa\_rollback 0 0 0 0 0Com\_xa\_start 0 0 0 0 0Connections 94473 14 14 22 14Created\_tmp\_disk\_tables 8843 1 2 2 1Created\_tmp\_files 11121 6 2 0 4Created\_tmp\_tables 106349 27 28 21 21Delayed\_errors 0 0 0 0 0Delayed\_insert\_threads 0 0 0 0 0Delayed\_writes 0 0 0 0 0Flush\_commands 1 0 0 0 0Handler\_commit 0 0 0 0 0Handler\_delete 9933258 0 0 0 0Handler\_discover 0 0 0 0 0Handler\_prepare 0 0 0 0 0Handler\_read\_first 17017 4 5 0 0Handler\_read\_key 1996075471 749774 371223 306984 482515Handler\_read\_next 4711314584 6367215 11771546 11189425 9284705Handler\_read\_prev 17741214 0 0 0 49Handler\_read\_rnd 975309222 251810 137849 193116 181085Handler\_read\_rnd\_next 5576908550 1184208 362663 702245 1694545Handler\_rollback 0 0 0 0 0Handler\_savepoint 0 0 0 0 0Handler\_savepoint\_rollback 0 0 0 0 0Handler\_update 4958546 4 2 0 52Handler\_write 133956021 318 17390 26486 17745Innodb\_buffer\_pool\_pages\_data 508 0 0 0 0Innodb\_buffer\_pool\_pages\_dirty 0 0 0 0 0Innodb\_buffer\_pool\_pages\_flushed 1 0 0 0 0Innodb\_buffer\_pool\_pages\_free 0 0 0 0 0Innodb\_buffer\_pool\_pages\_misc 4 0 0 0 0Innodb\_buffer\_pool\_pages\_total 512 0 0 0 0Innodb\_buffer\_pool\_read\_ahead\_rnd 40 0 0 0 0Innodb\_buffer\_pool\_read\_ahead\_seq 0 0 0 0 0Innodb\_buffer\_pool\_read\_requests 39105 0 0 0 0Innodb\_buffer\_pool\_reads 638 0 0 0 0Innodb\_buffer\_pool\_wait\_free 0 0 0 0 0Innodb\_buffer\_pool\_write\_requests 1 0 0 0 0Innodb\_data\_fsyncs 7 0 0 0 0Innodb\_data\_pending\_fsyncs 0 0 0 0 0Innodb\_data\_pending\_reads 0 0 0 0 0Innodb\_data\_pending\_writes 0 0 0 0 0Innodb\_data\_read 18075648 0 0 0 0Innodb\_data\_reads 749 0 0 0 0Innodb\_data\_writes 7 0 0 0 0Innodb\_data\_written 35328 0 0 0 0Innodb\_dblwr\_pages\_written 1 0 0 0 0Innodb\_dblwr\_writes 1 0 0 0 0Innodb\_log\_waits 0 0 0 0 0Innodb\_log\_write\_requests 0 0 0 0 0Innodb\_log\_writes 2 0 0 0 0Innodb\_os\_log\_fsyncs 5 0 0 0 0Innodb\_os\_log\_pending\_fsyncs 0 0 0 0 0Innodb\_os\_log\_pending\_writes 0 0 0 0 0Innodb\_os\_log\_written 1024 0 0 0 0Innodb\_pages\_created 0 0 0 0 0Innodb\_page\_size 16384 0 0 0 0Innodb\_pages\_read 970 0 0 0 0Innodb\_pages\_written 1 0 0 0 0Innodb\_row\_lock\_current\_waits 0 0 0 0 0Innodb\_row\_lock\_time 0 0 0 0 0Innodb\_row\_lock\_time\_avg 0 0 0 0 0Innodb\_row\_lock\_time\_max 0 0 0 0 0Innodb\_row\_lock\_waits 0 0 0 0 0Innodb\_rows\_deleted 0 0 0 0 0Innodb\_rows\_inserted 0 0 0 0 0Innodb\_rows\_read 84 0 0 0 0Innodb\_rows\_updated 0 0 0 0 0Key\_blocks\_not\_flushed 1114 0 3 0 0Key\_blocks\_unused 1553092 0 -3 -3 0Key\_blocks\_used 1158393 0 0 0 0Key\_read\_requests 8584306387 3528381 2413625 2218747 2754284Key\_reads 1562990 0 0 3 0Key\_write\_requests 25326953 2 138 10 94Key\_writes 10194 0 0 0 0Last\_query\_cost 0 0 0 0 0Max\_used\_connections 52 0 0 0 0Not\_flushed\_delayed\_rows 0 0 0 0 0Opened\_tables 4802 0 0 0 0Open\_files 1917 0 2 0 1Open\_streams 0 0 0 0 0Open\_tables 1024 0 0 0 0Prepared\_stmt\_count 0 0 0 0 0Qcache\_free\_blocks 621 -103 -4 -3 0Qcache\_free\_memory 391715968 -13156128 -575592 -491032 -220672Qcache\_hits 569854 115 136 84 68Qcache\_inserts 288813 58 48 52 50Qcache\_lowmem\_prunes 203038 0 0 0 0Qcache\_not\_cached 10639 3 2 1 4Qcache\_queries\_in\_cache 1912 48 41 52 45Qcache\_total\_blocks 8901 104 79 104 92Queries 1293718 256 268 240 183Questions 1293718 256 268 240 183Rpl\_status 0 0 0 0 0Select\_full\_join 25379 10 9 6 4Select\_full\_range\_join 30 0 0 0 0Select\_range 42629 10 9 7 8Select\_range\_check 0 0 0 0 0Select\_scan 55529 11 4 8 20Slave\_open\_temp\_tables 0 0 0 0 0Slave\_retried\_transactions 0 0 0 0 0Slave\_running 0 0 0 0 0Slow\_launch\_threads 0 0 0 0 0Slow\_queries 336 0 1 0 0Sort\_merge\_passes 5541 3 1 0 2Sort\_range 20327 3 1 2 4Sort\_rows 2305797923 894557 344117 275728 782838Sort\_scan 125090 31 27 23 22Table\_locks\_immediate 578353 132 106 103 106Table\_locks\_waited 172 0 0 0 0Tc\_log\_max\_pages\_used 0 0 0 0 0Tc\_log\_page\_size 0 0 0 0 0Tc\_log\_page\_waits 0 0 0 0 0Threads\_cached 13 1 2 -1 -3Threads\_connected 14 -3 -6 1 3Threads\_created 730 0 0 0 0Threads\_running 4 -1 -1 1 3Uptime 25417 5 5 5 5

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [May 12, 2010, 6:45am UTC](https://forums.percona.com/t/what-will-be-the-best-way-to-load-tables-in-memory-to-speed-up-access/2085/8 "2010-05-12T06:45:52Z")

</div>

Great! Now this is much easier to understand. By the way, you are getting free consulting here )

In vmstat I see a lot of runnable and blocked processes. In Linux, processses waiting for IO are counted towards the loadavg. Look at the wa column (IO wait) – that is high. At the same time there is hardly any IO. This looks like a system with slow disks. How many processors / cores do you have?

In iostat I see very bad IO performance. The await for sda2, sda7, and sda8 is nearly a tenth of a second sometimes. The svctm is also very long. On server-class hard drives, I want to see that in the 3-5ms range. At the same time the queue is not long. Unfortunately iostat lumps reads and writes together so I can’t see whether this is due to reads or writes, but I can guess based on the volume of reads and writes. It looks like writes are very slow. So it looks like maybe you don’t have a system with a decent RAID controller and battery-backup unit on the write cache, and you probably have some 7200 RPM SATA disks or something like that.

The server doesn’t seem to be doing all that much work, based on the SHOW STATUS output. There are very few Com\_ operations per second, and it looks like the Handler\_ values are not that high either. However, the operations that are happening are causing a lot of Handler\_ operations. You might need to optimize queries. It looks like the queries are creating temp tables, some of them on disk, which will be very bad on your disks.

Select\_full\_join should be ZERO. Those queries are probably your problem.

It is possible that something else is happening on this server that’s consuming all your resources. Check that.

In conclusion, I think you have a low-powered server with slow disks, and you are running very badly optimized queries on it. You need to log all queries with the slow query log (use a Percona version of the server so you can get more information about them) and use mk-query-digest to understand which are causing the most load on the server. And consider more powerful hardware, if you can.