-------- Original Message --------
From: Perrin Harkins <[EMAIL PROTECTED]>
Subject: Re:Database transaction across multiple web requests
Date: Mon Apr 03 2006 03:55:54
How do you know it isn't just the operating system caching the disk
fetches? The docs certainly make it sound like a query cached with
LIMIT will not be useful when different LIMIT values are applied.
Having looked at the docs, at least for the v4 server, I have to agree
with Perrin. The docs don't specifically mention LIMIT, but they do say
that the SQL has to match, byte for byte. They give an example:
SELECT * from mytable
and
Select * from mytable
and say that the second statement will NOT use the first statements
cache because it does not match byte-for-byte (case is different). At a
guess, they probably just MD5 the SQL and add in some basic checks (for
MD5 collision) to identify the result set in the cache. Here is the URL
http://dev.mysql.com/doc/refman/4.1/en/query-cache-how.html
Saying that though, we have a highly interactive webapp, with about 100
active users per day from a user base of just under 1000. In the last 60
days, the qcache_hits shows 67 million hits. In our case, the underlying
data changes reasonably frequently. I believe that we would notice a
difference if the qcache was turned off! On a site with more static
underlying data, you would expect even better results.
As to the OS file cache - that works well too! If MySQL can't satisfy
the query from cache, it will query files that the OS has cached in
memory, meaning that even the fallback is better than disk access
speeds. We run about 20G of database per db server, and there is usually
about 2G of file cache memory in use.
For us, MySQL performance is blistering! Having worked for years with
Sybase, on big expensive hardware, my subjective feeling is that we get
about 10x the update and about 5x the read access on hardware that costs
1/10th the price, with 1/10th of the db tuning required. My Oracle
experience is less extensive, but I generally found Oracle was not much
faster than Sybase, but did have a bigger db admin overhead, and for
some reason seemed to always end up with bloated apps.
Regards
Jeff
