Hi,

I have a table with 18 cols, (4x varchar(255), 1xtext and the others are
int or tinyint (signed and unsigned).

I do the following select:
SELECT id, title, date
FROM feeds
WHERE parent_id=00000
AND draft =0
AND deleted =0
ORDER BY date DESC
LIMIT 5

I have an index on (parent_id, draft, deleted, date)

Most of the time the query is lightning quick but in some cases the
query is stuck in 'Sending data'

the 'title' is a varchar(255) and the id, date are ints.

I have noticed a certain 'parent_id' that seems to cause the problem
more often than others, but there is nothing special about it.

What could be causing the status to be stuck on 'Sending data'? the
table 'feeds' is locked because of that select.

Thanks

Simon

I had a similar problem a few years ago. My server uses cPanel, which allowed me to install Munin. Munin showed me that I was maxing out the number ofqueries allowed with my MySQL settings, so it was having to wait for one to finish before moving on to the next one.

A quick fix MIGHT be to change all of your SELECT queries to something like:

SELECT SQL_CACHE id, title, date ...

But that's a band-aid; if the problem is that you're getting more queries than the settings allow, then you'll either need to find where those queriesare coming from, or modify the settings to allow for more.

If you can install Munin and find this to be the problem, post back and I'll let you know what settings I had to change.

Simon <bad (AT) example (DOT) com> wrote:
Please give the table definition as shown by SHOW CREATE TABLE

the "parent_id=00000" looks fishy. Is this correctly quoted?

This has nothing to do with being "stuck". "Sending data" is the phase
of query execution where results are sent back to the client. If the
query does not require postprocessing of the result (such as external
sorting, grouping or distinct) then "sending data" is equivalent to
"fetching the requested rows" aka the full execution phase.

This then looks like the optimizer is switching between two execution
plans. One is fast and the other is slow (making it look "stuck").
If I had to guess, I would say that the slow plan uses an index on
(`date`) to save the extra sorting. This will happen if the requested
parent_id has many childs.

If you know good and bad parent_ids then run EXPLAIN for both values
and compare (or post here) the execution plans.

Also ANALYZE TABLE should be run regularly to make sure the index
statistics are up to date. Outdated statistics will fool the optimizer.


XL

On 2011/11/16 02:02 AM, Jason C wrote:
I'll try and look for Munin later and install it.
But it is not always that easy to manage such issues.

When the query is stuck I am not always able to jump on the server and
run queries to investigate what is going on.

That might be a bit difficult, I have so many queries happening all the
time and as you said, this is a band-aid solution.

I must admit, I never heard of a setting limiting the number of queries?
I didn't know there was such a limit.

I would be curious to know what settings you changes, even if only to
investigate my own settings and what those are for.

Many thanks

Simon

On 2011/11/16 10:19 AM, Axel Schwenke wrote:
no, it is not, the actual value varies all the time, it is an integer.

I used the word 'stuck', because that what it looks like to me.
A seemingly simple query takes a very, very long time to complete.


I have tried that and both are running the same plan, (is it possible
that they are not always running the same plan?).

I will create a cron job to analyse the table regularly and I'll post
back if this is still an issue.

Thanks

Simon

Simon <bad (AT) example (DOT) com> wrote:
How long is "very, very long" ?

Yes. Specifically InnoDB adds a random element. In order to be fast,
InnoDB "dives" a certain number of times into each index tree at random
positions and extrapolates from what it finds. This process can be
tuned. RTFM.

You mentioned "the table is locked" earlier, which would point to
MyISAM. In general MyISAM is better when it comes to stable statistics.
However ANALYZE TABLE is more costly for MyISAM (and locks the table).

With MyISAM you should also check for table fragmentation and run
OPTIMIZE TABLE if needed. Fragmentation is often seen for tables used
to implement a queue or sessions with limited life time.

OK. Frequency of this job depends on how many rows are modified in the
table. Per rule-of-thumb you should analyze if 25-50% of rows have been
modified. For InnoDB this is a cheap operation, so you can run this
often. RTFM on "table maintenance"


XL

On 11/15/2011 5:17 AM, Simon wrote:
If the index has date in ASCending order, it's not helping you much.
Perhaps related to the amount of data it's returning? Unless the index
is on date DESC, the result set will have to be sorted; if the result
set is large, that could take a while.

Hum, I didn't know you could do that, sort an index ASC/DESC, how would
you do it?

I am not a database developer, but I am sure the engine can use the
index ASC or DESC?

I don't even know what the default it.
I have it as "KEY `ndex_1` (`parent_id`,`draft`,`deleted`,`date`)"

Simon

On 2011/11/16 02:54 PM, Axel Schwenke wrote:
I usually kill it after ~300 seconds, and those are the ones I see.
I don't know how long others actually run for.

Yes, sorry I didn't mention it, it is a MyISAM table.

I do optimize the tables from time to time, I have jobs running and when
the overhead is above 1Mb, I optimise the table.

I have found that optimising it often is faster, Maybe I should analyse
it at the same time.

Rows are modified often, (but not as often as some database I have seen).
This is not a small database, but I wouldn't call it large either, (3Gb).

The table has 200K rows with about 300 new ones a day and ~500 others
updated.

Thanks

Simon

Simon <bad (AT) example (DOT) com> wrote:
I see

No need. OPTIMIZE includes ANALYZE

Oh. Some people has this much RAM. On the graphics card.

Then you shouldn't need to optimize/analyze more often than weekly.


XL