mysqld - жадно кушает ресурсы

[rusty_j4ck]

Ос - 5.5release

Обновил mysql5027 до
mysql-server-5.0.67_1 Multithreaded SQL database (server)
Стал кушать ресурсы нещадно - постоянно в топе 15-20%цпу на холостом ходе (mytop пустой).

В чем может быть причина?
Есть способ оптимизировать/усмирить демона?

[Хостинг HostFood.ru]

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

[hizel]

Код: Выделить всё

mysqladmin processlist

Код: Выделить всё

mysqladmin status

тоже все пучком?

[rusty_j4ck]

processlist- практически всегда пустой

Код: Выделить всё

+------+------+-----------+----+---------+------+-------+------------------+
| Id   | User | Host      | db | Command | Time | State | Info             |
+------+------+-----------+----+---------+------+-------+------------------+
| 1103 | root | localhost |    | Query   | 0    |       | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+

status-

Код: Выделить всё

Uptime: 3896  Threads: 2  Questions: 35454  Slow queries: 4  Opens: 461  Flush tables: 1  Open tables: 256  Queries per second avg: 9.100

[zingel]

Код: Выделить всё

mysqladmin ext status

[rusty_j4ck]

top

Код: Выделить всё

  PID USERNAME      PRI NICE   SIZE    RES STATE  C   TIME   WCPU    CPU COMMAND
83319 mysql          20    0   334M 90884K kserel 0  74:59 15.53% 15.53% mysqld

status

Код: Выделить всё

+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 3          |
| Aborted_connects                  | 33         |
| Binlog_cache_disk_use             | 0          |
| Binlog_cache_use                  | 0          |
| Bytes_received                    | 52791292   |
| Bytes_sent                        | 2737846497 |
| Com_admin_commands                | 2155       |
| Com_alter_db                      | 0          |
| Com_alter_table                   | 0          |
| Com_analyze                       | 0          |
| Com_backup_table                  | 0          |
| Com_begin                         | 0          |
| Com_call_procedure                | 0          |
| Com_change_db                     | 14545      |
| Com_change_master                 | 0          |
| Com_check                         | 0          |
| Com_checksum                      | 0          |
| Com_commit                        | 0          |
| Com_create_db                     | 0          |
| Com_create_function               | 0          |
| Com_create_index                  | 0          |
| Com_create_table                  | 1          |
| Com_create_user                   | 0          |
| Com_dealloc_sql                   | 0          |
| Com_delete                        | 538        |
| Com_delete_multi                  | 0          |
| Com_do                            | 0          |
| Com_drop_db                       | 0          |
| Com_drop_function                 | 0          |
| Com_drop_index                    | 0          |
| Com_drop_table                    | 1          |
| Com_drop_user                     | 0          |
| Com_execute_sql                   | 0          |
| Com_flush                         | 0          |
| Com_grant                         | 0          |
| Com_ha_close                      | 0          |
| Com_ha_open                       | 0          |
| Com_ha_read                       | 0          |
| Com_help                          | 0          |
| Com_insert                        | 2501       |
| Com_insert_select                 | 1          |
| Com_kill                          | 0          |
| Com_load                          | 0          |
| Com_load_master_data              | 0          |
| Com_load_master_table             | 0          |
| Com_lock_tables                   | 119        |
| Com_optimize                      | 0          |
| Com_preload_keys                  | 0          |
| Com_prepare_sql                   | 0          |
| Com_purge                         | 0          |
| Com_purge_before_date             | 0          |
| Com_rename_table                  | 0          |
| Com_repair                        | 0          |
| Com_replace                       | 7          |
| Com_replace_select                | 0          |
| Com_reset                         | 0          |
| Com_restore_table                 | 0          |
| Com_revoke                        | 0          |
| Com_revoke_all                    | 0          |
| Com_rollback                      | 0          |
| Com_savepoint                     | 0          |
| Com_select                        | 120393     |
| Com_set_option                    | 35798      |
| Com_show_binlog_events            | 0          |
| Com_show_binlogs                  | 0          |
| Com_show_charsets                 | 0          |
| Com_show_collations               | 0          |
| Com_show_column_types             | 0          |
| Com_show_create_db                | 0          |
| Com_show_create_table             | 4400       |
| Com_show_databases                | 2          |
| Com_show_errors                   | 0          |
| Com_show_fields                   | 4451       |
| Com_show_grants                   | 0          |
| Com_show_innodb_status            | 0          |
| Com_show_keys                     | 0          |
| Com_show_logs                     | 0          |
| Com_show_master_status            | 134        |
| Com_show_ndb_status               | 0          |
| Com_show_new_master               | 0          |
| Com_show_open_tables              | 0          |
| Com_show_privileges               | 0          |
| Com_show_processlist              | 135        |
| Com_show_slave_hosts              | 0          |
| Com_show_slave_status             | 134        |
| Com_show_status                   | 137        |
| Com_show_storage_engines          | 0          |
| Com_show_tables                   | 15143      |
| Com_show_triggers                 | 4400       |
| Com_show_variables                | 1          |
| Com_show_warnings                 | 0          |
| Com_slave_start                   | 0          |
| Com_slave_stop                    | 0          |
| Com_stmt_close                    | 0          |
| Com_stmt_execute                  | 0          |
| Com_stmt_fetch                    | 0          |
| Com_stmt_prepare                  | 0          |
| Com_stmt_reset                    | 0          |
| Com_stmt_send_long_data           | 0          |
| Com_truncate                      | 45         |
| Com_unlock_tables                 | 123        |
| Com_update                        | 3659       |
| Com_update_multi                  | 1          |
| Com_xa_commit                     | 0          |
| Com_xa_end                        | 0          |
| Com_xa_prepare                    | 0          |
| Com_xa_recover                    | 0          |
| Com_xa_rollback                   | 0          |
| Com_xa_start                      | 0          |
| Compression                       | OFF        |
| Connections                       | 18444      |
| Created_tmp_disk_tables           | 38362      |
| Created_tmp_files                 | 5847       |
| Created_tmp_tables                | 69947      |
| Delayed_errors                    | 0          |
| Delayed_insert_threads            | 0          |
| Delayed_writes                    | 39         |
| Flush_commands                    | 1          |
| Handler_commit                    | 98         |
| Handler_delete                    | 1145       |
| Handler_discover                  | 0          |
| Handler_prepare                   | 0          |
| Handler_read_first                | 10134      |
| Handler_read_key                  | 201815573  |
| Handler_read_next                 | 118280171  |
| Handler_read_prev                 | 6284666    |
| Handler_read_rnd                  | 1922137    |
| Handler_read_rnd_next             | 69163698   |
| Handler_rollback                  | 0          |
| Handler_savepoint                 | 0          |
| Handler_savepoint_rollback        | 0          |
| Handler_update                    | 2434314    |
| Handler_write                     | 61041488   |
| Innodb_buffer_pool_pages_data     | 163        |
| Innodb_buffer_pool_pages_dirty    | 0          |
| Innodb_buffer_pool_pages_flushed  | 1          |
| Innodb_buffer_pool_pages_free     | 349        |
| Innodb_buffer_pool_pages_latched  | 0          |
| Innodb_buffer_pool_pages_misc     | 0          |
| Innodb_buffer_pool_pages_total    | 512        |
| Innodb_buffer_pool_read_ahead_rnd | 4          |
| Innodb_buffer_pool_read_ahead_seq | 0          |
| Innodb_buffer_pool_read_requests  | 11121      |
| Innodb_buffer_pool_reads          | 129        |
| Innodb_buffer_pool_wait_free      | 0          |
| Innodb_buffer_pool_write_requests | 1          |
| Innodb_data_fsyncs                | 7          |
| Innodb_data_pending_fsyncs        | 0          |
| Innodb_data_pending_reads         | 0          |
| Innodb_data_pending_writes        | 0          |
| Innodb_data_read                  | 4853760    |
| Innodb_data_reads                 | 151        |
| Innodb_data_writes                | 7          |
| Innodb_data_written               | 35328      |
| Innodb_dblwr_pages_written        | 1          |
| Innodb_dblwr_writes               | 1          |
| Innodb_log_waits                  | 0          |
| Innodb_log_write_requests         | 0          |
| Innodb_log_writes                 | 2          |
| Innodb_os_log_fsyncs              | 5          |
| Innodb_os_log_pending_fsyncs      | 0          |
| Innodb_os_log_pending_writes      | 0          |
| Innodb_os_log_written             | 1024       |
| Innodb_page_size                  | 16384      |
| Innodb_pages_created              | 0          |
| Innodb_pages_read                 | 163        |
| Innodb_pages_written              | 1          |
| Innodb_row_lock_current_waits     | 0          |
| Innodb_row_lock_time              | 0          |
| Innodb_row_lock_time_avg          | 0          |
| Innodb_row_lock_time_max          | 0          |
| Innodb_row_lock_waits             | 0          |
| Innodb_rows_deleted               | 0          |
| Innodb_rows_inserted              | 0          |
| Innodb_rows_read                  | 4846       |
| Innodb_rows_updated               | 0          |
| Key_blocks_not_flushed            | 0          |
| Key_blocks_unused                 | 227756     |
| Key_blocks_used                   | 5741       |
| Key_read_requests                 | 418889660  |
| Key_reads                         | 200281     |
| Key_write_requests                | 10906706   |
| Key_writes                        | 3272       |
| Last_query_cost                   | 0.000000   |
| Max_used_connections              | 18         |
| Not_flushed_delayed_rows          | 0          |
| Open_files                        | 508        |
| Open_streams                      | 0          |
| Open_tables                       | 256        |
| Opened_tables                     | 16092      |
| Prepared_stmt_count               | 0          |
| Qcache_free_blocks                | 822        |
| Qcache_free_memory                | 3152808    |
| Qcache_hits                       | 209391     |
| Qcache_inserts                    | 104680     |
| Qcache_lowmem_prunes              | 85147      |
| Qcache_not_cached                 | 26656      |
| Qcache_queries_in_cache           | 2853       |
| Qcache_total_blocks               | 7869       |
| Questions                         | 438997     |
| Rpl_status                        | NULL       |
| Select_full_join                  | 89         |
| Select_full_range_join            | 2          |
| Select_range                      | 14618      |
| Select_range_check                | 0          |
| Select_scan                       | 50787      |
| Slave_open_temp_tables            | 0          |
| Slave_retried_transactions        | 0          |
| Slave_running                     | OFF        |
| Slow_launch_threads               | 0          |
| Slow_queries                      | 18         |
| Sort_merge_passes                 | 2921       |
| Sort_range                        | 22449      |
| Sort_rows                         | 1962146    |
| Sort_scan                         | 43134      |
| Table_locks_immediate             | 206262     |
| Table_locks_waited                | 13         |
| Tc_log_max_pages_used             | 0          |
| Tc_log_page_size                  | 0          |
| Tc_log_page_waits                 | 0          |
| Threads_cached                    | 7          |
| Threads_connected                 | 1          |
| Threads_created                   | 83         |
| Threads_running                   | 1          |
| Uptime                            | 82741      |
| Uptime_since_flush_status         | 82741      |
+-----------------------------------+------------+
Uptime: 82741  Threads: 1  Questions: 438998  Slow queries: 18  Opens: 16092  Flush tables: 1  Open tables: 256  Queries per second avg: 5.306

[zingel]

Код: Выделить всё

Slow queries: 18 

подключай лог медленных запросов и смотри, что лагает.

[rusty_j4ck]

лагают wordpressы
- но не настолько что бы так кушать цпу
- за день порядка 20 медленных запросов (по 10-15 секунд).

[zingel]

а что есть в my.cnf?

[Гость]

Могу оишбаться - но если компилял руками - то прикрутил ключи linux_tread и build_static - как-то не очень они в паре, попадал еще на 6 версии скуль в боекомплекте с постфиксом (5 доменов, 130 юзеров) тормозил жутко

[zingel]

вообще никак не в паре.

Как компилял?
Что в my.cnf?

[rusty_j4ck]

Собран из портов

Код: Выделить всё

./configure --localstatedir=/var/db/mysql --without-debug --without-readline --without-libedit --without-bench --without-extra-tools --with-libwrap --with-mysqlfs --with-low-memory --with-comment=FreeBSD port: mysql-server-5.0.67_1 --enable-thread-safe-client --enable-assembler --with-berkeley-db --with-named-thread-libs=-pthread --prefix=/usr/local --mandir=/usr/local/man --infodir=/usr/local/info/ --build=i386-portbld-freebsd5.5

my.cnf

Код: Выделить всё

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
default-character-set=cp1251
character-set-server=cp1251
collation-server=cp1251_general_ci
default-collation=cp1251_general_ci
port            = 3306
socket          = /tmp/mysql.sock
skip-name-resolve
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
thread_concurrency = 8
max_connections = 300

log-slow-queries=/var/log/mysql/slow.log
long_query_time=5

[warzoni]

Ос - 5.5release

Обновил mysql5027 до
mysql-server-5.0.67_1 Multithreaded SQL database (server)
Стал кушать ресурсы нещадно - постоянно в топе 15-20%цпу на холостом ходе (mytop пустой).

В чем может быть причина?
Есть способ оптимизировать/усмирить демона?

что за железо

[rusty_j4ck]

вкратце - 2 x Intel xeon dualcoreCPU, 2G RAM, 2x250 WD Raid-1

[warzoni]

вкратце - 2 x Intel xeon dualcoreCPU, 2G RAM, 2x250 WD Raid-1

Код: Выделить всё

mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -p[passwordroot] 

такое зделай подожди 10 минут и посматри что будет

и выдай top сюда псоле 10 минут после того как произведёшь првоерку.

[rusty_j4ck]

такое у меня каждый день ночью по крону

[warzoni]

такое у меня каждый день ночью по крону

тогда токо одно на уме крутитца,криво собран мускуль.

да накройняк покажи

Код: Выделить всё

uname -a