MySQL: Performance issues in heavy multi-threaded environment

Question

I have 23 of these tables in one database:

CREATE TABLE foo1 (
 DateTime datetime NOT NULL,
 BidOpen decimal(11, 5) NOT NULL,
 BidHigh decimal(11, 5) NOT NULL,
 BidLow decimal(11, 5) NOT NULL,
 BidClose decimal(11, 5) NOT NULL,
 AskOpen decimal(11, 5) NOT NULL,
 AskHigh decimal(11, 5) NOT NULL,
 AskLow decimal(11, 5) NOT NULL,
 AskClose decimal(11, 5) NOT NULL,
 Volume decimal(8, 2) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE foo1 ADD UNIQUE INDEX AskHigh (DateTime, AskHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX AskLow (DateTime, AskLow);
ALTER TABLE foo1 ADD UNIQUE INDEX BidHigh (DateTime, BidHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX BidLow (DateTime, BidLow);
ALTER TABLE foo1 ADD UNIQUE INDEX DateTime (DateTime);

When running this query in ~70 threads
SELECT MIN(DateTime) FROM fooXY WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444;
almost each needs ~2 seconds for ~225k rows.

The issues is only when these queries are ran multi-threaded.
Single execution is very fast and as expected.

Environment:

• Server has 4GB RAM
• MySQL 8.0.15
• 8 cores
• key_buffer_size == 268435456
• innodb_thread_concurrency = 64
• innodb_buffer_pool_size = 3133495296 [e.q. 75% of available RAM]
• MySQL is using all of the available cores.

My question:

How can be this query optimized/rewritten so there is no performance issue?

---

Update:
As requested, the result of EXPLAIN:

id 1 
select_type SIMPLE 
table foo1
partitions (null) 
type range 
possible_keys PRIMARY 
key PRIMARY 
key_len 5
ref (null) 
rows 113400 
filtered 33.33 
Extra Using where

The result of the suggestions in the chat or the answer:

Suggestions by user ypercube:

• I changed the DateTime index to PRIMARY KEY.
• Changing MIN() to ORDER BY DateTime LIMIT 1 fixed the performance issues.. The job needs now 50% of the time.

Suggestions by user Rick James:

• The other indexes - apart from PRIMARY KEY are indeed not needed. The deletion of them also shrunk the database by almost 50%. This alone didn't fix the issue.
• Changing the pool size to 50% of the available RAM. I don't see any change in comparison to 75% so I leave the 50% in order to avoid/minimize any possible swapping.

• Peter VARGA: @ypercubeTM Question updated in order to answer all your comments. PS: Can yo clean up a bit your comments? Otherwise SO complaints we should start a chat... Thx
• Peter VARGA: Let us continue this discussion in chat.
• Bill Karwin: FYI key_buffer_size is irrelevant for InnoDB tables. That buffer is used only for indexes of MyISAM tables.

Answer 1

First, some cleanup. Then I will provide the solution.

First, the UNIQUE(DateTime) obviates the need for any of the other unique keys. They could be plain (non-unique) indexes.

Second, you have not specified a PRIMARY KEY for your InnoDB table. This is naughty, but not the end of the world. I recommend you promote UNIQUE(DateTime) to be PRIMARY KEY(DateTime).

Third; once you have done that, the rest of the indexes (plain or unique) are useless; get rid of them.

The query is a tough one to optimize -- it needs, shall we say, a "2-dimensional" index. But indexes are only 1D. WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>999999 would probably involve scanning all the rows with a date after the given one if an index starting with DateTime were used.

So, to make that variation run faster, you would need an index starting with AskHigh. But then that is essentially useless if you ask AND AskHign > 0.0001.

PARTITIONing won't help because of the same issues -- pruning partitions with low values of DateTime or AskHigh is no better than having an index to avoid it.

With only 4GB of RAM, 3G is too much for the buffer_pool. Lower it to 1500M. Swapping is terrible for performance of MySQL.

For further discussion, please provide

EXPLAIN SELECT ...
the size of the table

• Peter VARGA: I understand that I did the requested cleanup before you provide the solution. Now it works because I eliminated MIN() but I would like to see the solution with MIN().

Answer 2

SELECT MIN(DateTime) 
FROM foo1 
WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444;

This query needs to find all records where the expression in the WHERE-clause it true, resulting in a growing number of records when date is added to the table.

SELECT DateTime
FROM foo1 
WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444
ORDER BY DateTime LIMIT 1

This query need to just find the first record that is found based on the index, and can ignore all other records.

In this DBFIDDLE you can see the diffence the first query scans 259 records, the second query just 1.