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.

@ypercubeᵀᴹ 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
– Peter VARGA, Commented Feb 11, 2019 at 16:17
FYI key_buffer_size is irrelevant for InnoDB tables. That buffer is used only for indexes of MyISAM tables. — Bill Karwin
– Bill Karwin, Commented Apr 28, 2023 at 17:09

Rick James · Accepted Answer · 2019-02-12 04:17:49Z

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

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(). — Peter VARGA
– Peter VARGA, Commented Feb 14, 2019 at 12:43

Luuk · Accepted Answer · 2025-05-17 12:49:57Z

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.

Stack Exchange Network

MySQL: Performance issues in heavy multi-threaded environment

2 Answers 2

Hot Network Questions

MySQL: Performance issues in heavy multi-threaded environment

2 Answers 2

Related

Hot Network Questions