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: > >> > >> SELECT (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. > >> > >> Ideally, 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. > > >
