ISAM = Indexed Sequential Access Method and is essentially a flat file (for those DBAs who can remember, think Btrieve, or B-Tree). It's a very old technology - but don't let that put you off using it. Because it's a flat file (more on that later), it is not relational, and thus is not an RDBMS, and thus is more appropriate in some situations.
InnoDB is the full RDBMS like you are most likely familiar with. MyISAM can appear to be relational through another layer added on top that maintains your links, logic and referential integrity.
ISAM is brilliant if you have a LOT of records (like, 20 million), and the records are mostly stand-alone (i.e. you don't need to do lots of links to retrieve associated data). It relies VERY heavilly on indexes and if you don't have the right index, be prepared for very very long query times. Case in point: We had a Btrieve ISAM table with 20M+ records and to do a retrieve and filter data based on an accurate index was almost instant. Using the wrong index was literally 15 minutes.
InnoDB is great for if you have a lot of relational links. Table A references a field in Table B, which references Table C and D. InnoDB can fetch these records using all sorts of nice joining methods (hash joins, etc), whereas an ISAM database would have to run multiple sub-queries for every single row and match the records manually.
You will really have to do a course in databases if you want much more detail than that!
wait_timeout is the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection. interactive_timeout is the same, but for interactive sessions (mysql shell)
Setting a value too low may cause connections to drop unexpectedly, specifically if you are using persistent connections in your web application.
Setting a value too high may cause stale connections to remain open, preventing new access to the database.
IMO, for wait_timeout, you would want this value to be as low as possible without affecting availability and performance. You could start with 10 seconds and gradually increase it if you are seeing degraded performance.
For interactive_timeout, changing this value won't really increase or decrease performance of your application.
Best Answer
interactive_time
is for interactive sessions, whereaswait_timeout
is for non-interactive sessions.What's an interactive session? It's one with a human at the keyboard.
When your code connects to MySQL, runs a query and then spends 3 seconds processing that query before disconnecting, that's 3 seconds of the
wait_timeout
.When you use the
mysql
command line client to connect, run a command and spend 10 seconds reading the output, that's 10 seconds ofinteractive_timeout
. If you walk away and have lunch, that's 3600 seconds ofinteractive_timeout
.In both cases, when you or your code runs another query, the waiting time is reset back to 0.
You can see the values for all the current sessions by typing
show processlist
. The values in thesleep(5)
function is the number of seconds since that connection last did anything.