Главная страница » Почему mysql грузит процессор

Почему mysql грузит процессор

  • автор:

MySQL process goes over 100% of CPU usage

I’m experiencing some problems with my LAMP server. Recently everything became very slow, even though visitor count on my websites didn’t change to much. When I run top command, it says that MySQL process has taken over 150-200% of CPU. How’s that possible, I always thought that 100% is a maximum?

I’m running Ubuntu 9.04 server edition with 1,5 GB RAM.

Here is the output of MySQLTuner:

MySQLTuner output

The top command:

top output

What could be the cause of this problem? Can I make changes to my my.cnf to prevent server from hanging?

ZygD's user avatar

5 Answers 5

  1. Increase Key Buffer ( yours is currently 64MB, but total indexes are 116M, so put at least 128MB). Should help immediately.
  2. Run mysqloptimize and mysqlrepair on your tables
  3. Increase table cache/ decrease total number of tables to increase the table cache hit rate. Maybe you’ve got some unused or old tables which could be deleted.

Other recommended confugration options:

  • log_slow_queries = /var/log/mysql/mysql-slow.log
  • long_query_time = 4
  • log-queries-not-using-indexes

Check log file after some time.

Run top -H to see all the running threads and not just the overall process. Also, if you hit the 1 key while in top, it will show you the cpu usage for the individual CPUs/cores.

You have a processor which has more than one core, or you have multiple processors. If you have two cores and a process is using 100% of both cores, it will show as 200% in top.

Likewise, this is likely working as intended — nothing is wrong with your configuration. If you are experiencing frequent hangs, from what you posted, you may want to look into adding proper indexes to your tables (or optimizing your queries).

Mysql has multiple processes (threads) working independently, one, for example, is responsible for writing data from memory onto the disk. With multiple cores in the CPU (and/or multiple CPUs), more than one thread is working, and so it can run more than 100% of a single core — on a simplistic level, maybe 75% of each of two cores are running, giving 150%.

I’ve noticed a problem, not related to CPU. If you’re using apache and MySQL on the same server you can reach bad conditions (RAM) when your apache activity increase.

MySQLTunner tells you that using the 200 available connections (your max connection setting) you will fill the RAM. Let’s say you have limited apache to 150 process you’ll certainly won’t have enough RAM when MySQL and apache will try to use 150 connexions (as Apache as well is a good RAM eater).

So this is about RAM and you’re maybe not hit yet 🙂 The top commands show only 15 apache process (but you’re in load average 3/6/16 so that means the storm was 15 minute ago and is now in leaving).

About the CPU problem, to complement the good response of shakalandy, this may be because of one single query. It can be on a huge table, or doing a lot of re-index tasks, or using a lot of temporary file, an index missing (removed?), etc. The only way to detect it is activating the slow query log (maybe with a high thresold, like 8s). Then use the mysqlsla tool to analyse this slow query log, and run some explain on the identified queries.

Форум пользователей MySQL

Помогите пжл разобраться — не понятно почему mysqld грузит процессор 40-80%.При 7 подключении к примеру.
Сервер:Quad Xeon 2.13Ghz
RAM:2Gb (половина свободна)
Крутится сайт на Nginx в связки php-fpm
Вот вывод mytop:
Queries: 1.5M qps: 357 Slow: 465.0 Se/In/Up/De(%): 87/01/00/01
qps now: 264 Slow qps: 0.0 Threads: 3 ( 1/ 63) 83/01/01/01
Cache Hits: 1.1M Hits/s: 252.4 Hits now: 202.3 Ratio: 81.8% Ratio now: 92.8%
Key Efficiency: 93.8% Bps in/out: 39.7k/ 2.4M Now in/out: 44.3k/783.4k

Id User Host/IP DB Time Cmd Query or State
— —- ——- — —- — ———-
28589 root localhost site 0 Query show full processlist
4390 root localhost 18 Sleep
169 root localhost 3841 Sleep

При этом mysql 82 20 0 2577M 514M uwait 3 57:47 52.05% mysqld

Может я где то что то не так настроил?
Или как можно выяснить из-за чего?Из кривого сайта или настроек mysql..

#2 20.09.2012 19:20:57

Re: Mysql грузит процессор — но подключений практически нету

mytop вам пишет, что 300 запросов в секунду, но почему-то не показывает сами запросы. Попробуйте SHOW FULL PROCESSLIST несколько раз, подключившись к mysql под рутом.

#3 20.09.2012 20:32:07

Re: Mysql грузит процессор — но подключений практически нету

rgbeast написал:

mytop вам пишет, что 300 запросов в секунду, но почему-то не показывает сами запросы. Попробуйте SHOW FULL PROCESSLIST несколько раз, подключившись к mysql под рутом.

В предыдущем примере тоже было все под рутом.(а может mytop не показывать запросы — так как они закешированы? query_cache_size=256M — 56мб занято)

Вот с SHOW FULL PROCESSLIST

MySQL on localhost (5.5.20-log) up 0+00:28:50 [19:27:33]
Queries: 783.8k qps: 464 Slow: 0.0 Se/In/Up/De(%): 87/00/00/01
qps now: 702 Slow qps: 0.0 Threads: 17 ( 5/ 21) 86/01/00/01
Cache Hits: 614.3k Hits/s: 363.6 Hits now: 533.3 Ratio: 89.9% Ratio now: 88.2%
Key Efficiency: 92.2% Bps in/out: 49.5k/ 1.2M Now in/out: 76.2k/ 1.6M

Id User Host/IP DB Time Cmd Query or State
— —- ——- — —- — ———-
8829 root localhost site 0 Query show full processlist
13758 site localhost site 0 Query select * from video where id_lang=1 and st
13774 root localhost site 0 Query SELECT * FROM extension WHERE `type` = ‘mo
13776 root localhost site 0 Sleep
13759 site localhost site 1 Query select * from video where id_lang=1 and st
13762 root localhost site 1 Sleep
13763 site localhost site 1 Sleep
13768 root localhost site 1 Sleep
13769 site localhost site 1 Sleep
13770 root localhost site 1 Sleep
13771 site localhost site 1 Sleep
13772 root localhost site 1 Query SELECT * FROM (SELECT id.title AS title, i
13773 site localhost site 1 Sleep
13777 site localhost site 1 Sleep
13775 site localhost site 1 Sleep
9126 root localhost 347 Sleep
8491 root localhost 564 Sleep

Отредактированно SSD (20.09.2012 20:37:04)

#4 20.09.2012 20:44:50

Re: Mysql грузит процессор — но подключений практически нету

Бросается в глаза, что везде SELECT *
Может быть все поля требуются не всегда.

#5 16.02.2013 23:29:43

Re: Mysql грузит процессор — но подключений практически нету

Вынесли Mysql на отдельный сервер.

Все отлично — но иногда вывод странички тормозит — ожидание ответа от"сайта" — и при этом mysqld грузит процессорное время ..

Вот конфиг
innodb_flush_log_at_trx_commit=1
sync_binlog=1
key_buffer_size = 1G
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 64M
read_buffer_size = 12M
read_rnd_buffer_size = 24M
net_buffer_length = 2K
thread_stack = 128K
max_connections=1000
max_join_size=1000000
thread_cache_size=64
query_cache_size=512M
ft_min_word_len=2
log-slow-queries=/var/log/mysql/slow_queries
tmp_table_size=64M
max_heap_table_size=64M
table_cache=1024
————————
Версия
mysql-server-5.5.28 Multithreaded SQL database (server)
——————————-

MySQL on localhost (5.5.28-log) up 0+00:14:34 [21:00:53]
Queries: 349.5k qps: 409 Slow: 0.0 Se/In/Up/De(%): 90/00/01/01

Cache Hits: 267.1k Hits/s: 313.0 Hits now: 0.0 Ratio: 84.9% Ratio now: 0.0%
Key Efficiency: 99.6% Bps in/out: 36.8k/586.4k
Master: mysql-bin.000030/2684553 do: ign:

Id User Host/IP DB Time Cmd Query or State
— —- ——- — —- — ———-
4459 tvi3 172.16.0.1 tvi2012103 0 Query DELETE FROM whos_online WHERE time_last_click < ‘1361040295’
4460 tvi3 172.16.0.1 tvi2012103 0 Query INSERT INTO whos_online SET customer_id = ‘0’, full_name = ‘Guest’, session_id = ‘q5veap1m4i511on4qpgp48h9a3’, ip_address = ‘85.246.217.235’, b
4461 root localhost test 0 Query show full processlist
4462 tvi3 172.16.0.1 tvi2012103 0 Query SELECT news_id as uid, date_public as date, title, purple_new as isPriority, file_local as video FROM news WHERE in_list = 1 and status = 1 and
4453 tvi3 172.16.0.1 tvi2012103 1 Query SELECT n.*, DATE_FORMAT(n.date_public , ‘%H:%i’) AS public_time, DATE_FORMAT(n.date_public, ‘%e,%c,%w’) AS public_date, DATE_FORMAT(n.date_efir
4454 tvi3 172.16.0.1 tvi2012103 1 Query UPDATE news SET views = views + 1 WHERE news_id = ‘13686’
4455 tvi3 172.16.0.1 tvi2012103 1 Query UPDATE news SET video_views = video_views + 1 WHERE news_id = ‘13674’
4456 tvi3 172.16.0.1 tvi2012103 1 Query SELECT news_id, image, file_local FROM news WHERE news_id = ‘13689’
4457 tvi3 172.16.0.1 tvi2012103 1 Query SELECT alias, type_id, UNIX_TIMESTAMP(date_public) AS rss_date FROM news WHERE news_id = ‘12491’
4458 tvi3 172.16.0.1 tvi2012103 1 Query SELECT news_id FROM news WHERE alias = ‘mCSB_buttons.png’
2269 root localhost test 4 Sleep
319 replicato 172.16.0.1 820 Binlog Master has sent all binlog to slave; waiting for binlog to be updated

Может еще подскажите что не так или куда смотреть.

P.S."Стоит ли доверять mysqltuner?
Цитирую
——— General Statistics —————————————————
[—] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28-log
[OK] Operating on 64-bit architecture

——— Storage Engine Statistics ——————————————-
[—] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[—] Data in MyISAM tables: 2G (Tables: 219)
[—] Data in InnoDB tables: 11M (Tables: 7)
[—] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

——— Performance Metrics ————————————————-
[—] Up for: 17m 7s (434K q [423.396 qps], 5K conn, TX: 596M, RX: 39M)
[—] Reads / Writes: 89% / 11%
[—] Total buffers: 1.7G global + 100.2M per thread (1000 max threads)
[!!] Maximum possible memory usage: 99.6G (1106% of installed RAM)
[OK] Slow queries: 0% (0/434K)
[OK] Highest usage of available connections: 2% (20/1000)
[OK] Key buffer size / total MyISAM indexes: 1.0G/227.2M
[OK] Key buffer hit rate: 99.7% (24M cached / 82K reads)
[OK] Query cache efficiency: 84.9% (333K cached / 392K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[!!] Temporary tables created on disk: 41% (842 on disk / 2K total)
[OK] Thread cache hit rate: 99% (20 created / 5K connections)
[OK] Table cache hit rate: 76% (314 open / 410 opened)
[OK] Open file limit used: 4% (544/11K)
[OK] Table locks acquired immediately: 97% (81K immediate / 83K locks)
[OK] InnoDB data size / buffer pool: 11.1M/128.0M

——— Recommendations ——————————————————
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours — recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 64M)
max_heap_table_size (> 64M)

MySQL high CPU usage [closed]

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 10 years ago .

Recently my server CPU has been going very high.

CPU load averages 13.91 (1 min) 11.72 (5 mins) 8.01 (15 mins) and my site has only had a slight increase in traffic.

After running a top command, I saw MySQL was using 160% CPU!

Recently I’ve been optimizing tables and I’ve switched to persistent connections. Could this be causing MySQL to use high amounts of CPU?

3 Answers 3

First I’d say you probably want to turn off persistent connections as they almost always do more harm than good.

Secondly I’d say you want to double check your MySQL users, just to make sure it’s not possible for anyone to be connecting from a remote server. This is also a major security thing to check.

Thirdly I’d say you want to turn on the MySQL Slow Query Log to keep an eye on any queries that are taking a long time, and use that to make sure you don’t have any queries locking up key tables for too long.

Some other things you can check would be to run the following query while the CPU load is high:

This will show you any queries that are currently running or in the queue to run, what the query is and what it’s doing (this command will truncate the query if it’s too long, you can use SHOW FULL PROCESSLIST to see the full query text).

You’ll also want to keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size (if you’re using innodb tables) as all of these memory allocations can have an affect on query performance which can cause MySQL to eat up CPU.

You’ll also probably want to give the following a read over as they contain some good information.

It’s also a very good idea to use a profiler. Something you can turn on when you want that will show you what queries your application is running, if there’s duplicate queries, how long they’re taking, etc, etc. An example of something like this is one I’ve been working on called PHP Profiler but there are many out there. If you’re using a piece of software like Drupal, Joomla or WordPress you’ll want to ask around within the community as there’s probably modules available for them that allow you to get this information without needing to manually integrate anything.

MySQL сильно потребляет процессор

Доброго времени суток, уважаемые коллеги. Столкнулся я вот с такой проблемой: хотели увеличить скорость работы сайта, за счёт увеличения кэшей в озу и перехода на SSD переехали c: Intel(R) Xeon(R) CPU L5520 @ 2.27GHz 8 GB RAM 200GB HDD на сервер: AMD Opteron 3280 8 x 2,4 GHz 32 GB RAM 250GB SSD

А получили напротив жёсткие тормоза в часы пик. Сейчас пишу пост, всё летает, а по вечерам — тормоза. MySQL жрёт ЦПУ ядер 5, load average 12-15 иногда даже до 30 доходит. Наши характеристики в часы пик

75 посетителей онлайн, количество запросов в секунду почему-то после переезда выросло с 8 до 30, в то же время посещаемость сильно не изменилась.

Уже так и этак колдую с конфигом, c mysqltuner’ом и руками, немного ситуацию улучшил конечно, но проблема не уходит, скорость в часы пик ниже чем до переезда. Вот какой конфиг получился:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *