Привет LOR!
В общем ситуевина такая..
Есть MySQL версии 5.1.49 работающий под управленем Debian GNU/Linux версии 6.0.2. Который в свою очередь вогружен на DELL R710 с 6х300G SAS 15K RAID 6 CPU x2 E5506 2.13GHz и 32GB памяти.
Размер базы 256Gb, таблицы в основном InnoDB, есть и MyISAM но мало. Приложение которое работает с базой закрытое и модифицировать запросы мы не можем.
Нагрузка на базу в среднем 9000 запросов в минуту, в пике (который длится около часа-двух) 147000 запросов в минуту.
Рейтинг запросов примерно выглядит так SELECT 30%, UPDATE 40%, INSERT 30%.
В кеш запросов попадает где-то 20% запросов, я так понимаю из=за большого количества запросов содержащие многоэтажные JOINы.
Собственно, что происходит.. На момент запуска базы и приложения, все ок, но спустя пару суток база начинает жутко тупить на UPDATE и INSERT.
Ниже конфиг.. сразу говорю я не DBA, настраивал эмпирически + google.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#memlock
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /db/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
skip-name-resolve
skip-host-cache
connect_timeout = 30
bind-address = 0.0.0.0
old_passwords = 1
max_connections = 7000
table_cache = 3G
net_buffer_length = 100M
net_read_timeout = 30
net_write_timeout = 30
sort_buffer_size = 912M
join_buffer_size = 1G
thread_cache_size = 1100
thread_concurrency = 24
query_cache_size = 3G
query_cache_limit = 100M
query_cache_type = 1
query_prealloc_size = 65536
read_rnd_buffer_size = 724288
bulk_insert_buffer_size = 100M
query_alloc_block_size = 631072
tmp_table_size = 940M
max_tmp_tables = 1024
key_buffer_size = 8G
wait_timeout = 28800
max_allowed_packet = 800M
open_files_limit = 100000
read_buffer_size = 280M
read_rnd_buffer_size = 460M
binlog_format = row
sync_binlog = 1
max_write_lock_count = 1
#transaction-isolation = READ-COMMITTED
max_length_for_sort_data = 1G
bulk_insert_buffer_size = 456M
myisam_sort_buffer_size = 712M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 12
myisam_recover
myisam_data_pointer_size = 6
myisam-recover = BACKUP
character-set-server = cp1251
collation-server = cp1251_general_ci
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log = 0
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#log-slow-queries = /var/log/mysql/mysql-query-slow.log
#long_query_time = 2
#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 = /db/mysql-bin-log/mysql-bin.log
#expire_logs_days = 10
#max_binlog_size = 1024M
#binlog_do_db = billing
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
#skip-innodb
innodb
default-storage-engine = innodb
innodb_file_per_table
innodb_log_group_home_dir = /db/mysql-bin-log
innodb_additional_mem_pool_size = 260M
innodb_buffer_pool_size = 18G
innodb_file_io_threads = 10
innodb_thread_concurrency = 24
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_files_in_group = 10
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
quote-names
max_allowed_packet = 800M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[mysqld_safe]
log-error=/var/log/mysqld.log
[isamchk]
key_buffer =256M
sort_buffer =126M
read_buffer =128M
write_buffer =128M
[mysqlhotcopy]
interactive-timeout
[client]
#default-character-set=cp1251
#
# * 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/
Для желающих помочь с радостью предоставлю любую инфу для анализа.
Заранее спасибо.
