Hallo zusammen,
wir haben bei uns eine MySQL DB laufen, die Daten über 11 Stunden pro Tag sammelt.
Es werden durchschnittlich 1 Mio. bis 1.5 Mio. Updates in diesem Zeitraum in die DB geschrieben.
Beim schreiben der Updates bekommen nach ca. 5 Stunden den folgenden Fehler:
Out of memory (Needed 8164 bytes)DB-Error
Hier der Auszug der my.cnf
# The MySQL server
[mysqld]
default-storage-engine=innodb
#innodb
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 64M
thread_cache_size = 6M
query_cache_size = 256M
query_cache_limit = 4G
query_cache_type = 1
max_prepared_stmt_count=65132
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
wait_timeout=28800
interactive_timeout=28800
tmp_table_size=32M
max_heap_table_size=32M
table_open_cache=524288
thread_cache_size=16384
max_connections = 4
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /xxx/xxx/mysql/data/
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_log_group_home_dir = /xxx/xxx/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
Ein Lauf von mysqltuner.pl brachte die folgenden Ergebnisse:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.46
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 756M (Tables: 1)
[!!] Total fragmented tables: 1
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 9m 50s (20 q [0.000 qps], 7 conn, TX: 10K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 2.7G global + 132.3M per thread (4 max threads)
[OK] Maximum possible memory usage: 3.2G (40% of installed RAM)
[OK] Slow queries: 5% (1/20)
[OK] Highest usage of available connections: 25% (1/4)
[OK] Key buffer size / total MyISAM indexes: 384.0M/96.0K
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 9 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 85% (1 created / 7 connections)
[OK] Table cache hit rate: 56% (9 open / 16 opened)
[OK] Open file limit used: 0% (16/65K)
[OK] Table locks acquired immediately: 100% (21 immediate / 21 locks)
[!!] Connections aborted: 14%
[OK] InnoDB data size / buffer pool: 756.0M/2.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_limit (> 3G, or use smaller result sets)
Selbst wenn query_cache_limit auf 4G erhöht wird, schlägt mysqltuner.pl query_cache_limit (> 3G, or use smaller result sets) vor.
Die Query geht nach 0815 vor. INSERT INTO MeineDB......
PRIMARY KEY auf timedate und symbol.
HW: SUN Sparc 4Core 8GB RAM
BS: sun-solaris 2.10
MySQL: V 5.1.46
Bin für jeden Tipp Dankbar!
MfG