Dotaz: MySQL casem chce vic a vic RAM a pak i SWAP

[basss]

Hezký den

Mám Slackware 13.37 s DB MySQL 5.5.13 na 2xCPU XEON 16-jader ,38GB RAM ,500GB RAID-10

Po zpustení databaze se zavede do ram 8GB a časem začne bobtnat az na 40GB nevim co to dela. Když jsem zkousel tuning-primer.sh , mysqltuner.pl tak to jen doporucu navisovat hodnoty coz by chtelo navišovat RAM Chtel bych aby db pustil a rozknit v ram byl +-2GB ale neustale navisovani se my nelibi protože pak při větším množství dotazu to začne swapowap a je to pomale naroste load average z beznych 6 na nejakych az 200 a system je nepomalejsi az do doby restartu mysql nekdy i 1-2h nez se to z toho stavu dostane .

V db pozivam MyISAM a trvale je pripojeno na db az 200 uzivatelu .

Prosim jestli nekoho napada proc to casem začne brat vice ram a pak swap budu rad za kazdou radu. (rady typu dej to do googlu nechci a super rada najdi co to dela a to odstran)

 

my.cnf

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3309
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3309
socket        = /tmp/mysql.sock
#skip-external-locking
default-storage-engine = MyISAM
table_cache = 16392
max_allowed_packet = 1M
binlog_cache_size = 2M

ft_min_word_len = 4

query_cache_limit = 1M

## Pre Thread buffer
preload_buffer_size = 32K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size =  256K
myisam_sort_buffer_size = 256K
join_buffer_size = 128K
thread_stack = 192K
## Pre Thread buffer

## Global Buffers
key_buffer_size = 4G
query_cache_size =  1M
max_heap_table_size = 16M
tmp_table_size = 16M
## Global Buffers

## Write operations
bulk_insert_buffer_size = 4M
concurrent_insert = 2
## Write operations

myisam_max_sort_file_size = 10M

skip-name-resolve
skip-grant-tables

max_connections = 512 # pocet pripojeni po zviseni pridat ram na server
wait_timeout = 600

#skip-innodb

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
thread_cache_size = 111
# bind-address = 192.168.1.222


# slow query logovani /log/
long_query_time = 5
log_slow_queries = /log/mysql_slow_queries.log


ssl = 1
ssl-key = /etc/mysql/ssl/server-key.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-ca = /etc/mysql/ssl/ca-cert.pem


# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted

server-id = 2
auto_increment_increment = 2
auto_increment_offset = 2

log-bin=master2-bin

report-password = replikace
report-host = 10.0.0.10
report-user = replikace
report-port = 3309
replicate-do-db = data
slave_compressed_protocol
master-retry-count = 86400

skip-name-resolve

[mysqldump]
quick
max_allowed_packet = 48M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M


[myisamchk]
key_buffer = 2000M
sort_buffer_size = 1500M
read_buffer = 128M
write_buffer = 128M

 

***************************** tuning-primer.sh *****************************************

 

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.5.13-log x86_64

Uptime = 10 days 23 hrs 57 min 35 sec
Avg. qps = 23
Total Questions = 22049085
Threads Connected = 147

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5.000000 sec.
You have 123 out of 22049108 that take longer than 5.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.5/en/purge-master-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 111
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 512
Current threads_connected = 147
Historic max_used_connections = 157
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 128 M
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 : 4.59 G
Configured Max Per-thread Buffers : 1.46 G
Configured Max Global Buffers : 4.14 G
Configured Max Memory Limit : 5.61 G
Physical Memory : 31.41 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 3.00 G
Current key_buffer_size = 4.00 G
Key cache miss rate is 1 : 406
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 1 M
Current query_cache_used = 991 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 96.83 %
Current query_cache_min_res_unit = 4 K
However, 823827 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 33306 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 = 16392 tables
Current table_definition_cache = 400 tables
You have a total of 4365 tables
You have 4427 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 13969984 temp tables, 31% 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 = 128 K
Current table scan ratio = 688 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 11908
Your table locking seems to be fine

 

********************************* ./mysqltuner.pl **************************************

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.13-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 4322)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 2

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 0h 17m 16s (22M q [23.219 qps], 2K conn, TX: 41B, RX: 1B)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 4.2G global + 960.0K per thread (512 max threads)
[OK] Maximum possible memory usage: 4.6G (14% of installed RAM)
[OK] Slow queries: 0% (126/22M)
[OK] Highest usage of available connections: 30% (157/512)
[OK] Key buffer size / total MyISAM indexes: 4.0G/3.0G
[OK] Key buffer hit rate: 99.8% (188M cached / 466K reads)
[!!] Query cache efficiency: 1.1% (223K cached / 20M selects)
[!!] Query cache prunes per day: 74960
[!!] Sorts requiring temporary tables: 11% (333K temp sorts / 2M sorts)
[!!] Temporary tables created on disk: 31% (6M on disk / 20M total)
[OK] Thread cache hit rate: 93% (157 created / 2K connections)
[!!] Table cache hit rate: 13% (4K open / 32K opened)
[OK] Open file limit used: 26% (8K/33K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
query_cache_size (> 1M)
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 16392)

Odpovědi

[Heron]

Těžko říct v čem to bude, ale určitě není problém v konfiguraci. Je to nakonfigurováno hodně nízko (až moc nízko, cache se příliš neuplatňují a zbytečně mnoho temporary tabulek je na disku) a oba nástroje to potvrzují:

Configured Max Memory Limit : 5.61 G
Physical Memory : 31.41 G

[OK] Maximum possible memory usage: 4.6G (14% of installed RAM)

Tedy pokud tu paměť opravdu žere MySQLd, tak je problém v nějakém podivném memory leaku.

[Heron]

skip-grant-tables

Tohle máte na produkčním serveru?

[Šangala]

Jak napsal Heron, chtělo by to možná navýšit cache,možná vypnout InnoDB skip-innodb, velmi zvláštní je skip-grant-tables, ale jinak tam nic zvláštního nevidím, opravdu to žere mysqld?

[okbob]

Tohle vypadá na memory leak v MySQL a tedy bug v MySQL

Ověřte si, že používáte aktuální verzi MySQL, případně můžete reportovat bug do Oracle.

Pokud je za tím skutečně memory leak, tak je bohužel jediné řešení - jednou za čas otočit MySQL.

[ET]

Pokud je za tím skutečně memory leak, tak je bohužel jediné řešení - jednou za čas otočit MySQL.

wtf ??? co takhle upgrade/downgrade na jinou verzi ?

[basss]

Zkoušel jsem vše jit na posledni verzi 5.5.20 a ta to dela taky.

Problem je v procedurach kdyz se zpoušti tak berou ram a i kdyz vsechny ukončím korektně tak zabrana ram neni uvolněna. A čim je větší procedura tim vic ram to bere.

[okbob]

A neni neco spatne v tech procedurach?

Ne, ze by implementace SP v MySQL byla Buhvico, ale tohle se obycejne nedeje.

[Šangala]

Jestli to opravdu papká proces mysql, tak to je dost divné.
To musí tedy bobnat s každým použitím procedury, nevznikají tam nějaké obludné temporary table, to při 200 připojení se může, slušně násobit (temporary tabuly je třeba DROP-nout, jinak zaniknou až při ukončení spojení, což od způsobu připojení mohou být sekundy nebo hodiny).

[Josef Kufner]

Zkusil bych na to pustit nějaké zátěžové testy a sledovat růst spotřeby paměti. Trápit vždy jen jednu věc a jakmile paměť začne mizet, je to ono.

Pokud by se to povedlo lokalizovat, tak buď najdeš chybu u sebe, nebo pošli bugreport a nějak to přepiš.