List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:August 11 2013 6:57pm
Subject:Re: Concurrent read performance problems
View as plain text  
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" <brad@stripped> 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 <vegivamp@stripped
> >wrote:
>
> > On my phone now, but it smells of file-based sorting, making disk access
> > the bottleneck. Can you provide the explain?
> >
> >
> > Brad Heller <brad@stripped> 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.
> >
>

Thread
Concurrent read performance problemsBrad Heller10 Aug
  • Re: Concurrent read performance problemsJohan De Meersman11 Aug
    • Re: Concurrent read performance problemsBrad Heller11 Aug
      • Re: Concurrent read performance problemsJohnny Withers11 Aug
        • Re: Concurrent read performance problemsJohan De Meersman11 Aug
          • Re: Concurrent read performance problemsBrad Heller11 Aug
            • Re: Concurrent read performance problemsJohan De Meersman11 Aug
              • RE: Concurrent read performance problemsRick James12 Aug