List:General Discussion« Previous MessageNext Message »
From:Brad Heller Date:August 11 2013 6:22pm
Subject:Re: Concurrent read performance problems
View as plain text  
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