Johan, your suggestion to tweak max_heap_table_size and tmp_table_size fixed the issue. Bumping them both to 512MB got our performance back on-par. I came up with a way to avoid the contention using a complex set of temp tables, but performance was abysmal.
By reverting to the more straight-forward query with the subselect as well as tweaking the max_healp_table_size and tmp_table_size I saw no resource contention causing slowdowns, as well as a 12x performance boost. Thanks for your help! *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe <http://www.twitter.com/bradhe> | @cloudability<http://www.twitter.com/cloudability> On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman <vegiv...@tuxera.be>wrote: > True, which is why I said I suspected file-based sort :-) At one million > rows, that seems to be an accurate guess, too. Still on the phone, though, > and in bed. I'll read the thread better tomorrow, but you might get some > benefit from cutting out the subselect if that's possible. > > If you have plenty of memory, have a look at the max_temp_table_size and > max_heap_table variables, too; those decide when the sort goes to disk. > > > Johnny Withers <joh...@pixelated.net> wrote: >> >> Just because it says filrsort doesn't mean it'll create a file on disk. >> Table schema and full query would be helpful here too >> >> http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ >> On Aug 11, 2013 1:28 PM, "Brad Heller" <b...@cloudability.com> wrote: >> >> Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like >>> there is a lot of filesort goin' on here. Also note that I'm only using the >>> first two fields of the covering index (intentionally). >>> >>> >>> +------+-------------+------------------------+-------+ >>> ------------------------------ >>> +----------------------------+---------+ >>> ------------------------------ >>> +---------+ >>> ------------------------------ >>> + >>> | id | select_type | table | type | >>> possible_keys >>> | key | key_len | ref >>> | rows | Extra >>> | >>> >>> +------+-------------+------------------------+-------+ >>> ------------------------------ >>> +----------------------------+---------+ >>> ------------------------------ >>> +---------+ >>> ------------------------------ >>> + >>> | 1 | PRIMARY | <derived2> | ALL | NULL >>> | NULL | NULL | NULL >>> | 1004685 | Using temporary; Using >>> filesort | >>> | 2 | DERIVED | accounts | range | >>> PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | >>> NULL | 3 | Using >>> where; Using index; Using temporary; Using filesort | >>> | 2 | DERIVED | facts | ref | covering >>> | covering | 4 | >>> facts.accounts.id | 334895 | Using >>> where >>> | >>> >>> +------+-------------+------------------------+-------+ >>> ------------------------------ >>> +----------------------------+---------+ >>> ------------------------------ >>> +---------+ >>> ------------------------------ >>> + >>> >>> >>> *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | >>> Skype: >>> brad.heller | @bradhe <http://www.twitter.com/bradhe> | >>> @cloudability<http://www.twitter.com/cloudability> >>> >>> >>> On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman <vegiv...@tuxera.be >>> >>>> wrote: >>>> >>> >>> On my phone now, but it smells of file-based sorting, making disk access >>>> the bottleneck. Can you provide the explain? >>>> >>>> >>>> Brad Heller <b...@cloudability.com> wrote: >>>> >>>>> >>>>> Hey list, first time posting here so apologies if this is the wrong >>>>> >>>> forum >>> >>>> for this but I'm really out of options on how to solve this problem! >>>>> >>>>> *Short version:* >>>>> >>>>> >>>>> 1. High concurrent reads, performing the same well-indexed query type to >>>>> the same two tables. >>>>> 2. No additional traffic at all--just reads from these two tables. No >>>>> >>>>> >>>>> writes anywhere. >>>>> 3. Very fast (sub-second) when server is only servicing one request at a >>>>> time. >>>>> 4. Very slow (30s+) when server is servicing 2-3 requests at a time. >>>>> >>>>> Questions: >>>>> >>>>> 1. What resource is under contention for servicing read-only queries if >>>>> >>>> you >>> >>>> have a large buffer pool and a plenty-big thread cache? >>>>> 2. What parameters can I tune to increase concurrent reads to these two >>>>> tables? >>>>> >>>>> *Long version:* >>>>> >>>>> >>>>> I've got a MySQL server that has only about 50 connections open to it at >>>>> any given time. It basically only has one OLAP q >>>>> uery >>>>> type being ran against >>>>> it that amounts to something like this: >>>>> >>>>> S >>>>> ELECT >>>>> (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) >>>>> FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; >>>>> >>>>> These queries are well indexed and run very well >>>>> >>>> individually--sub-second, >>> >>>> usually even faster. When I run a few of these queries simultaneously >>>>> >>>> (2-3 >>> >>>> on my laptop, 6-7 on our production boxes) performance grinds to a halt: >>>>> Consistently about 30 seconds to service a query. >>>>> >>>>> I >>>>> deally, >>>>> I'd like to be able to run 50+ of these queries concurrently. >>>>> >>>>> I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on >>>>> >>>> both >>> >>>> machines. I've tried tweaking the following my.cnf parameters to be >>>>> >>>> higher: >>> >>>> >>>>> thread_concurrency = 20 >>>>> thread_cache_size = 340 >>>>> innodb_buffer_pool_size=2G >>>>> >>>>> A few other my.cnf parameters that I have set: >>>>> >>>>> innodb_file_format=Barracuda >>>>> >>>>> innodb_file_format_max=Barracuda >>>>> innodb_file_per_table=1 >>>>> skip-external-locking >>>>> innodb_log_files_in_group=2 >>>>> innodb_log_file_size=2000M >>>>> max_allowed_packet=64M >>>>> >>>>> Thanks in advance, >>>>> >>>>> Brad Heller >>>> >>>> >>>> >>>> -- >>>> Sent from Kaiten Mail. Please excuse my brevity. >>> >>> >>> >>> > -- > Sent from Kaiten Mail. Please excuse my brevity. >
