database - mysql suddenly ignoring table indices for a time -
on our mysql 5.1 server innodb table called central our web application. has around 150,000 rows , far many columns due bad implementation.
lately we've been running task updates table (insert or update) once every few seconds.
when task running, database periodically (every 1 - 3 hours) become slower process client read requests. during times slow_log points table culprit , request looks it's doing full table scan. eventually, within 5-20 minutes work out , go processing normally.
my assumption constant table updates throwing mysql's query optimizer statistics out of whack every once in while. don't know how prove or find solution it.
can following causes?
- too many secondary indices on table (there ~45, many unnecessary take time , care prune away)
- bad configuration parameters (query_cache on , innodb_buffer @ 5g)
- mysql 5.1 outdated , should upgraded
- the database server needs more ram/cpu handle load
i more happy provide additional information might request in comments.
can following causes?
too many secondary indices on table (there ~45, many unnecessary take time , care prune away)
yes, if running task witch lot of inserts/updates sure having lots of indexes in table overload sql engine. take in account every insert/update, sql has same insert each index. try keep indexes needed. , if amount of selects bigger insert statements.
bad configuration parameters (query_cache on , innodb_buffer @ 5g)
true, query_cache can or not, depends on ram amount dedicated (query_cache_size) , total ram amount server have. best assign small amount, check performance, increase amount, again check if goes better , on. other options key_buffer (important! if have many indexes) can decisive.
mysql 5.1 outdated , should upgraded
well, of course it's important upgrade , boosts performance wouldn't expect resolve problem.
the database server needs more ram/cpu handle load
we same here, priorize on db design , optimizing queries.
Comments
Post a Comment