PHP SQL - Optimising Queries

Author

Optimising Queries

Vigil

2004-11-18, 3:57 pm

Seems this is the only newsgroup that has anything to do with MySQL :-/

I am trying to optimise various queries, but caches and buffers are
getting in the way. I want to connect to the server using the MySQL
command line client and run a select. I don't want the results to be
cached, buffered, or remembered in any way. When I run the select again, I
want the query to run as if it was the first time it was called. If the
query took 16 seconds the first time, I want it the same query to take 16
seconds at subsequent times.

I have tried disabling various buffers and cache types. In my.cnf:

[mysqld]
init_connect = 'set query_cache_type = 0'
key_buffer_size = 0
query_cache_limit = 0
query_cache_size = 0
thread_cache_size = 0

I even restart the server, and quit and restart the client, and the
previous queries are still returning results almost instantly. Is Linux
caching my (disk?) requests? How can I turn everything off so I can
optimise my queries?

I'm surprised I couldn't find much help on Google about this, TBH. I would
have thought it would have been as common as muck.

--

..

J.O. Aho

2004-11-18, 3:57 pm

Vigil wrote:

> I even restart the server, and quit and restart the client, and the
> previous queries are still returning results almost instantly. Is Linux
> caching my (disk?) requests? How can I turn everything off so I can
> optimise my queries?

Linux dose cache things into RAM to speed up things.
You will se that easilly by running top.

> I'm surprised I couldn't find much help on Google about this, TBH. I would
> have thought it would have been as common as muck.

This ain't one of those features that you normally want to turn off. I think
you will need to dig into the kernel a bit and disable the cache code.

//Aho

Manuel Lemos

2004-11-18, 3:57 pm

Hello,

On 11/18/2004 12:46 PM, Vigil wrote:
> Seems this is the only newsgroup that has anything to do with MySQL :-/
>
> I am trying to optimise various queries, but caches and buffers are
> getting in the way. I want to connect to the server using the MySQL
> command line client and run a select. I don't want the results to be
> cached, buffered, or remembered in any way. When I run the select again, I
> want the query to run as if it was the first time it was called. If the
> query took 16 seconds the first time, I want it the same query to take 16
> seconds at subsequent times.

I recommend that you just enable the slow queries log.

In real world applications the greatest concern with unoptimized queries
are those that are slowed down because they are locked by others that
take a great time to run and lock other tables implicitly.

Once you activate the slow queries log you can see which queries are
stalling others. Once you optimize which run first, the others that
seemed slow will no longer be stalled for so long and eventually will
become much faster.

The query cache is irrelevant here because this is a matter of table
access contention and not table traversal speed.

--

Regards,
Manuel Lemos

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/

PHP Reviews - Reviews of PHP books and other products
http://www.phpclasses.org/reviews/

Metastorage - Data object relational mapping layer generator
http://www.meta-language.net/metastorage.html

Sponsored Links