My (MySQL) database is experiencing intermittent lock timeouts. I do not have a root cause yet and we do not have any recommendations yet. Back in the day I would have to reindex my SQL Server and Oracle indexes because they needed it. No one ever talks about that and MySQL.
Any recommendations?
You should not need to reindex MySQL except in cases of corruption from a crash or sudden power-off. If you're having locking issues, you may want to look into converting your MyISAM tables (or at least the tables with the biggest lock contention issues) to InnoDB. It requires a bit more RAM, but it supports row-level locking instead of just table-level locking (among many other improvements).
You will also want to get on and run "show full processlist;" to make sure that it's not something deliberately explicitly locking the table, like some sort of backup.
As already noted, the storage engine selected is the main factor affecting MySQL's performance. Should you use MyISAM or InnoDB, that depends on your workload.
If in your case SELECTs are fast to do and there's not much write activity to DB, MyISAM is not bad at all. But if you have even moderate write activity and some long-running SELECTs, please be aware that MyISAM only supports table-level locks. During a slow SELECT all write activity will be queued and that can pretty soon lead to all kind of nasty problems.
InnoDB supports row-level locks, transactions and multiversioning, so even a heavily mixed read/write operations will not slow it down nearly as much as MyISAM.
Also make sure that whatever the storage engine is in use, it is properly tuned. For MyISAM key_buffer_size and table_cache are the most important values to tune, for InnoDB the first thing to adjust is innodb_buffer_pool_size.
But both of the storage engines have their own gotchas: for example, only MyISAM supports full-text indexing and InnoDB table space does not shrink, so in an actively updated table where there's lots of deletes/updates/inserts going on, you need to dump to table contents and suck them back in every now and then.
Locks timeout? MySQL is pretty well designed in this regard, especially if you use the InnoDB engine.
Indexes do not need to be re-indexed... but they need to be correctly indexed in the first place.
If we are talking about actual locks, as in deadlock / timeout, you should check for any suspicious LOCK TABLES command.
Sounds like issues with the index statistics.
If you are using MyISAM, sudden spikes of INSERTs would throw the statistics off in the eyes of the MySQL Query Optimizer. This would cause the MySQL Query Optimizer to take very bad guesses within the EXPLAIN plans of SELECT queries.
If you are using InnoDB, ANALYZE TABLE becomes completely useless.
As long as the table is reasonably small, ANALYZE TABLE is all you can really do for MyISAM. Rebuilding indexes may help InnoDB tables periodically.
As long as tables are not heavily written, index statistics will remain stable and query EXPLAIN plans will remain more consistent.
Just remember: once you have tons of INSERTs, UPDATEs, and DELETEs, all bets are off for trustworthy index statistics until the next rebuild or ANALYZE TABLE.
With regard to intermittent lock timeouts, here is something to consider: it is possible for SELECTs on InnoDB tables to timeout on Deadlocks.
SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three DBA Stack Exchange questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.
Please read these links carefully. I hope this helps.
http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes.
But I don't know if you ever need to do this. The indexes should be updated automatically.
I have had similar issues, and in the "old days" of Sql Server, I would change the queries to Select with NOLOCK().
I resolved this issue in MySQL stored procedures using:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
-- execute select command here such as (this works in stored procs as well):
SELECT LAST_NAME FROM TEMP WHERE NAME LIKE 'SMITH%' ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
-Clark Vera
