[nycbug-talk] Re: MySQL

[Gordon Smith]

<snip> 
> Again depends on schema - noteably what storage engine.  Different storage
> engines do different things.  If you're swapping - especially if you're
> using MyISAM, pull back on some buffer sizes.  The query cache is a fixed
> size, so if you set it to something, it won't grow.  Other things will,
> including the kernel's reliance on disk caches.
</snip>

If query concurrency is a problem and the app is implemented using the
MyISAM table type/engine, ***if you're prepared to do thorough testing using
non-production systems*** you might want to consider using the InnoDB
engine.  InnoDB is better than MyISAM when multiple queries request access
to the same tables.  MyISAM is fast, but seems to only allow one query to
hit a table at a given time; other queries have to wait for the table lock
to be released, so any performance advantage you may have had on a
query-by-query basis is lost.

Since InnoDB supports transactions and MyISAM does not, you'll need to make
certain that autocommit is enabled so that each executed SQL statement will
be committed.  Autocommit is supposed to be enabled for each connection by
default (e.g. unless MySQL is configured otherwise), but please test for
this to be certain with your MySQL version.  Autocommit should be
configurable in the MySQL configuration files independently of your
application.  Other options may need to be set differently as well.  

InnoDB gives you more configurability than MyISAM, so once you're using
InnoDB you'll have somewhat more direct control over MySQL configuration if
you want to invest the time in additional tuning of your app.

Hope this helps.

G