List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:August 11 2013 3:45pm
Subject:Re: Concurrent read performance problems
View as plain text  
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 query 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