Thanks Joseph.
I just to another look at the query and it actually takes 65 seconds on the
first run, not 10 seconds, so this is important for me to understand.
So my next questions are:
Is there any way to tune the OS file system cache?
Is there any way to flush it or examine what's in it?
Seems like if I wait a while, the long query happens again. Looks like it
flushes itself after some time - how often does it flush itself?
Thanks again,
-Bob
|---------+---------------------------->
| | Joseph Bueno |
| | <[EMAIL PROTECTED]|
| | der.com> |
| | |
| | 10/29/2003 11:36 |
| | AM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc: [EMAIL PROTECTED]
|
| Subject: Re: inconsistent query times for same query
|
>---------------------------------------------------------------------------------------------------------------|
There is caching but it is at OS level: on first query, data are
fetched from disk; other queries read directly from file system cache.
Hope this helps
Joseph Bueno
[EMAIL PROTECTED] wrote:
> Hi everyone. I'm using MySQL 4.0.16 with W2K/SP4 on a 512KB RAM/1.2GHz
> Athlon machine (my dev workstation).
> I'm having a strange problem: when I execute a query via the MySQL
Control
> Center, it takes 10 seconds to return. About three seconds into the
query,
> I issue a "mysqladmin processlist" and see the State as "sending data".
To
> me, that means the query is done and the server is sending the data to
the
> client (I'm running both on same machine). The CPU is only at about 4%,
but
> the hard drive light is flashing like an XMas tree on crack.
> I examined the query using "Explain" and it's using the correct index as
I
> specified.
> If I re-execute the query, it returns in .13 seconds!!!
> I thought maybe it was cached by the client so I kill the client and
> execute it again - .13 seconds. I restart the server and do it again -
.13
> seconds. So it doesn't look like anything's being cached. Variables
> query_cache_size=0 and query_cache_type=DEMAND, it seems like no caching
> should happen. This happens to other queries as well.
> I just don't get it. Why the difference?
> The table "test" has 1,000,000 records, so I'm definitely not complaining
> about a subsecond response - but if the public hits the production server
> and I get the 10 second version for every query, I'm in trouble. Since
> users dynamically create the queries, I can't count on them being cached
-
> so any moderately complex query seems to exibit this behavior. I really
> feel like I need to understand why it's happening so I can fix any
> underlying problems.
> Any ideas????
>
>
> Here's the query - because the users choose several options to build the
> query, it's dynamically built via a Java class (don't think that's
> relevant, but..):
>
> SELECT
> UID,UserName,Gender,City,State,Zip,Country,Age,Photo,OnLine,LastLogon
FROM
> test USE INDEX (big) WHERE Status=2 AND Viewable=1 AND Age >=18 AND Age
<=
> 99 AND Photo!='' AND Height <=66 AND gender IN ('b') AND Weight >=100 AND
> Height >=60 AND Weight <=150 LIMIT 250
>
> The following columns are in the "big" multi-column index:
> Status, Viewable, Online, Age, Height. See the table definition below.
>
> here are the relavent columns:
>
+-------------------+---------------+------+-----+------------------------------+-------+
> | Field | Type | Null | Key | Default
> | Extra |
>
+-------------------+---------------+------+-----+------------------------------+-------+
> | UID | int(11) | | PRI | 0
> | |
> | UserName | char(20) | | UNI |
> | |
> | Status | tinyint(1) | | MUL | 0
> | |
> | Viewable | tinyint(1) | | | 0
|
> |
> | City | char(30) | YES | | some city
> | |
> | State | char(2) | YES | | CA
> | |
> | Country | char(3) | YES | | USA
> | |
> | Zip | char(10) | YES | MUL | 90210
> | |
> | Age | tinyint(2) | YES | MUL | NULL
> | |
> | Gender | char(1) | YES | | NULL
> | |
> | Height | tinyint(3) | YES | | NULL
|
> |
> | Weight | char(3) | YES | MUL | NULL
> | |
> | OnLine | tinyint(1) | YES | | NULL
> | |
> | Photo | char(30) | YES | | /path/path/photo.jpg |
> |
>
+-------------------+---------------+------+-----+------------------------------+-------+
>
> Thanks for any insight!
> -Bob
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
