perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Issac Goldstand <mar...@beamartyr.net>
Subject Re: [OT] Re: Database transaction across multiple web requests
Date Mon, 03 Apr 2006 11:07:57 GMT
Right - that was the line I was trying to find earlier.  So much for my
theory about ignoring the LIMITs :-(

All I can think of to explain the speedup that people (including myself)
tend to see anyway is the indexes being cached in the key_buffer the
second+ times around.

  Issac

Jeff wrote:
> -------- 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