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

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