Go Back   Rhinocerus > Newsgroup > Newsgroup comp.databases.mysql

Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old 09-04-2009, 08:22 PM
Brian Wakem
Guest
 
Posts: n/a
Default Avoid checking query cache

Is there anyway to tell mysql to not check the query cache on a per query
basis?

I have a batch of queries, perhaps 1 million ish, that will all be answered
very quickly. The overhead of checking the cache will probably be a not
insignificant portion of the query time, and I *KNOW* the answer isn't in
the cache so I would like to avoid looking there.

I can't turn the query cache off because it is used heavily for other
queries and SQL_NO_CACHE only stops writes, not reads to the cache.



--
Brian Wakem
Reply With Quote
Alt Today
Advertising
 
and become member of Rhinocerus
Standard Sponsored Links

  #2 (permalink)  
Old 09-04-2009, 09:22 PM
Captain Paralytic
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

On 4 Sep, 21:22, Brian Wakem <n...@email.com> wrote:
> The overhead of checking the cache will probably be a not
> insignificant portion of the query time

On what do you base this assertion?
Reply With Quote
  #3 (permalink)  
Old 09-04-2009, 10:57 PM
Dikkie Dik
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem wrote:
> Is there anyway to tell mysql to not check the query cache on a per query
> basis?
>
> I have a batch of queries, perhaps 1 million ish, that will all be answered
> very quickly. The overhead of checking the cache will probably be a not
> insignificant portion of the query time, and I *KNOW* the answer isn't in
> the cache so I would like to avoid looking there.
>
> I can't turn the query cache off because it is used heavily for other
> queries and SQL_NO_CACHE only stops writes, not reads to the cache.


What's the problem exactly? if your batch runs too slowly, try setting
autocommit off and postpone foreign-key checking. Or use compression if
you send all queries over a large or slow network.

You can configure the server not to use the query cache by setting its
size to zero, I think. I did not look it up, but you could check if you
can set that setting on a session basis with the SET command.

But it is the last factor I would suspect to make a difference.

By the way, as far as I know it is not the answer that is in the cache,
but the result of the query parsing. So if you fire the same query twice
with different field values in a WHERE clause, the server does not need
to reparse the entire query, thus saving potentially a lot of work.

And my gut feeling says that checking the cache takes less time then
submitting a "stop caching" keyword over a network...

Best regards.
Reply With Quote
  #4 (permalink)  
Old 09-04-2009, 11:38 PM
toby
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

On Sep 4, 6:57*pm, Dikkie Dik <dik...@nospam.org> wrote:
> Brian Wakem wrote:
> > Is there anyway to tell mysql to not check the query cache on a per query
> > basis?

>
> > I have a batch of queries, perhaps 1 million ish, that will all be answered
> > very quickly. *The overhead of checking the cache will probably be a not
> > insignificant portion of the query time, and I *KNOW* the answer isn't in
> > the cache so I would like to avoid looking there.

>
> > I can't turn the query cache off because it is used heavily for other
> > queries and SQL_NO_CACHE only stops writes, not reads to the cache.

>
> What's the problem exactly? if your batch runs too slowly, try setting
> autocommit off and postpone foreign-key checking. Or use compression if
> you send all queries over a large or slow network.
>
> You can configure the server not to use the query cache by setting its
> size to zero, I think. I did not look it up, but you could check if you
> can set that setting on a session basis with the SET command.
>
> But it is the last factor I would suspect to make a difference.
>
> By the way, as far as I know it is not the answer that is in the cache,
> but the result of the query parsing.


The result set is cached. And (afaik) the key is the raw text of the
query.

> So if you fire the same query twice
> with different field values in a WHERE clause, the server does not need
> to reparse the entire query, thus saving potentially a lot of work.
>
> And my gut feeling says that checking the cache takes less time then
> submitting a "stop caching" keyword over a network...


Any operation that hits the disk will blow away any QC overhead. Since
MySQL is a disk based system I suspect, along with Paralytic, that the
OP's wild-ass guess is suspect.

>
> Best regards.


Reply With Quote
  #5 (permalink)  
Old 09-05-2009, 08:01 AM
Brian Wakem
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Captain Paralytic wrote:

> On 4 Sep, 21:22, Brian Wakem <n...@email.com> wrote:
>> The overhead of checking the cache will probably be a not
>> insignificant portion of the query time

> On what do you base this assertion?


I need to do it a million times and the queries themselves are very fast.


--
Brian Wakem
Reply With Quote
  #6 (permalink)  
Old 09-05-2009, 09:11 AM
Brian Wakem
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem wrote:

> Captain Paralytic wrote:
>
>> On 4 Sep, 21:22, Brian Wakem <n...@email.com> wrote:
>>> The overhead of checking the cache will probably be a not
>>> insignificant portion of the query time

>> On what do you base this assertion?

>
> I need to do it a million times and the queries themselves are very fast.



Also when I SHOW PROCESSLIST while these queries are running, about 3 times
in 10 it says 'checking query cache' or something of that effect.


--
Brian Wakem
Reply With Quote
  #7 (permalink)  
Old 09-05-2009, 12:28 PM
Peter H. Coffin
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

On Sat, 05 Sep 2009 00:57:45 +0200, Dikkie Dik wrote:
> Brian Wakem wrote:
>> Is there anyway to tell mysql to not check the query cache on a per query
>> basis?
>>
>> I have a batch of queries, perhaps 1 million ish, that will all be answered
>> very quickly. The overhead of checking the cache will probably be a not
>> insignificant portion of the query time, and I *KNOW* the answer isn't in
>> the cache so I would like to avoid looking there.
>>
>> I can't turn the query cache off because it is used heavily for other
>> queries and SQL_NO_CACHE only stops writes, not reads to the cache.

>
> What's the problem exactly?


Almost always the vital question. A mailing list I follow has an aphorism
"Ask questions; don't ask answers."

> if your batch runs too slowly, try setting
> autocommit off and postpone foreign-key checking. Or use compression if
> you send all queries over a large or slow network.
>
> You can configure the server not to use the query cache by setting its
> size to zero, I think. I did not look it up, but you could check if you
> can set that setting on a session basis with the SET command.


And, I think, that's the default for small installs...

--
47. If I learn that a callow youth has begun a quest to destroy me, I will
slay him while he is still a callow youth instead of waiting for him to
mature.
--Peter Anspach's list of things to do as an Evil Overlord
Reply With Quote
  #8 (permalink)  
Old 09-05-2009, 10:12 PM
Jerry Stuckle
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem wrote:
> Jerry Stuckle wrote:
>
>> Brian Wakem wrote:
>>> Jerry Stuckle wrote:
>>>

>> And how are you going to guarantee that? You can't, that's how. Any
>> disk access from any other application may free some or all of that disk
>> cache for other uses.

>
>
> The server has sufficient RAM to hold the whole database, which has already
> been read into the OS cache using cat dbfile >> /dev/null
>
> The server does nothing else but access the same database so I can say with
> some certainty the disk will not be accessed, as the query time in my
> testing backs up.
>


No, the server does a lot of other things. MySQL is not an OS, but it
runs under an OS. And that OS is always doing things you don't see.
Also, if you aren't running anything else on this machine, you would
have to be accessing it from a network. That's a whole different layer
(which makes the problem even more complex).

>
>>>>> I did qualify my original question with "probably". I don't think it
>>>

>> And the details of how you set up such a test? The data being searched,
>> hardware being used, other applications in use? IOW, everything
>> necessary to duplicate your test.

>
>
> I copied the database onto a test machine. I can't give you the database to
> test yourself as it is inpracticely large and is DPA protected.
>


How convenient. IOW, you expect us to take your test as the Gospel with
no confirmation. I think not.

> The production server is:
>
> 2 x Quad Core E5540 Xeons
> 16GB 1066MHz RAM
> 4 x 146GB 15000rpm SAS
> RAID 1+0
> RHEL5
>
> The test machine is:
>
> 1 x Core2 Duo E6750
> 4GB RAM
> 7200rpm IDE HDD
> Mandriva 2008.1
>
> Both running mysql 5.0.45
>


Which means the production server could get completely different results.


> I wrote a perl script to loop over the queries, which are simple SELECT
> COUNT(*) FROM t1,t2 WHERE t1.id=t2.id AND t1.colname = ? AND t2.colname = ?
>


Are these the real queries you're executing? Of not, your test means
absolutely nothing.

> I used Time::HiRes to measure the time spent running the queries.
>
> I ran them first with query cache on and set to 8MB, then I turned it off.
>
>
>>> Now does anyone have anything useful to say on this subject?
>>>
>>>

>> We have been saying useful things. They just don't agree with your
>> (incorrect) preconceived ideas.
>>
>> I suggest you take your questions somewhere where no one knows anything
>> about MySQL. You might get an answer you agree with - instead of
>> correct ones.

>
>
> I fail to see why you are even posting in this thread as you have nothing to
> offer?
>
> Anyway with my own trial and error I have found that adding AND RAND() or
> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
> 10500/sec, so mysql must be making the decision not to use the cache if
> they are present. It may just be not writing to the cache but it is faster
> than using SQL_NO_CACHE. It needs further research but I may have my
> answer.
>
>


I fail to see why you are even posting on usenet when you won't take advice.

You already know your answer. Why don't you just go do it. And don't
come crying back here when your sorry arse is in a jam because you were
wrong and people much more knowledgeable than you tried to tell you that.

Loser...

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Reply With Quote
  #9 (permalink)  
Old 09-06-2009, 06:46 AM
Axel Schwenke
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem <no@email.com> wrote:
>>>
>>> OK I will stop guessing.


Good. You're becoming (kind of) serious. Finally.

>>> I have set-up the environment on a test machine and have run 50000
>>> queries
>>> (10 times to get a average) with query cache on and off. Here are the
>>> results:
>>>
>>>
>>> Query cache on:
>>>
>>> 50000 queries in 6.48 seconds = 7711 queries per second
>>> 50000 queries in 6.46 seconds = 7735 queries per second
>>> 50000 queries in 6.46 seconds = 7736 queries per second
>>> 50000 queries in 6.47 seconds = 7725 queries per second
>>> 50000 queries in 6.53 seconds = 7661 queries per second
>>> 50000 queries in 6.50 seconds = 7695 queries per second
>>> 50000 queries in 6.49 seconds = 7706 queries per second
>>> 50000 queries in 6.45 seconds = 7751 queries per second
>>> 50000 queries in 6.78 seconds = 7375 queries per second
>>> 50000 queries in 6.48 seconds = 7720 queries per second
>>> (average 6.51 seconds)
>>>
>>>
>>> Query cache off:
>>>
>>> 50000 queries in 4.51 seconds = 11087 queries per second
>>> 50000 queries in 4.44 seconds = 11255 queries per second
>>> 50000 queries in 4.45 seconds = 11240 queries per second
>>> 50000 queries in 4.42 seconds = 11316 queries per second
>>> 50000 queries in 4.42 seconds = 11314 queries per second
>>> 50000 queries in 4.43 seconds = 11293 queries per second
>>> 50000 queries in 4.41 seconds = 11334 queries per second
>>> 50000 queries in 4.45 seconds = 11223 queries per second
>>> 50000 queries in 4.47 seconds = 11186 queries per second
>>> 50000 queries in 4.45 seconds = 11233 queries per second
>>> (average 4.45 seconds)
>>>
>>> So the potential saving for 1m queries is 20*(6.51-4.45) = 41.2 seconds


[waiting for more details as the above info is quite scarce]

> I wrote a perl script to loop over the queries, which are simple SELECT
> COUNT(*) FROM t1,t2 WHERE t1.id=t2.id AND t1.colname = ? AND t2.colname = ?
>
> I used Time::HiRes to measure the time spent running the queries.
>
> I ran them first with query cache on and set to 8MB, then I turned it off.


There are several points to be noted here:

- you seem not to have queried any query cache statistics for
the first run. Why not?

- what about the "?" in your SQL string? Will any of those
queries ever repeat? No? Is that a realistic workload then?

- 8MB query cache seems to be quite small.


It looks as if either

- your query cache is too small to give a significant number
of cache hits. This can easily be seen by looking at the
Qcache_hits, Qcache_inserts and Qcache_lowmem_prunes
status variables. (SHOW GLOBAL STATUS LIKE 'Qcache%')

- all your queries are unique. I.o.w. your workload is not
cachable at all. This might be the situation with real
workload. Probably it's just an effect of a poorly chosen
benchmark.

Anyway: if the query cache never gets any hits but only misses,
then indeed the cache only adds overhead and turning it off will
increase performance. This sometimes happens with real workload,
but it's not normal.

The query cache is a bet that an (expensive) query will repeat
before it is thrown out of the cache (for whatever reason).
If you construct pathological workload that makes the server
lose this bet all the time, then ... well ... it loses.

> Anyway with my own trial and error I have found that adding AND RAND() or
> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
> 10500/sec, so mysql must be making the decision not to use the cache if
> they are present.


You never read the manual, do you? It explains quite nicely
when the query cache is used and when not.


XL
Reply With Quote
  #10 (permalink)  
Old 09-06-2009, 08:28 AM
Brian Wakem
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Axel Schwenke wrote:

> Brian Wakem <no@email.com> wrote:
>>>>
>>>> OK I will stop guessing.

>
> Good. You're becoming (kind of) serious. Finally.
>
>>>> I have set-up the environment on a test machine and have run 50000
>>>> queries
>>>> (10 times to get a average) with query cache on and off. Here are the
>>>> results:
>>>>
>>>>
>>>> Query cache on:
>>>>
>>>> 50000 queries in 6.48 seconds = 7711 queries per second
>>>> 50000 queries in 6.46 seconds = 7735 queries per second
>>>> 50000 queries in 6.46 seconds = 7736 queries per second
>>>> 50000 queries in 6.47 seconds = 7725 queries per second
>>>> 50000 queries in 6.53 seconds = 7661 queries per second
>>>> 50000 queries in 6.50 seconds = 7695 queries per second
>>>> 50000 queries in 6.49 seconds = 7706 queries per second
>>>> 50000 queries in 6.45 seconds = 7751 queries per second
>>>> 50000 queries in 6.78 seconds = 7375 queries per second
>>>> 50000 queries in 6.48 seconds = 7720 queries per second
>>>> (average 6.51 seconds)
>>>>
>>>>
>>>> Query cache off:
>>>>
>>>> 50000 queries in 4.51 seconds = 11087 queries per second
>>>> 50000 queries in 4.44 seconds = 11255 queries per second
>>>> 50000 queries in 4.45 seconds = 11240 queries per second
>>>> 50000 queries in 4.42 seconds = 11316 queries per second
>>>> 50000 queries in 4.42 seconds = 11314 queries per second
>>>> 50000 queries in 4.43 seconds = 11293 queries per second
>>>> 50000 queries in 4.41 seconds = 11334 queries per second
>>>> 50000 queries in 4.45 seconds = 11223 queries per second
>>>> 50000 queries in 4.47 seconds = 11186 queries per second
>>>> 50000 queries in 4.45 seconds = 11233 queries per second
>>>> (average 4.45 seconds)
>>>>
>>>> So the potential saving for 1m queries is 20*(6.51-4.45) = 41.2 seconds

>
> [waiting for more details as the above info is quite scarce]
>
>> I wrote a perl script to loop over the queries, which are simple SELECT
>> COUNT(*) FROM t1,t2 WHERE t1.id=t2.id AND t1.colname = ? AND t2.colname =
>> ?
>>
>> I used Time::HiRes to measure the time spent running the queries.
>>
>> I ran them first with query cache on and set to 8MB, then I turned it
>> off.

>
> There are several points to be noted here:
>
> - you seem not to have queried any query cache statistics for
> the first run. Why not?
>
> - what about the "?" in your SQL string? Will any of those
> queries ever repeat? No? Is that a realistic workload then?
>
> - 8MB query cache seems to be quite small.
>
>
> It looks as if either
>
> - your query cache is too small to give a significant number
> of cache hits. This can easily be seen by looking at the
> Qcache_hits, Qcache_inserts and Qcache_lowmem_prunes
> status variables. (SHOW GLOBAL STATUS LIKE 'Qcache%')
>
> - all your queries are unique. I.o.w. your workload is not
> cachable at all. This might be the situation with real
> workload. Probably it's just an effect of a poorly chosen
> benchmark.



None of the queries will be repeated, so none of it is cacheable and the
size of the cache is irrelevant.


> Anyway: if the query cache never gets any hits but only misses,
> then indeed the cache only adds overhead and turning it off will
> increase performance. This sometimes happens with real workload,
> but it's not normal.
>
> The query cache is a bet that an (expensive) query will repeat
> before it is thrown out of the cache (for whatever reason).
> If you construct pathological workload that makes the server
> lose this bet all the time, then ... well ... it loses.
>
>> Anyway with my own trial and error I have found that adding AND RAND() or
>> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
>> 10500/sec, so mysql must be making the decision not to use the cache if
>> they are present.

>
> You never read the manual, do you? It explains quite nicely
> when the query cache is used and when not.
>



I did but it doesn't make it clear if RAND() AND CURTIME() stops cache look
ups, it only says it doesn't cache results that contain them.


--
Brian Wakem
Reply With Quote
  #11 (permalink)  
Old 09-06-2009, 08:32 AM
Brian Wakem
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Jerry Stuckle wrote:

> Brian Wakem wrote:
>> Jerry Stuckle wrote:
>>
>>> Brian Wakem wrote:
>>>> Jerry Stuckle wrote:
>>>>
>>> And how are you going to guarantee that? You can't, that's how. Any
>>> disk access from any other application may free some or all of that disk
>>> cache for other uses.

>>
>>
>> The server has sufficient RAM to hold the whole database, which has
>> already been read into the OS cache using cat dbfile >> /dev/null
>>
>> The server does nothing else but access the same database so I can say
>> with some certainty the disk will not be accessed, as the query time in
>> my testing backs up.
>>

>
> No, the server does a lot of other things. MySQL is not an OS, but it
> runs under an OS. And that OS is always doing things you don't see.
> Also, if you aren't running anything else on this machine, you would
> have to be accessing it from a network. That's a whole different layer
> (which makes the problem even more complex).



The point I was making (lost on you it seems) is that mysql will not access
the disk to answer my queries. If something else on the box accesses the
disk, I don't care.


>>>>>> I did qualify my original question with "probably". I don't think it
>>>>
>>> And the details of how you set up such a test? The data being searched,
>>> hardware being used, other applications in use? IOW, everything
>>> necessary to duplicate your test.

>>
>>
>> I copied the database onto a test machine. I can't give you the database
>> to test yourself as it is inpracticely large and is DPA protected.
>>

>
> How convenient. IOW, you expect us to take your test as the Gospel with
> no confirmation. I think not.
>
>> The production server is:
>>
>> 2 x Quad Core E5540 Xeons
>> 16GB 1066MHz RAM
>> 4 x 146GB 15000rpm SAS
>> RAID 1+0
>> RHEL5
>>
>> The test machine is:
>>
>> 1 x Core2 Duo E6750
>> 4GB RAM
>> 7200rpm IDE HDD
>> Mandriva 2008.1
>>
>> Both running mysql 5.0.45
>>

>
> Which means the production server could get completely different results.
>
>
>> I wrote a perl script to loop over the queries, which are simple SELECT
>> COUNT(*) FROM t1,t2 WHERE t1.id=t2.id AND t1.colname = ? AND t2.colname =
>> ?
>>

>
> Are these the real queries you're executing? Of not, your test means
> absolutely nothing.
>
>> I used Time::HiRes to measure the time spent running the queries.
>>
>> I ran them first with query cache on and set to 8MB, then I turned it
>> off.
>>
>>
>>>> Now does anyone have anything useful to say on this subject?
>>>>
>>>>
>>> We have been saying useful things. They just don't agree with your
>>> (incorrect) preconceived ideas.
>>>
>>> I suggest you take your questions somewhere where no one knows anything
>>> about MySQL. You might get an answer you agree with - instead of
>>> correct ones.

>>
>>
>> I fail to see why you are even posting in this thread as you have nothing
>> to offer?
>>
>> Anyway with my own trial and error I have found that adding AND RAND() or
>> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
>> 10500/sec, so mysql must be making the decision not to use the cache if
>> they are present. It may just be not writing to the cache but it is
>> faster than using SQL_NO_CACHE. It needs further research but I may have
>> my answer.
>>
>>

>
> I fail to see why you are even posting on usenet when you won't take
> advice.
>
> You already know your answer. Why don't you just go do it. And don't
> come crying back here when your sorry arse is in a jam because you were
> wrong and people much more knowledgeable than you tried to tell you that.
>
> Loser...



I was wrong? I was 100% correct on every point. You were wrong on every
point. You think you are more knowledgeable? You didn't offer a single
useful suggestion, only egotistical BS.

Don't bother replying, I wont read it.


--
Brian Wakem
Reply With Quote
  #12 (permalink)  
Old 09-06-2009, 11:20 AM
Axel Schwenke
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem <no@email.com> wrote:
> Axel Schwenke wrote:
>
>> It looks as if


[cut]

>> - all your queries are unique. I.o.w. your workload is not
>> cachable at all. This might be the situation with real
>> workload. Probably it's just an effect of a poorly chosen
>> benchmark.

>
> None of the queries will be repeated, so none of it is cacheable and the
> size of the cache is irrelevant.


Indeed the whole cache is irrelevant then.
Your "test" doesn't show anything unexpected or new.

>>> Anyway with my own trial and error I have found that adding AND RAND() or
>>> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
>>> 10500/sec, so mysql must be making the decision not to use the cache if
>>> they are present.

>>
>> You never read the manual, do you? It explains quite nicely
>> when the query cache is used and when not.

>
> I did but it doesn't make it clear if RAND() AND CURTIME() stops cache look
> ups, it only says it doesn't cache results that contain them.


SQL_NO_CACHE prevents both lookup and caching. It's by far the
most efficient way to disable the query cache selectively.
The only better approach would be to turn off the query cache.

Note: query_cache_type can be set both globally and per session.
This allows to turn off the query cache for a session without
modifying the query string.


XL
Reply With Quote
  #13 (permalink)  
Old 09-06-2009, 12:29 PM
Jerry Stuckle
Guest
 
Posts: n/a
Default Re: Avoid checking query cache

Brian Wakem wrote:
> Jerry Stuckle wrote:
>
>> Brian Wakem wrote:
>>> Jerry Stuckle wrote:
>>>
>>>> Brian Wakem wrote:
>>>>> Jerry Stuckle wrote:
>>>>>
>>>> And how are you going to guarantee that? You can't, that's how. Any
>>>> disk access from any other application may free some or all of that disk
>>>> cache for other uses.
>>>
>>> The server has sufficient RAM to hold the whole database, which has
>>> already been read into the OS cache using cat dbfile >> /dev/null
>>>
>>> The server does nothing else but access the same database so I can say
>>> with some certainty the disk will not be accessed, as the query time in
>>> my testing backs up.
>>>

>> No, the server does a lot of other things. MySQL is not an OS, but it
>> runs under an OS. And that OS is always doing things you don't see.
>> Also, if you aren't running anything else on this machine, you would
>> have to be accessing it from a network. That's a whole different layer
>> (which makes the problem even more complex).

>
>
> The point I was making (lost on you it seems) is that mysql will not access
> the disk to answer my queries. If something else on the box accesses the
> disk, I don't care.
>


And the point I am making is that you cannot guarantee that. But your
preconceived notions don't allow you to understand that.

>
>>>>>>> I did qualify my original question with "probably". I don't think it
>>>> And the details of how you set up such a test? The data being searched,
>>>> hardware being used, other applications in use? IOW, everything
>>>> necessary to duplicate your test.
>>>
>>> I copied the database onto a test machine. I can't give you the database
>>> to test yourself as it is inpracticely large and is DPA protected.
>>>

>> How convenient. IOW, you expect us to take your test as the Gospel with
>> no confirmation. I think not.
>>
>>> The production server is:
>>>
>>> 2 x Quad Core E5540 Xeons
>>> 16GB 1066MHz RAM
>>> 4 x 146GB 15000rpm SAS
>>> RAID 1+0
>>> RHEL5
>>>
>>> The test machine is:
>>>
>>> 1 x Core2 Duo E6750
>>> 4GB RAM
>>> 7200rpm IDE HDD
>>> Mandriva 2008.1
>>>
>>> Both running mysql 5.0.45
>>>

>> Which means the production server could get completely different results.
>>
>>
>>> I wrote a perl script to loop over the queries, which are simple SELECT
>>> COUNT(*) FROM t1,t2 WHERE t1.id=t2.id AND t1.colname = ? AND t2.colname =
>>> ?
>>>

>> Are these the real queries you're executing? Of not, your test means
>> absolutely nothing.
>>
>>> I used Time::HiRes to measure the time spent running the queries.
>>>
>>> I ran them first with query cache on and set to 8MB, then I turned it
>>> off.
>>>
>>>
>>>>> Now does anyone have anything useful to say on this subject?
>>>>>
>>>>>
>>>> We have been saying useful things. They just don't agree with your
>>>> (incorrect) preconceived ideas.
>>>>
>>>> I suggest you take your questions somewhere where no one knows anything
>>>> about MySQL. You might get an answer you agree with - instead of
>>>> correct ones.
>>>
>>> I fail to see why you are even posting in this thread as you have nothing
>>> to offer?
>>>
>>> Anyway with my own trial and error I have found that adding AND RAND() or
>>> AND CURTIME() onto the end of every query speeds it up from 7700/sec to
>>> 10500/sec, so mysql must be making the decision not to use the cache if
>>> they are present. It may just be not writing to the cache but it is
>>> faster than using SQL_NO_CACHE. It needs further research but I may have
>>> my answer.
>>>
>>>

>> I fail to see why you are even posting on usenet when you won't take
>> advice.
>>
>> You already know your answer. Why don't you just go do it. And don't
>> come crying back here when your sorry arse is in a jam because you were
>> wrong and people much more knowledgeable than you tried to tell you that.
>>
>> Loser...

>
>
> I was wrong? I was 100% correct on every point. You were wrong on every
> point. You think you are more knowledgeable? You didn't offer a single
> useful suggestion, only egotistical BS.
>
> Don't bother replying, I wont read it.
>
>


You were wrong on every point. But yoy refuse to see the facts I (and
others) have presented because they don't agree with your incorrect
preconceived ideas.

Loser.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Reply With Quote
 
Reply

Popular Tags in the Forum
avoid, cache, checking, query

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off




All times are GMT. The time now is 03:18 AM.


Copyright ©2009

LinkBacks Enabled by vBSEO 3.3.0 RC2 © 2009, Crawlability, Inc.