mysql долго выполняет Insert/update/delete

Есть БД (~12 гигов), в БД есть несколько табличек (innodb), в которых 2-3 миллиона записей, (есть одна на 16-18 миллионов, собственно логи доступа)

Сайт крутится под друпалом, поэтому оптимизировать конкретные запросы - сложновато будет.

Так вот, инсерты, апдейты и делиты в этой базе (почти всегда только в этих табличках) длятся от 5 (когда сервер mysql только (ре)стартанул) секунд и до 90.

Конфиг

grep -v '#' my.cnf



key_buffer_size         = 256M



max_allowed_packet      = 128M

net_buffer_length       = 32K

thread_stack            = 192K

thread_cache_size       = 64

thread_cache = 64

myisam-recover         = BACKUP

max_connections        = 100

thread_concurrency     = 16

query_cache_type        = on

query_cache_limit       = 128M

query_cache_size        = 4096M





wait_timeout = 120

interactive_timeout = 15

myisam_sort_buffer_size = 384M



join_buffer_size        = 16M

read_buffer_size        = 4M

sort_buffer_size        = 64M

long_query_time         = 10

table_cache             = 2048

max_heap_table_size     = 512M

tmp_table_size          = 512M



read_rnd_buffer_size    = 8M

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

log_bin                 = /var/log/mysql/mysql-bin.log

expire_logs_days        = 10

max_binlog_size         = 800M

binlog_cache_size       = 128K



innodb_data_home_dir = /var/lib/mysql/

innodb_data_file_path = /ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1536M

innodb_flush_log_at_trx_commit = 0

innodb_log_buffer_size = 16M

innodb_lock_wait_timeout = 70

innodb_thread_concurrency = 48





[mysqldump]

quick

quote-names

max_allowed_packet      = 16M



[mysql]



[isamchk]

key_buffer              = 160M



!includedir /etc/mysql/conf.d/

~/mysqltuner.pl 



 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering



-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.22-1~dotdeb.0-log

[OK] Operating on 64-bit architecture



-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 

[--] Data in MyISAM tables: 1G (Tables: 120)

[--] Data in InnoDB tables: 9G (Tables: 41)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 47



-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned



-------- Performance Metrics -------------------------------------------------

[--] Up for: 8h 5m 49s (73M q [2K qps], 99K conn, TX: 247B, RX: 9B)

[--] Reads / Writes: 83% / 17%

[--] Total buffers: 6.3G global + 92.2M per thread (100 max threads)

[!!] Maximum possible memory usage: 15.3G (97% of installed RAM)

[OK] Slow queries: 0% (449/73M)

[!!] Highest connection usage: 100%  (101/100)

[OK] Key buffer size / total MyISAM indexes: 256.0M/331.2M

[OK] Key buffer hit rate: 100.0% (5B cached / 7K reads)

[OK] Query cache efficiency: 89.6% (63M cached / 71M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 1% (21K temp sorts / 1M sorts)

[!!] Temporary tables created on disk: 41% (700K on disk / 1M total)

[OK] Thread cache hit rate: 99% (223 created / 99K connections)

[OK] Table cache hit rate: 57% (823 open / 1K opened)

[OK] Open file limit used: 14% (609/4K)

[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)

[!!] InnoDB data size / buffer pool: 9.7G/1.5G



-------- 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

    Reduce or eliminate persistent connections to reduce connection usage

    Temporary table size is already large - reduce result set size

    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 ***

    max_connections (> 100)

    wait_timeout (< 120)

    interactive_timeout (< 15)

    innodb_buffer_pool_size (>= 9G)

Выкрутить innodb_buffer_pool_size (>= 9G) не могу, ибо на сервере всего 16 гигабайт

service mysql status

/usr/bin/mysqladmin  Ver 8.42 Distrib 5.5.22, for debian-linux-gnu on x86_64

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Server version          5.5.22-1~dotdeb.0-log

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /var/run/mysqld/mysqld.sock

Uptime:                 8 hours 8 min 6 sec



Threads: 5  Questions: 73515963  Slow queries: 449  Opens: 1642  Flush tables: 1  Open tables: 823  Queries per second avg: 2510.276.

Селекты при этом стремятся к 0.0001

Помогите, братцы, а то я уже весь мозг проел себе :(

Если понадобиться, могу воложить variables

---------- Добавлено 11.04.2012 в 17:38 ----------

Все в одно сообщение не влезло, еще

sh ~/tuning-primer.sh 

MySQL Version 5.5.22-1~dotdeb.0-log x86_64



Uptime = 0 days 8 hrs 7 min 12 sec

Avg. qps = 2509

Total Questions = 73364914

Threads Connected = 6





SLOW QUERIES

The slow query log is enabled.             

Current long_query_time = 10.000000 sec.

You have 449 out of 73365106 that take longer than 10.000000 sec. to complete

Your long_query_time seems to be fine



BINARY UPDATE LOG

The binary update log is enabled 

Binlog sync is not enabled, you could loose binlog records during a server crash



WORKER THREADS

Current thread_cache_size = 64   

Current threads_cached = 58

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine



MAX CONNECTIONS

Current max_connections = 100      

Current threads_connected = 6

Historic max_used_connections = 101

The number of used connections is 101% of the configured maximum.

You should raise max_connections



INNODB STATUS

Current InnoDB index space = 1.12 G

Current InnoDB data space = 9.66 G

Current InnoDB buffer pool free = 0 %

Current innodb_buffer_pool_size = 1.50 G

Depending on how much space your innodb indexes take up it may be safe

to increase this value to up to 2 / 3 of total system memory



MEMORY USAGE

Max Memory Ever Allocated : 14.87 G

Configured Max Per-thread Buffers : 9.01 G

Configured Max Global Buffers : 5.77 G

Configured Max Memory Limit : 14.78 G

Physical Memory : 15.61 G      

Max memory limit exceeds 90% of physical memory



KEY BUFFER

Current MyISAM index space = 331 M

Current key_buffer_size = 256 M

Key cache miss rate is 1 : 725589

Key buffer free ratio = 78 %

Your key_buffer_size seems to be too high.

Perhaps you can use these resources elsewhere 



QUERY CACHE

Query cache is enabled                    

Current query_cache_size = 4.00 G

Current query_cache_used = 1.15 G

Current query_cache_limit = 128 M

Current Query cache Memory fill ratio = 28.84 %

Current query_cache_min_res_unit = 4 K

Query Cache is 24 % fragmented

Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory

If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.

MySQL won't cache query results that are larger than query_cache_limit in size



SORT OPERATIONS

Current sort_buffer_size = 64 M

Current read_rnd_buffer_size = 8 M

Sort buffer seems to be fine



JOINS

Current join_buffer_size = 16.00 M

You have had 0 queries where a join could not use an index properly

Your joins seem to be using indexes properly

join_buffer_size >= 4 M        

This is not advised                      



OPEN FILES LIMIT

Current open_files_limit = 4206 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine



TABLE CACHE

Current table_open_cache = 2048 tables 

Current table_definition_cache = 400 tables

You have a total of 202 tables

You have 823 open tables.

The table_cache value seems to be fine



TEMP TABLES

Current max_heap_table_size = 512 M

Current tmp_table_size = 512 M

Of 992049 temp tables, 41% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.



TABLE SCANS

Current read_buffer_size = 4 M 

Current table scan ratio = 37 : 1

read_buffer_size seems to be fine



TABLE LOCKING

Current Lock Wait ratio = 1 : 23992     

Your table locking seems to be fine