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 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.
