Re: Slow 'sending data' phase on mySQL query, for simple query
Rick James Wrote:
-------------------------------------------------------
> Hourly processing -- Is it munching the same old
> data over and over?
Its running over the data but doesn't create summary data, it needs to loop through the records each time.
>
> When you run a query that hits only columns that
> are in a single index, then only the index needs
> to be read. This is noticeably faster. (EXPLAIN
> will say "Using index".) (In InnoDB, this note
> does not apply to the PRIMARY KEY, which is
> embedded in the data.)
Yeah, I had a feeling it was probably something like that
> Your example of running much faster the second
> time is because of caching of index and/or data
> blocks in the "buffer pool". With the Query cache
> on, and SQL_CACHE requested, it _could_ run almost
> instantly. (There are several caveats here. In
> general I do not recommend using the Query
> cache.)
Yeah, I knew it would be cached within the buffer pool, obviously the 100K fits in it, but not the 500k query.
> 512M = 524k Kib; but how much RAM is available?
> Could you make the buffer pool bigger? Is the 4GB
> VM exclusive to MySQL? Is there "overcommit"?
> Are you running only InnoDB, no MyISAM? Then
> recommend key_buffer_size = 40M and
> innodb_buffer_pool_size = 2500M, perhaps less if
> the entire dataset is much less than that.
The real server only has 4GB anyway.
> Why use VM at all? As you noticed, it can add
> some overhead.
Easiest way to setup the development server in the office, I knew the performance was roughly inline with the real life dual Xeon server so its a reasonably good indicator.
> If you consider splitting the table, consider
> PARTITION.
Its not in the version of mySQL we've got though and I don't fancy testing all the websites we host to make sure it works without a problem with all of them.
To be honest, setting up 15 tables is easy enough and simple to implement within the system anyway.
-UPDATE: I downloaded 5.1.45 (the current general release) and tried out partitioning. However, the table has an autoinc field in it (ui_id), and the field being partitioned upon (in this case user_group_id) isn't part of the primary key, its not allowed and I can't partition the table.
> Yes, you have valid reasons for using
> mysql_unbuffered_query. I'm curious; what
> percentage of the total (query execution + result
> processing) time does buffered/unbuffered
> cost/save?
Well, in my experience, the 500k query either takes 12sec with mysql_query or returns almost instantly with mysql_unbuffered_query and then takes 12sec to loop through the rows with mysql_fetch_row.
So the total time is the same, it depends on the script as to which is better in any given situation.
Thanks for your help though Rick, its appreciated :)
Edited 1 time(s). Last edit at 03/23/2010 02:39AM by Steve Childs.