MySQL Forums
Forum List  »  Performance

Re: Monitoring large resultsets
Posted by: Rick James
Date: July 12, 2012 05:49AM

Not good! --
> | query_cache_size | 2147483648 |
When a write to a table occurs, it has to scan through all that to find any cached entries for that table. That takes a noticeable amount of time. I recommend no more than 50M for that setting.

> created like '%2012-07-04%'
That's a terrible way to test a DATETIME.
This is better, but it includes midnight at both ends:
> created >='S' AND created<='S'

Do no hide field inside a function call (at least if you want an index to be used):
> AND UNIX_TIMESTAMP(publish_up)<=UNIX_TIMESTAMP(NOW()) )
Probably this is equivalent:
AND publish_up <= NOW()

> AND ( `state`='1' OR `state`='-1' )
-->
AND state IN (1, -1)

and  (
            (sm.sec_id=jos_content.sectionid
        and  jos_content.catid=cm.cat_id
        and  cm.cat_show_archive='1')
    OR  (jos_content.sectionid=0  and  jos_content.catid=0)
     )
That feels 'wrong'. The first part of the OR is JOINing the tables; the second part is ignoring such.

Please rewrite the query to use explicit JOINs (instead of 'commajoin'):
FROM a
JOIN b ON a.id = b.id
JOIN ...

The exercise of rewriting will
(1) make the query more readable
(2) may uncover a coding error.

There is no way to catch just bulky resultsets. The SlowLog is the best tool available.

How much RAM do you have?
http://mysql.rjweb.org/doc.php/memory

276qps -- moderately high. Once you fix some of the slow queries, other things will run faster. Note that you are using MyISAM (for all the tables?). This means that any write locks a table against all SELECTs.

The EXPLAIN is incomplete without SHOW CREATE TABLE for jos_ijoomla_archive_section_manager and jos_ijoomla_archive_categories_manager. It smells like those tables are not indexed, or not adequately indexed.

Please provide SHOW TABLE STATUS for the tables in question. The table you showed has a lot of MEDIUMTEXT/TEXT fields. Since the table is being 'scanned', it has to step over those fields (like cow patties) to find the rows desired.

One technique for improving things is to "vertically partition" a wide table (like jos_content) -- Meta data in one table (narrow, well indexed); bulky data in another table (with just an id). Then do a JOIN where one 'table' is a subquery finding the ids that are desired.

I agree that returning 14K rows with news articles does not make a lot of sense.

I find, in general, that third party software has not been tested for scaling.

Maybe adding this would help:
INDEX(created)

Options: ReplyQuote


Subject
Views
Written By
Posted
2046
July 10, 2012 10:26PM
1114
July 11, 2012 02:01AM
896
July 12, 2012 02:19AM
946
July 12, 2012 03:37AM
939
July 12, 2012 04:13AM
Re: Monitoring large resultsets
981
July 12, 2012 05:49AM
1113
July 12, 2012 07:02AM
932
July 13, 2012 07:50PM
918
July 14, 2012 02:43AM
900
July 12, 2012 08:13AM
897
July 12, 2012 08:55AM
980
July 12, 2012 12:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.