[mailto:vegiv...@tuxera.be]
Sent: Sunday, August 11, 2013 2:16 PM
To: Brad Heller
Cc: Johnny Withers; MySQL General List
Subject: Re: Concurrent read performance problems
Good to hear. A word of warning, though: make sure you don't have more
connections allocating those buffers than your machine can
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
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).
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 b...@cloudability.com wrote:
Yes sorry,
True, which is why I said I suspected file-based sort :-) At one million rows,
that seems to be an accurate guess, too. Still on the phone, though, and in
bed. I'll read the thread better tomorrow, but you might get some benefit from
cutting out the subselect if that's possible.
If you have
Johan, your suggestion to tweak max_heap_table_size and tmp_table_size
fixed the issue. Bumping them both to 512MB got our performance back
on-par. I came up with a way to avoid the contention using a complex set of
temp tables, but performance was abysmal.
By reverting to the more
Good to hear. A word of warning, though: make sure you don't have more
connections allocating those buffers than your machine can handle memory-wise,
or you'll start swapping and performance will REALLY go down the drain.
A query/index based solution would still be preferred. Could you for
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
