1

Im working on a project for my company which sells huge amounts of weather data to customers through a Web API. As part of that API, there is a backend service which needs to fetch data from MYSQL database (ver 5.1.1, InnoDB). The data set can reach amount of 50 000 rows and 12 columns if interval is set to whole year, which is very common. MYSQL database runs on Red Hat Linux 64bit, 4 core server computer.

Query which calculates, for example, daily averages on set of 50000 rows executes in 3.5 sec. However, if i run 4 of those queries in parallel, execution time for each of them reaches average 8sec per query. Im also monitoring client connection on DB and I can clearly see there are 4 threads executing each query. CPU usage on server during execution goes from 0 to cca 50% usage.

My question is, why execution time rises if I run same query in parallel? Im I missing something or this is expected behaviour?

More server info: CPU: Intel Core I5-4440 @ 3.1 GHz (4 cores), Memory: 8 GB

EDIT: Increase of innodb_buffer_pool_size from 530 MB to 5.5 GB solved problem!

Improve this question

3 Answers 3

Reset to default
2

  • Upgrade to 5.5, then 5.6, then 5.7. You will be able to run more simultaneous queries at each upgrade. Do you really mean "5.1.1"? That was a pre-release for 5.1 from about 2005.

  • Use summary tables for old, static, data. (It is static after it is INSERTed, correct?) Perhaps summarizing (eg AVG, MAX, MIN) daily readings can be done just after midnight?

  • Use InnoDB, not MyISAM. Much better sharing of the table. (And it gets better with each version upgrade.)

  • innodb_buffer_pool_size should be about 5500M for your 8G machine. Is the dataset, including indexes, bigger than that? If so, get more RAM and/or RAID with striping and/or SSDs.

  • And Summary tables are the main cure for Data Warehouse performance issues. Further discussion . It's not uncommon to speed up queries 10-fold, especially I/O-bound ones.

"How does one solve..." -- Most applications are not doing lots of 3.5-second queries. They are doing 'point queries', which might be 3.5 milliseconds.

Improve this answer
4
  • Thank you for your answer. Since I dont have much experience in DBA, especially not in DB performance, this was an eye opening. PS: innodb_buffer_pool_size was set to 530MB, I guess this is the problem.
    – Tomislav Medved
    Oct 26, 2016 at 22:06
  • That is certainly something to change. And it may be the problem. If your data is smaller than 530MB, it may not help any. Or if all the queries are in a small part of the table (and you have adequate indexes), then it may not help. (Etc.) My suggestions are orthogonal and cumulative. Do what you can; come back for more help if you need.
    – Rick James
    Oct 26, 2016 at 22:17
  • Increase of innodb_buffer_pool_size from 530 MB to 5.5 GB solved problem!
    – Tomislav Medved
    Oct 27, 2016 at 7:54
  • Well, keep my list; you may outgrow things later. And you should plan for upgrading; it only get more difficult as time goes on.
    – Rick James
    Oct 27, 2016 at 15:56
0

Speculating, what seems like running in parallel in CPU (multiple threads), there could be only one drive holding the data. Multiple seek requests on the hard drive for data on one thread at the same time may cause I/O waits for other threads requesting data. If all the data is in-memory (e.g., cached) times may improve a lot more, but there could still be contention for accessing the same data (in memory) at the same time by multiple threads.

Improve this answer
1
  • How does one solve this kind of problem? I'm pretty sure there are a lot of cases in production where multiple cilients have to access same data in approximately same time. How those DBs handle that load? Would you recommend different kind of DB for this application?
    – Tomislav Medved
    Oct 26, 2016 at 20:03
0

While all the things mentioned above for general performance will help alleviate bottle necks, the main issue that you'll run into with many parallel requests that are not point requests is due to the standard mySQL thread pool scheduler...which is...not great for a large number of concurrent threads. If you find this behavior alot, try pool-of-threads thread scheduling. Link is below, testing it out is a simply affair, so try this before you spend hours refactoring things...might be a life saver.

https://www.percona.com/doc/percona-server/8.0/performance/threadpool.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.