Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I´ve recently moved my magento store from a shared host environment to a private VPS. The VPS is running CentOS, mysql, nginx and php-fpm.

My VPS has 4gb ram and 4 cpu cores. At the moment my website is quite small, but hopefully its number of visitors will increase.

Anyway here´s my problem. Mysqld is always running at 90% cpu. Even when there are no active processes, on startup, always. Not less not more. I have searched for this issue but I have not found anything that could be used to solve it.

If anyone would kindly look through my.cnf file it would be much appreciated to see if the problems is caused by faulty configuration, I am quite new to this server admin work.

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]
query_cache_limit=4M
query_cache_size=16M
query_cache_type=1
sort_buffer_size=1M
read_buffer_size=2M
read_rnd_buffer_size=1536K
join_buffer_size=2M
bulk_insert_buffer_size = 8M
max_allowed_packet = 64M
thread_stack = 192K


# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid_file                       = /var/run/mysqld/mysqld.pid
symbolic-links=0
skip-networking
skip-slave-start
log_warnings=0


# SAFETY #
skip_name_resolve
innodb                         = FORCE
innodb_strict_mode             = 0

# DATA STORAGE #
datadir                        = /var/lib/mysql
tmpdir                         = /tmp

# BINARY LOGGING #
#log_bin                        = /var/lib/mysql/mysql-bin
#expire_logs_days               = 6
#sync_binlog                    = 1

max_connections=20
max_user_connections=10

wait_timeout=400

key_buffer_size=32M # 256M for 4GB, 512M for 8GB
key_buffer=32M

table_open_cache=1024
table_definition_cache=1024

max_heap_table_size=32M
tmp_table_size=32M




innodb_additional_mem_pool_size=8M
innodb_buffer_pool_size=64M 
innodb_flush_log_at_trx_commit=2
innodb_support_xa = 0
innodb_flush_method=O_DIRECT
innodb_log_file_size=32M
innodb_log_files_in_group=2
innodb_use_sys_malloc=0
innodb_thread_concurrency=10




# Advanced
low_priority_updates=1
concurrent_insert=2
thread_cache_size=32 

Very appreciated! Much thanks!


Only shows the following:

202 | root | localhost | NULL | Query   |    0 | NULL  | show processlist

vmstat shows the following:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 3215392      0 172600    0    0    28    10    0   16 69  7  2  0 23

Mysql Show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
share|improve this question
1  
mysql -e 'show processlist' –  Korjavin Ivan Sep 25 '12 at 18:56
    
it seems that your CPU is busy, cpu.us = 69, while you don't have active "heavy" SQL queries. The us column shows the percent of CPU time spent in user mode. A UNIX process can execute in either user mode or system (kernel) mode. When in user mode, a process executes within its application code and does not require kernel resources to perform computations, manage memory, or set variables. So the problem is not in MySQL or it's config - the problem is within operation system itself. It looks you ran or running an application manually (outside of SQL) that consumes a lot of CPU resources. –  DmitrySemenov Sep 30 '12 at 5:23
    
try to stop mysql service and run sar -u 1 and report it here. If you don't have sar, you can install it: yum install sysstat –  DmitrySemenov Sep 30 '12 at 5:24

2 Answers 2

Insert lines for slow_query log in my.cnf file and review the slow-query.log

log-slow-queries = /var/log/slow-query.log

restart the mysqld service and execute

tail -f /var/log/slow-query.log

share|improve this answer
    
Or better yet, wait until tomorrow and run mysqldumpslow -s t /var/log/slow-query.log. This will add up all of the times of all the occurrences of the same query and sort by this value. 100x 2-second queries is usually worse than a single 100-second query. –  Ladadadada Sep 25 '12 at 21:50

Nothing problematic with your my.cnf config.

  1. Make sure your database & tables use innodb not myISAM. If you have myisam - then you may experience table locking which will increase CPU usage, as more queries will be put into queue until lock is released

  2. According to your config you have really small site (20 parallel connections - Max), so the traffic is not a problem.

  3. If you run a custom site - probably the problem could be in inefficient queries, then you need to setup sloq queries log and monitor execution time for slow queries behind 2 secs. If you have queries that run for 15+ seconds - you have problems with indexes.

You will need to check how many records you have in your tables and then either apply indexes or denormalize your DB

  1. Another problem is that you may got a VPS on "overcrowded" virtualized environment. It means you may have very ineffective IO or fights for Memory/CPU from other VMs. Run

    vmstat 1

and watch columns

  • CPU id *(idle time), the more iddle the better
  • swap & io

give us output of the following commands

  1. vmstat
  2. iotop
  3. login into mysql as root > show processlist
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.