[RndTbl] mysql query_cache_size

John Lange john.lange at open-it.ca
Wed Jun 13 02:40:28 CDT 2007


Further to Sean's excellent presentation this evening; I had a look at
the default config for mysql.

mysql source code comes with a directory called "support-files" that
(among other things) contains a set of recommended default config files.

my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

grepping for query_cache_size reveals that my-huge.cnf,
my-innodb-heavy-4G.cnf, and my-large.cnf have the query_cache activated
by default.

Keep in mind these configs were originally created back in the day when
1G of memory was considered huge, I think most typical web servers can
run with my-huge.cnf and a single user system (like a laptop) can use
my-medium.cnf.

The settings in my-huge.cnf look pretty reasonable and agree with the
settings Sean was recommending.

I mentioned that mysql creates .bin files when doing replication which
is turned ON by default so the setting you should comment out is
"log-bin" unless you are doing replication. This is especially true if
you are doing lots of updates to your data.

For example, I have a client that replicates a large access to database
to mysql several times per day. The entire database is dropped and
reloaded each time and this ended up exhausting many gigs of disk space
just in mysql .bin files (and imagine how inefficient creating them
was!).

Just for interest sake, I checked some SUSE installs and the
my-medium.cnf was the default so on a production web server this should
be changed. The my-huge.cnf is included in the install but is not the
default.

Regards,

John




More information about the Roundtable mailing list