Re: Re: RE: performance tunning 4.x
mos,
Friday, August 30, 2002, 7:04:25 PM, you wrote:
m At 07:58 AM 8/30/2002, you wrote:
mos,
Thursday, August 29, 2002, 5:45:05 PM, you wrote:
Start by looking at the MySQL documentation:
http://www.mysql.com/doc/en/Query_Cache.html
Read the entire 6.9 section.
It offers quite a bit of information on how the cache operates, how
you should configure it and so on.
m I read it too but I have a question about the cache, this sentence
m in particular.
m NOTE: The query cache does not return stale data. When data is modified,
m any relevant entries in the query cache are flushed.
m If I have a select statement like:
m select col1, col2 from table1 where col1='A'
[skip]
m c) Or will any update to the table automatically flush the cache?
m Victoria,
Yes, relevant entries in the query cache are flushed when any changes were
made on the
table.
m Can you define relevant? Do you mean all caches for that table?
m Are you saying any change at all to the table, say I add a row, delete a
m row, or modify a row even if it is not in the scope of the cached query,
m will cause all caches for that table to be discarded? Argghhh! :-O
Yes.
And it's described in the manual:
http://www.mysql.com/doc/en/Query_Cache_How.html
m Example:
m select customer_name from customers where state = 'NY' //-Adds query to cache
m insert into customers (customer_name, state) values('jones',
m 'CA') //-Flushes the cache for customers table?
m select customer_name from customers where state = 'NY' //-No longer in cache?
m If you are correct, then I feel caching is useful primarily for readonly
m access to a table. As soon as a row is modified in any way, the caches for
m that table are flushed. Would you agree?
Yes.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: performance tunning 4.x
mos, Thursday, August 29, 2002, 5:45:05 PM, you wrote: Start by looking at the MySQL documentation: http://www.mysql.com/doc/en/Query_Cache.html Read the entire 6.9 section. It offers quite a bit of information on how the cache operates, how you should configure it and so on. m I read it too but I have a question about the cache, this sentence m in particular. m NOTE: The query cache does not return stale data. When data is modified, m any relevant entries in the query cache are flushed. m If I have a select statement like: m select col1, col2 from table1 where col1='A' [skip] m c) Or will any update to the table automatically flush the cache? Yes, relevant entries in the query cache are flushed when any changes were made on the table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: performance tunning 4.x
At 07:58 AM 8/30/2002, you wrote:
mos,
Thursday, August 29, 2002, 5:45:05 PM, you wrote:
Start by looking at the MySQL documentation:
http://www.mysql.com/doc/en/Query_Cache.html
Read the entire 6.9 section.
It offers quite a bit of information on how the cache operates, how
you should configure it and so on.
m I read it too but I have a question about the cache, this sentence
m in particular.
m NOTE: The query cache does not return stale data. When data is modified,
m any relevant entries in the query cache are flushed.
m If I have a select statement like:
m select col1, col2 from table1 where col1='A'
[skip]
m c) Or will any update to the table automatically flush the cache?
Victoria,
Yes, relevant entries in the query cache are flushed when any changes were
made on the
table.
Can you define relevant? Do you mean all caches for that table?
Are you saying any change at all to the table, say I add a row, delete a
row, or modify a row even if it is not in the scope of the cached query,
will cause all caches for that table to be discarded? Argghhh! :-O
Example:
select customer_name from customers where state = 'NY' //-Adds query to cache
insert into customers (customer_name, state) values('jones',
'CA') //-Flushes the cache for customers table?
select customer_name from customers where state = 'NY' //-No longer in cache?
If you are correct, then I feel caching is useful primarily for readonly
access to a table. As soon as a row is modified in any way, the caches for
that table are flushed. Would you agree?
Mike
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: performance tunning 4.x
At 11:04 -0500 8/30/02, mos wrote:
At 07:58 AM 8/30/2002, you wrote:
mos,
Thursday, August 29, 2002, 5:45:05 PM, you wrote:
Start by looking at the MySQL documentation:
http://www.mysql.com/doc/en/Query_Cache.html
Read the entire 6.9 section.
It offers quite a bit of information on how the cache operates, how
you should configure it and so on.
m I read it too but I have a question about the cache,
this sentence
m in particular.
m NOTE: The query cache does not return stale data. When data is modified,
m any relevant entries in the query cache are flushed.
m If I have a select statement like:
m select col1, col2 from table1 where col1='A'
[skip]
m c) Or will any update to the table automatically flush the cache?
Victoria,
Yes, relevant entries in the query cache are flushed when any
changes were made on the
table.
Can you define relevant? Do you mean all caches for that table?
Queries in the cache that use the table that has been changed.
Are you saying any change at all to the table, say I add a row,
delete a row, or modify a row even if it is not in the scope of the
cached query, will cause all caches for that table to be discarded?
Argghhh! :-O
Yes. How will the server know that the row is in the scope of the query
unless it reruns it again? If you change the thing from which the cached
result is derived, the cache becomes invalid.
How would you do it otherwise?
Example:
select customer_name from customers where state = 'NY' //-Adds
query to cache
insert into customers (customer_name, state) values('jones', 'CA')
//-Flushes the cache for customers table?
select customer_name from customers where state = 'NY' //-No
longer in cache?
If you are correct, then I feel caching is useful primarily for
readonly access to a table. As soon as a row is modified in any way,
the caches for that table are flushed. Would you agree?
Yes. How would you suggest that it be done otherwise?
Mike
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: performance tunning 4.x
Hi,
Start by looking at the MySQL documentation:
http://www.mysql.com/doc/en/Query_Cache.html
Read the entire 6.9 section.
It offers quite a bit of information on how the cache operates, how
you should configure it and so on.
Regards,
Iikka
likka,
I read it too but I have a question about the cache, this sentence
in particular.
NOTE: The query cache does not return stale data. When data is modified,
any relevant entries in the query cache are flushed.
If I have a select statement like:
select col1, col2 from table1 where col1='A'
1) will the cache be flushed if someone changes a non-referenced column for
one of the retrieved rows:
Update table1 set col3 = 'x' where col1='A'
this update changes col3 that falls within the Where of the query but it
affects a column that is not returned by the query.
2) or will the cache be flushed if someone changes a row that falls outside
of the query's Where clause:
insert into table1 (col1, col2, col3) values('D', 'x', 'y');
This last query updates the same table as the select query, but the row
that is added is outside the range of the query's where clause.
So how smart is the cache?
a) Is it smart enough to allow table updates and if the update doesn't
affect the query, will it continue to use the cache? (This requires a lot
of intelligence.)
b) Or will any update to the rows that fall within the range of the query
(regardless of whether the column is in the query) will flush the cache?
c) Or will any update to the table automatically flush the cache?
MONTY:
I would like to see the Explain command to say if it is using the cache or
not (CACHE:Yes/No). This would help developers make better use of the cache
if they know how what affects it.
Mike
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: performance tunning 4.x
Hello,
A MySQL developer would be able to answer to these questions more
accurately, but I'll try to answer _logically_. So don't bet a million
dollars on the correctness of my answer.
NOTE: The query cache does not return stale data. When data is modified,
any relevant entries in the query cache are flushed.
If I have a select statement like:
select col1, col2 from table1 where col1='A'
1) will the cache be flushed if someone changes a non-referenced column for
one of the retrieved rows:
Update table1 set col3 = 'x' where col1='A'
this update changes col3 that falls within the Where of the query but it
affects a column that is not returned by the query.
I think this flushes the cache. Think about a situation where col1 is a
computed column that derives its values from the data in col3. When col3
is updated, the value of col1 in the query cache would become invalid.
2) or will the cache be flushed if someone changes a row that falls outside
of the query's Where clause:
This shouldn't explicitly flush the cache, but I don't know the details of
the implementation. This insert shouldn't have any effect on the
aforementioned SELECT query, because col1 here is 'D'.
insert into table1 (col1, col2, col3) values('D', 'x', 'y');
This last query updates the same table as the select query, but the row
that is added is outside the range of the query's where clause.
So how smart is the cache?
a) Is it smart enough to allow table updates and if the update doesn't
affect the query, will it continue to use the cache? (This requires a lot
of intelligence.)
b) Or will any update to the rows that fall within the range of the query
(regardless of whether the column is in the query) will flush the cache?
c) Or will any update to the table automatically flush the cache?
MONTY:
I would like to see the Explain command to say if it is using the cache or
not (CACHE:Yes/No). This would help developers make better use of the cache
if they know how what affects it.
Mike
**
* Iikka Meriläinen *
* E-mail: [EMAIL PROTECTED] *
* Vaala, Finland *
**
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: performance tunning 4.x
Hello, Yes, there are lots of good documentation: First, the official MySQL Technical Reference, Section 5 at http://www.mysql.com/doc/en/index.html Then scroll down to 5 MySQL Optimisation and read those. Also, check this: http://www.devshed.com/Server_Side/MySQL/Optimize/page1.html It's a bit older, but still useful. And finally, use www.google.com. :-) Regards, Iikka On Wed, 28 Aug 2002, Sean Hager wrote: Are there any documents on how to performance tune mySQL 4.x? sean. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: performance tunning 4.x
In the Ziff Daveis eWeek benchmark http://www.eweek.com/article/0,3658,s=708a=23115,00.asp )I noticed the following: MySQL's great performance was due mostly to our use of an in-memory query results cache that is new in MySQL 4.0.1. When we tested without this cache, MySQL's performance fell by two-thirds. I couldn't find any information in the online document on how to configure this in-memory query results chache. Can anyone point me to some information on how to do this? sean. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Iikka Meriläinen Sent: Wednesday, August 28, 2002 8:37 AM To: Sean Hager Cc: mySQL (E-mail) Subject: Re: performance tunning 4.x Hello, Yes, there are lots of good documentation: First, the official MySQL Technical Reference, Section 5 at http://www.mysql.com/doc/en/index.html Then scroll down to 5 MySQL Optimisation and read those. Also, check this: http://www.devshed.com/Server_Side/MySQL/Optimize/page1.html It's a bit older, but still useful. And finally, use www.google.com. :-) Regards, Iikka On Wed, 28 Aug 2002, Sean Hager wrote: Are there any documents on how to performance tune mySQL 4.x? sean. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: performance tunning 4.x
On Wed, 28 Aug 2002, Sean Hager wrote: In the Ziff Daveis eWeek benchmark http://www.eweek.com/article/0,3658,s=708a=23115,00.asp )I noticed the following: MySQL's great performance was due mostly to our use of an in-memory query results cache that is new in MySQL 4.0.1. When we tested without this cache, MySQL's performance fell by two-thirds. I couldn't find any information in the online document on how to configure this in-memory query results chache. Can anyone point me to some information on how to do this? Hi, Start by looking at the MySQL documentation: http://www.mysql.com/doc/en/Query_Cache.html Read the entire 6.9 section. It offers quite a bit of information on how the cache operates, how you should configure it and so on. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
