High CPU usage in %user

[Marco Ramirez]

Hello,

Recently I got a new server and I have migrated everything from old to new (LAMP).
Without any modification, mysql have started to usage too much CPU and the queries are very slow.

The more strange thing it is that the CPU usage is for the %user and it is always about 30%:

# 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

I don't have memory problem, because the disk is not used (as you can see iowait is in 0%).

The only process is usaging the cpu is mysql:

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

So it should be the queries, but the queries was running fine in old server. Mysql version, apache versión, and debian version is the same, only have changed the hardware, now 24 cpus, 32GB RAM, better than previous.

In mysql the processlist:

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 <[e-mail]>
 >>  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.







Edited 1 time(s). Last edit at 05/14/2013 12:37PM by Marco Ramirez.