CREATE TABLE `assessment` (
  `id` varchar(22) COLLATE utf8_unicode_ci NOT NULL,
  `dateCreated` date NOT NULL,
  `dateCompleted` date NOT NULL,
  `type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'EX',
  `template` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
  `token` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `d_fk` int(22) unsigned NOT NULL DEFAULT '0',
  `vertical_fk` int(22) unsigned NOT NULL,
  `contact_fk` int(22) unsigned NOT NULL,
  `accountManager_fk` int(22) unsigned NOT NULL,
  `engineer_fk` int(22) unsigned NOT NULL,
  `region_fk` int(22) unsigned NOT NULL,
  `country_fk` int(22) unsigned NOT NULL,
  `status_fk` int(11) unsigned NOT NULL,
  `lastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `product` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'xxx',
  PRIMARY KEY (`id`),
  KEY `dateCreated` (`dateCreated`),
  KEY `cNameLong` (`cNameShort`,`cNameLong`),
  KEY `product` (`product`),
  KEY `dateCompleted` (`dateCompleted`),
  KEY `status` (`status_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

*************************** 1. row ***************************
           Name: assessment
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 81920
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2013-05-09 14:00:00
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

EXPLAIN EXTENDED SELECT datediff(now(),`dateCreated`) AS 'Created',
       datediff(now(),`dateCompleted`) AS 'Completed',
       a.id AS id,
       product AS 'Product',
       CONCAT(status,'(',status_fk,')') AS 'Status',
       cNameLong AS 'Customer',
       email AS 'Engineer',
       token AS Download,
       (SELECT COUNT(deviceId) FROM T1.chassis c WHERE c.assessmentId = a.id) AS COUNT,
       'files'
FROM assessment a
LEFT OUTER JOIN `chassis` c ON `a`.`id` = `assessmentId`
LEFT OUTER JOIN `status` ON status_fk=`status`.`id`
LEFT OUTER JOIN `engineer` ON engineer_fk=`engineer`.`id`
WHERE status_fk <=
    (SELECT `id`
     FROM status
     WHERE `status`='GENERATED')
GROUP BY `id`\G


*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE noticed after reading const tables
*************************** 2. row ***************************
           id: 3
  select_type: SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: ref
possible_keys: assessmentId2
          key: assessmentId2
      key_len: 62
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
3 rows in set, 2 warnings (0.00 sec)

SHOW WARNINGS;

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'T1.a.id' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select (to_days(now()) - to_days(`T1`.`a`.`dateCreated`)) AS `Created`,(to_days(now()) - to_days(`T1`.`a`.`dateCompleted`)) AS `Completed`,`T1`.`a`.`id` AS `id`,`T1`.`a`.`product` AS `Product`,concat(`T1`.`status`.`status`,'(',`T1`.`a`.`status_fk`,')') AS `Status`,`T1`.`a`.`cNameLong` AS `Customer`,`T1`.`employee`.`email` AS `Engineer`,`T1`.`a`.`token` AS `Download`,(/* select#2 */ select count(`T1`.`c`.`deviceID`) from `T1`.`chassis` `c` where (`T1`.`c`.`assessmentId` = `T1`.`a`.`id`)) AS `COUNT`,'files' AS `files` from `T1`.`assessment` `a` left join `T1`.`chassis` `c` on((`T1`.`a`.`id` = `T1`.`c`.`assessmentId`)) left join `T1`.`status` on((`T1`.`status`.`id` = `T1`.`a`.`status_fk`)) left join (`T1`.`employee`) on(((`T1`.`a`.`engineer_fk` = `T1`.`employee`.`id`) and (`T1`.`employee`.`role` = _utf8'Engineer'))) where 0 group by `T1`.`a`.`id`
2 rows in set (0.00 sec)

# sar 2 5
Linux 2.6.32-5-amd64 (hostname) 	05/14/2013 	_x86_64_	(24 CPU)

08:11:59 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
08:13:07 PM     all     33.11      0.00      0.77      0.00      0.00     66.12
08:13:09 PM     all     32.71      0.00      0.73      0.02      0.00     66.54
08:13:11 PM     all     34.51      0.00      0.63      0.00      0.00     64.86
08:13:13 PM     all     33.39      0.00      0.42      0.04      0.00     66.15
08:13:15 PM     all     33.29      0.00      0.44      0.00      0.00     66.26
Average:        all     33.39      0.00      0.60      0.01      0.00     66.00

Tasks: 424 total,   1 running, 422 sleeping,   0 stopped,   1 zombie
Cpu(s): 27.3%us,  0.4%sy,  0.0%ni, 72.3%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32979568k total, 27494980k used,  5484588k free,   932472k buffers
Swap:  7999992k total,     6792k used,  7993200k free, 19934596k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                  
31390 mysql     20   0 5542m 5.0g 7976 S  708 15.9   1833:52 mysqld                                                                                   
28063 www-data  20   0  261m  14m 5516 S    2  0.0   0:00.08 apache2                                                                                  
28077 root      20   0 19340 1656 1020 R    1  0.0   0:00.20 top                                                                                      
27948 www-data  20   0  245m  13m 5148 S    1  0.0   0:00.05 apache2                                                                                  
27976 www-data  20   0  245m  14m 5284 S    1  0.0   0:00.17 apache2                                                                                  
28032 www-data  20   0  248m  15m 5280 S    1  0.0   0:00.10 apache2

mysql> show full processlist;
+-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id    | User       | Host      | db         | Command | Time | State                | Info                                                                                                                                                                                                                                                                 |
+-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10161 | root       | localhost | artigoo_es | Query   |    0 | NULL                 | show full processlist                                                                                                                                                                                                                                                |
| 22594 | artigoo_es | localhost | artigoo_es | Query   |    4 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="adrid" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                      |
| 22595 | artigoo_es | localhost | artigoo_es | Query   |    3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="aduana" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                     |
| 22596 | artigoo_es | localhost | artigoo_es | Query   |    3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="advertencia" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                |
| 22597 | artigoo_es | localhost | artigoo_es | Query   |    3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="aefa" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                       |
| 22598 | artigoo_es | localhost | artigoo_es | Query   |    3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="ahorra" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                     |
| 22600 | artigoo_es | localhost | artigoo_es | Query   |    1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                        |
| 22604 | artigoo_es | localhost | artigoo_es | Query   |    2 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="cocina-casera" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                              |
| 22605 | artigoo_es | localhost | artigoo_es | Query   |    1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                        |
| 22606 | artigoo_es | localhost | artigoo_es | Query   |    1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="cocina-faci" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40                                                                                |
| 22609 | artigoo_es | localhost | artigoo_es | Query   |    2 | Copying to tmp table | select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cocina-facil" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 90, 10 |
+-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

That query explain:
mysql> explain SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40;
+----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                | key                      | key_len | ref                | rows  | Extra                                                     |
+----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | g     | index | PRIMARY,idx_idgoo_idstategoo | idx_idgoo_idstategoo     | 9       | NULL               | 44860 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t     | ref   | idx_tag_id_idcontenttype     | idx_tag_id_idcontenttype | 5       | artigoo_es.g.idgoo |     2 | Using where                                               |
+----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)



That query spent about 6 sec when the server are with high load, but only 0.72 sec when it is normal.


Not always is that query, I already have activated slow queries and i found lot of them. Always same status "Copying to tmp table".





By the way, my system memory usage:


# free
             total       used       free     shared    buffers     cached
Mem:      32979568   27272216    5707352          0     932860   19774576
-/+ buffers/cache:    6564780   26414788
Swap:      7999992       6788    7993204



As you can see, I have about 25GB free (about 5GB truly free and another 26GB in cache, which can be use if necesary).





Some other stats:





mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
130514 20:21:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 375146125, signal count 358559755
Mutex spin waits 0, rounds 133666397543, OS waits 226613066
RW-shared spins 30516517, OS waits 2475040; RW-excl spins 14667880, OS waits 696823
------------
TRANSACTIONS
------------
Trx id counter 0 24016085
Purge done for trx's n:o < 0 24016065 undo n:o < 0 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 24016079, not started, process no 31390, OS thread id 139647571896064
MySQL thread id 22814, query id 716143 localhost artigoo_es Table lock
select title, url, date(create_date) as date, (select sum(pageviews) from analytics where idgoo=goos.idgoo) as visits from goos where iduser=1625 and idstategoo=1 order by title
---TRANSACTION 0 24016065, not started, process no 31390, OS thread id 139642324195072
MySQL thread id 22813, query id 716129 localhost artigoo_es Table lock
update goos set metakeys="grafeno, grafito, matertial, futuro, l�mina", title="Grafeno el material del futuro", url="grafeno-material-futuro", update_date=now() where idgoo=45215 and iduser=13167
---TRANSACTION 0 24015879, not started, process no 31390, OS thread id 139642329331456
MySQL thread id 10161, query id 716148 localhost root
show engine innodb status
---TRANSACTION 0 24016084, ACTIVE 10 sec, process no 31390, OS thread id 139642327709440 starting index read, thread declared inside InnoDB 453
mysql tables in use 2, locked 0
MySQL thread id 22785, query id 716104 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="incidentes-river" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016083, ACTIVE 10 sec, process no 31390, OS thread id 139642325006080 sleeping before joining InnoDB queue
mysql tables in use 3, locked 0
MySQL thread id 22819, query id 716124 localhost artigoo_es Copying to tmp table
select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="mejillones-rellenos" and t.id=g.idgoo group by g.idgoo order by gr.quality desc
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016082, ACTIVE 10 sec, process no 31390, OS thread id 139647572166400 starting index read, thread declared inside InnoDB 440
mysql tables in use 2, locked 0
MySQL thread id 22780, query id 716098 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="ideas-recomendaciones" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016081, ACTIVE 10 sec, process no 31390, OS thread id 139642325411584 starting index read, thread declared inside InnoDB 159
mysql tables in use 2, locked 0
MySQL thread id 22781, query id 716094 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inaki-pinuel" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016080, ACTIVE 10 sec, process no 31390, OS thread id 139642317977344 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 22784, query id 716100 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="ideas-decorar" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016078, ACTIVE 10 sec, process no 31390, OS thread id 139642323519232 starting index read, thread declared inside InnoDB 73
mysql tables in use 3, locked 0
MySQL thread id 22811, query id 716127 localhost artigoo_es Copying to tmp table
select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cocino" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10
Trx read view will not see trx with id >= 0 24016082, sees < 0 24016069
---TRANSACTION 0 24016077, ACTIVE 10 sec, process no 31390, OS thread id 139647571760896 starting index read, thread declared inside InnoDB 110
mysql tables in use 3, locked 0
MySQL thread id 22818, query id 716125 localhost artigoo_es Copying to tmp table
select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="megadeth" and t.id=g.idgoo group by g.idgoo order by gr.quality desc
Trx read view will not see trx with id >= 0 24016082, sees < 0 24016069
---TRANSACTION 0 24016076, ACTIVE 10 sec, process no 31390, OS thread id 139642329736960 fetching rows, thread declared inside InnoDB 359
mysql tables in use 2, locked 0
MySQL thread id 22783, query id 716103 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inauguracion" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016075, ACTIVE 10 sec, process no 31390, OS thread id 139642328520448 sleeping before joining InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 22782, query id 716095 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inaki" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016074, ACTIVE 10 sec, process no 31390, OS thread id 139642318112512 fetching rows, thread declared inside InnoDB 221
mysql tables in use 2, locked 0
MySQL thread id 22778, query id 716097 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idealizar" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016073, ACTIVE 10 sec, process no 31390, OS thread id 139642321086208 sleeping before joining InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 22776, query id 716096 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idealistaes" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016072, ACTIVE 10 sec, process no 31390, OS thread id 139642319329024 sleeping before joining InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 22777, query id 716101 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idea-despedidas" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016071, ACTIVE 10 sec, process no 31390, OS thread id 139642318247680 sleeping before joining InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 22779, query id 716102 localhost artigoo_es Copying to tmp table
select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="impresionante" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10
Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069
---TRANSACTION 0 24016070, ACTIVE 10 sec, process no 31390, OS thread id 139642325546752 fetching rows, thread declared inside InnoDB 480
mysql tables in use 3, locked 0
MySQL thread id 22806, query id 716123 localhost artigoo_es Copying to tmp table
select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="inciensos" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10
Trx read view will not see trx with id >= 0 24016074, sees < 0 24016069
---TRANSACTION 0 24016069, ACTIVE 10 sec, process no 31390, OS thread id 139642326087424 waiting in InnoDB queue
mysql tables in use 3, locked 0
MySQL thread id 22812, query id 716126 localhost artigoo_es Copying to tmp table
select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cizanero" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10
Trx read view will not see trx with id >= 0 24016074, sees < 0 24016070
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
113312 OS file reads, 1308072 OS file writes, 69295 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.18 writes/s, 0.59 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 23, seg size 25,
2354 inserts, 2354 merged recs, 661 merges
Hash table size 8850487, node heap has 14181 buffer(s)
275921.14 hash searches/s, 6189.22 non-hash searches/s
---
LOG
---
Log sequence number 4 462958358
Log flushed up to   4 462958358
Last checkpoint at  4 462958358
0 pending log writes, 0 pending chkp writes
429136 log i/o's done, 0.27 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4754045874; in additional pool allocated 1045760
Dictionary memory allocated 720952
Buffer pool size   262144
Free buffers       52225
Database pages     195738
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 211942, created 148049, written 1625483
0.00 reads/s, 0.00 creates/s, 0.91 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 7 queries in queue
16 read views open inside InnoDB
Main thread process no. 31390, id 139642338264832, state: sleeping
Number of rows inserted 10078554, updated 402156, deleted 315, read 9406695475
0.23 inserts/s, 0.00 updates/s, 0.23 deletes/s, 286462.98 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)



And here you are the my.cnf:





#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#
# * Fine Tuning
#
#############################################
############### Innodb Tuning ###############
#############################################
innodb_buffer_pool_size	= 4096M 
#innodb_log_file_size	= 128M
innodb_log_buffer_size	= 4M
innodb_flush_log_at_trx_commit	= 2
innodb_thread_concurrency	= 8
innodb_flush_method	= O_DIRECT
innodb_file_per_table
transaction-isolation	= READ-COMMITTED

#############################################
############### MyIsam Tuning ###############
#############################################
key_buffer		= 512M #Aumentado de 16M a 512M el 2011/06/26
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
read_buffer_size	= 4M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover		= BACKUP
max_connections        	= 1000
table_cache            	= 512
table_definition_cache 	= 2048
open_files_limit	= 1536
#thread_concurrency     = 10
#
#Minimo de letras a buscar
ft_min_word_len		= 2
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log		= /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 10
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
#skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/



Finally the mysqltuner, there are some recommendations but because mysql wasn't up 24h, they are not very real.





 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 214)
[--] Data in InnoDB tables: 2G (Tables: 119)
[!!] Total fragmented tables: 46

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 40m 11s (718K q [11.299 qps], 23K conn, TX: 4B, RX: 56M)
[--] Reads / Writes: 15% / 85%
[--] Total buffers: 4.5G global + 6.5M per thread (1000 max threads)
[OK] Maximum possible memory usage: 10.9G (34% of installed RAM)
[OK] Slow queries: 1% (11K/718K)
[OK] Highest usage of available connections: 11% (110/1000)
[OK] Key buffer size / total MyISAM indexes: 512.0M/501.4M
[OK] Key buffer hit rate: 100.0% (970M cached / 436K reads)
[OK] Query cache efficiency: 45.8% (72K cached / 158K selects)
[!!] Query cache prunes per day: 18526
[OK] Sorts requiring temporary tables: 0% (178 temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 2% (832 on disk / 32K total)
[OK] Thread cache hit rate: 88% (2K created / 23K connections)
[!!] Table cache hit rate: 16% (512 open / 3K opened)
[OK] Open file limit used: 10% (509/5K)
[OK] Table locks acquired immediately: 99% (638K immediate / 639K locks)
[OK] InnoDB data size / buffer pool: 2.6G/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    table_cache (> 512)



I run optimize and repair in more important tables, so I think 46 fragmented table could unused dbs or because many tables are innodb.





In previous server, I had some error like this, but it was because there was different with system timezone and localtime file in etc. When I configured same in both, previous server become stable.


In this server this is not the problem, and I'm a little lost.





Any idea ? If you need some other info, please tell me.





Thanks and best regards.

mysql> SHOW PROFILES;
+----------+--------------+------------------------------+
| Query_ID | Duration     | Query                        |
+----------+--------------+------------------------------+
|        1 | 369.54719400 | drop database db_madeintouch | 
|        2 |   0.00004600 | SELECT DATABASE()            | 
+----------+--------------+------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| (initialization)     | 0.000001  | 
| checking permissions | 369.54705 | 
| Opening table        | 0.000103  | 
| System lock          | 0.000003  | 
| Table lock           | 0.000018  | 
| query end            | 0.000004  | 
| freeing items        | 0.000004  | 
| logging slow query   | 0.000002  | 
+----------------------+-----------+
8 rows in set (0.00 sec)

mysql> show engine innodb status\G
*************************** 1. row ***************************
Status: 
=====================================
130415 23:11:27 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 781, signal count 781
Mutex spin waits 0, rounds 8629, OS waits 231
RW-shared spins 379, OS waits 190; RW-excl spins 380, OS waits 342
------------
TRANSACTIONS
------------
Trx id counter 0 7599
Purge done for trx's n:o < 0 7430 undo n:o < 0 0
History list length 3
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 14133, OS thread id 140617364518656
MySQL thread id 16, query id 1305 localhost stephane checking permissions
drop database db_madeintouch
---TRANSACTION 0 0, not started, process no 14133, OS thread id 140617364383488
MySQL thread id 13, query id 1307 localhost stephane
show engine innodb status
---TRANSACTION 0 7597, COMMITTED IN MEMORY, process no 14133, OS thread id 140617364518656 dropping table
COMMITTING , undo log entries 16
MySQL thread id 16, query id 1305 localhost stephane checking permissions
drop database db_madeintouch
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
174 OS file reads, 3781 OS file writes, 2099 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, used cells 122, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 7801057
Log flushed up to   0 7798962
Last checkpoint at  0 7798962
1 pending log writes, 0 pending chkp writes
1535 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22136914; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       2
Database pages     509
Modified db pages  18
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 183, created 1444, written 6980
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 14133, id 140617334142720, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql> SHOW VARIABLES;


> +---------------------------------+--------------------------------------------------------------------+ | Variable_name                   | Value                             
> |
> +---------------------------------+--------------------------------------------------------------------+ | auto_increment_increment        | 1                                 
> |  | auto_increment_offset           | 1                              
> |  | automatic_sp_privileges         | ON                             
> |  | back_log                        | 50                             
> |  | basedir                         |
> /home/stephane/programs/mysql-5.0.45/install/                      | 
> | binlog_cache_size               | 32768                             
> |  | bulk_insert_buffer_size         | 8388608                        
> |  | character_set_client            | latin1                         
> |  | character_set_connection        | latin1                         
> |  | character_set_database          | latin1                         
> |  | character_set_filesystem        | binary                         
> |  | character_set_results           | latin1                         
> |  | character_set_server            | latin1                         
> |  | character_set_system            | utf8                           
> |  | character_sets_dir              |
> /home/stephane/programs/mysql-5.0.45/install/share/mysql/charsets/ | 
> | collation_connection            | latin1_swedish_ci                 
> |  | collation_database              | latin1_swedish_ci              
> |  | collation_server                | latin1_swedish_ci              
> |  | completion_type                 | 0                              
> |  | concurrent_insert               | 1                              
> |  | connect_timeout                 | 5                              
> |  | datadir                         |
> /home/stephane/programs/mysql/install/data/                        | 
> | date_format                     | %Y-%m-%d                          
> |  | datetime_format                 | %Y-%m-%d %H:%i:%s              
> |  | default_week_format             | 0                              
> |  | delay_key_write                 | ON                             
> |  | delayed_insert_limit            | 100                            
> |  | delayed_insert_timeout          | 300                            
> |  | delayed_queue_size              | 1000                           
> |  | div_precision_increment         | 4                              
> |  | engine_condition_pushdown       | OFF                            
> |  | expire_logs_days                | 0                              
> |  | flush                           | OFF                            
> |  | flush_time                      | 0                              
> |  | ft_boolean_syntax               | + -><()~*:""&|                 
> |  | ft_max_word_len                 | 84                             
> |  | ft_min_word_len                 | 4                              
> |  | ft_query_expansion_limit        | 20                             
> |  | ft_stopword_file                | (built-in)                     
> |  | group_concat_max_len            | 1024                           
> |  | have_archive                    | NO                             
> |  | have_bdb                        | NO                             
> |  | have_blackhole_engine           | NO                             
> |  | have_compress                   | YES                            
> |  | have_crypt                      | YES                            
> |  | have_csv                        | NO                             
> |  | have_dynamic_loading            | YES                            
> |  | have_example_engine             | NO                             
> |  | have_federated_engine           | NO                             
> |  | have_geometry                   | YES                            
> |  | have_innodb                     | YES                            
> |  | have_isam                       | NO                             
> |  | have_merge_engine               | YES                            
> |  | have_ndbcluster                 | NO                             
> |  | have_openssl                    | NO                             
> |  | have_ssl                        | NO                             
> |  | have_query_cache                | YES                            
> |  | have_raid                       | NO                             
> |  | have_rtree_keys                 | YES                            
> |  | have_symlink                    | YES                            
> |  | hostname                        | server1                        
> |  | init_connect                    |                                
> |  | init_file                       |                                
> |  | init_slave                      |                                
> |  | innodb_additional_mem_pool_size | 1048576                        
> |  | innodb_autoextend_increment     | 8                              
> |  | innodb_buffer_pool_awe_mem_mb   | 0                              
> |  | innodb_buffer_pool_size         | 8388608                        
> |  | innodb_checksums                | ON                             
> |  | innodb_commit_concurrency       | 0                              
> |  | innodb_concurrency_tickets      | 500                            
> |  | innodb_data_file_path           | ibdata1:10M:autoextend         
> |  | innodb_data_home_dir            |                                
> |  | innodb_doublewrite              | ON                             
> |  | innodb_fast_shutdown            | 1                              
> |  | innodb_file_io_threads          | 4                              
> |  | innodb_file_per_table           | OFF                            
> |  | innodb_flush_log_at_trx_commit  | 1                              
> |  | innodb_flush_method             |                                
> |  | innodb_force_recovery           | 0                              
> |  | innodb_lock_wait_timeout        | 50                             
> |  | innodb_locks_unsafe_for_binlog  | OFF                            
> |  | innodb_log_arch_dir             |                                
> |  | innodb_log_archive              | OFF                            
> |  | innodb_log_buffer_size          | 1048576                        
> |  | innodb_log_file_size            | 5242880                        
> |  | innodb_log_files_in_group       | 2                              
> |  | innodb_log_group_home_dir       | ./                             
> |  | innodb_max_dirty_pages_pct      | 90                             
> |  | innodb_max_purge_lag            | 0                              
> |  | innodb_mirrored_log_groups      | 1                              
> |  | innodb_open_files               | 300                            
> |  | innodb_rollback_on_timeout      | OFF                            
> |  | innodb_support_xa               | ON                             
> |  | innodb_sync_spin_loops          | 20                             
> |  | innodb_table_locks              | ON                             
> |  | innodb_thread_concurrency       | 8                              
> |  | innodb_thread_sleep_delay       | 10000                          
> |  | interactive_timeout             | 28800                          
> |  | join_buffer_size                | 131072                         
> |  | key_buffer_size                 | 16384                          
> |  | key_cache_age_threshold         | 300                            
> |  | key_cache_block_size            | 1024                           
> |  | key_cache_division_limit        | 100                            
> |  | language                        |
> /home/stephane/programs/mysql-5.0.45/install/share/mysql/english/  | 
> | large_files_support             | ON                                
> |  | large_page_size                 | 0                              
> |  | large_pages                     | OFF                            
> |  | lc_time_names                   | en_US                          
> |  | license                         | GPL                            
> |  | local_infile                    | ON                             
> |  | locked_in_memory                | OFF                            
> |  | log                             | ON                             
> |  | log_bin                         | OFF                            
> |  | log_bin_trust_function_creators | OFF                            
> |  | log_error                       |
> /home/stephane/programs/mysql/install/mysql.error.log              | 
> | log_queries_not_using_indexes   | OFF                               
> |  | log_slave_updates               | OFF                            
> |  | log_slow_queries                | ON                             
> |  | log_warnings                    | 1                              
> |  | long_query_time                 | 10                             
> |  | low_priority_updates            | OFF                            
> |  | lower_case_file_system          | OFF                            
> |  | lower_case_table_names          | 0                              
> |  | max_allowed_packet              | 1047552                        
> |  | max_binlog_cache_size           | 18446744073709551615           
> |  | max_binlog_size                 | 1073741824                     
> |  | max_connect_errors              | 10                             
> |  | max_connections                 | 100                            
> |  | max_delayed_threads             | 20                             
> |  | max_error_count                 | 64                             
> |  | max_heap_table_size             | 16777216                       
> |  | max_insert_delayed_threads      | 20                             
> |  | max_join_size                   | 18446744073709551615           
> |  | max_length_for_sort_data        | 1024                           
> |  | max_prepared_stmt_count         | 16382                          
> |  | max_relay_log_size              | 0                              
> |  | max_seeks_for_key               | 18446744073709551615           
> |  | max_sort_length                 | 1024                           
> |  | max_sp_recursion_depth          | 0                              
> |  | max_tmp_tables                  | 32                             
> |  | max_user_connections            | 0                              
> |  | max_write_lock_count            | 18446744073709551615           
> |  | multi_range_count               | 256                            
> |  | myisam_data_pointer_size        | 6                              
> |  | myisam_max_sort_file_size       | 9223372036854775807            
> |  | myisam_recover_options          | OFF                            
> |  | myisam_repair_threads           | 1                              
> |  | myisam_sort_buffer_size         | 8388608                        
> |  | myisam_stats_method             | nulls_unequal                  
> |  | net_buffer_length               | 2048                           
> |  | net_read_timeout                | 30                             
> |  | net_retry_count                 | 10                             
> |  | net_write_timeout               | 60                             
> |  | new                             | OFF                            
> |  | old_passwords                   | OFF                            
> |  | open_files_limit                | 1024                           
> |  | optimizer_prune_level           | 1                              
> |  | optimizer_search_depth          | 62                             
> |  | pid_file                        |
> /home/stephane/programs/mysql/install/data/server1.pid             | 
> | port                            | 3306                              
> |  | preload_buffer_size             | 32768                          
> |  | profiling                       | OFF                            
> |  | profiling_history_size          | 15                             
> |  | protocol_version                | 10                             
> |  | query_alloc_block_size          | 8192                           
> |  | query_cache_limit               | 1048576                        
> |  | query_cache_min_res_unit        | 4096                           
> |  | query_cache_size                | 0                              
> |  | query_cache_type                | ON                             
> |  | query_cache_wlock_invalidate    | OFF                            
> |  | query_prealloc_size             | 8192                           
> |  | range_alloc_block_size          | 2048                           
> |  | read_buffer_size                | 258048                         
> |  | read_only                       | OFF                            
> |  | read_rnd_buffer_size            | 258048                         
> |  | relay_log_purge                 | ON                             
> |  | relay_log_space_limit           | 0                              
> |  | rpl_recovery_rank               | 0                              
> |  | secure_auth                     | OFF                            
> |  | secure_file_priv                |                                
> |  | server_id                       | 1                              
> |  | skip_external_locking           | ON                             
> |  | skip_networking                 | OFF                            
> |  | skip_show_database              | OFF                            
> |  | slave_compressed_protocol       | OFF                            
> |  | slave_load_tmpdir               | /tmp/                          
> |  | slave_net_timeout               | 3600                           
> |  | slave_skip_errors               | OFF                            
> |  | slave_transaction_retries       | 10                             
> |  | slow_launch_time                | 2                              
> |  | socket                          | /tmp/mysql.sock                
> |  | sort_buffer_size                | 65528                          
> |  | sql_big_selects                 | ON                             
> |  | sql_mode                        |                                
> |  | sql_notes                       | ON                             
> |  | sql_warnings                    | OFF                            
> |  | ssl_ca                          |                                
> |  | ssl_capath                      |                                
> |  | ssl_cert                        |                                
> |  | ssl_cipher                      |                                
> |  | ssl_key                         |                                
> |  | storage_engine                  | MyISAM                         
> |  | sync_binlog                     | 0                              
> |  | sync_frm                        | ON                             
> |  | system_time_zone                | MSK                            
> |  | table_cache                     | 4                              
> |  | table_lock_wait_timeout         | 50                             
> |  | table_type                      | MyISAM                         
> |  | thread_cache_size               | 0                              
> |  | thread_stack                    | 131072                         
> |  | time_format                     | %H:%i:%s                       
> |  | time_zone                       | SYSTEM                         
> |  | timed_mutexes                   | OFF                            
> |  | tmp_table_size                  | 33554432                       
> |  | tmpdir                          | /tmp/                          
> |  | transaction_alloc_block_size    | 8192                           
> |  | transaction_prealloc_size       | 4096                           
> |  | tx_isolation                    | REPEATABLE-READ                
> |  | updatable_views_with_limit      | YES                            
> |  | version                         | 5.0.45-log                     
> |  | version_comment                 | Source distribution            
> |  | version_compile_machine         | x86_64                         
> |  | version_compile_os              | unknown-linux-gnu              
> |  | wait_timeout                    | 28800                          
> | 
> +---------------------------------+--------------------------------------------------------------------+ 225 rows in set (43.41 sec)