MySQL: Performance issues in heavy multi-threaded environment

multi-threadMySQLoptimization

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.

Best Answer

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
Related Question