perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff <modp...@aquabolt.com>
Subject [OT] Re: Database transaction across multiple web requests
Date Mon, 03 Apr 2006 06:30:53 GMT
-------- Original Message  --------
From: Perrin Harkins <perrin@elem.com>
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

Mime
View raw message