We have some performance issues with one of our Test-Machines a MySQL 5.7.21 Standalone Server.

We have: 8 CPUs 16GB RAM (buffer_pool_size 5.6 G) and need to handle > 30 K Requests / minute.

When we run our tests the CPU workload raised to 100% and we got several issues (long query run time etc...)

What is your hardware recommendation for this workload? ty in advance br Andreas

  • How long is a piece of string? You hit a cpu limit but where else is the server hurting? RAM? I/O? Can you modify the code? Is the query just naturally cpu intensive? – Sir Swears-a-lot Mar 25 '18 at 8:49
  • Is that single thread/connection? Whats the transction time for lower query volumes? How much does each query change? How big are the tables its hitting is it all cached in memory? Is it only querying data or is it inserting and updating too? – Sir Swears-a-lot Mar 25 '18 at 8:53
  • Depending on all of the above the answer might be more CPU, more RAM , faster disk, different drive layouts, indexing or even table schema design. – Sir Swears-a-lot Mar 25 '18 at 8:57
  • @aeaeae Additional information request, please post in original question (or on pastebin.com). current complete my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; gathered just after your TEST has finished for basic tuning suggestions, you will probably be amazed. – Wilson Hauck Apr 3 '18 at 19:42
  • @aeaeae Any chance the requested information can be added to the Question with EDIT? If not, MODERATOR, please REMOVE the entire question, there is not much useful information in the Question. – Wilson Hauck Apr 9 '18 at 15:08

500 inserts per second is definitely into the area where out-of-the-box MySQL begins to have trouble.

Some things to do:

  • Don't trust benchmarks; use your code.
  • Combine one-row INSERTs into transactions. (Much of what is limiting you to 500qps is the transaction overhead.
  • "Batch" inserts -- 100-1000 rows per INSERT statement gives you 10x performance improvement.
  • Use LOAD DATA -- also 10x (maybe more).
  • Switch to SSDs -- perhaps 10x faster than spinning drives.
  • innodb_flush_log_at_trx_commit=2 -- much faster, but less secure in a crash.
  • Decrease the number of indexes, especially UNIQUE, on the table.
  • Do not use UUIDs.
  • Gather data some other way (eg, MyISAM table with no indexes), then batch INSERT...SELECT.... This has the added advantage of giving you a change to efficiently do ETL transformations.
  • My High Speed Ingestion technique. It involves ping-ponging between two staging tables and handles various variations (multiple connections, etc)
| improve this answer | |

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