5

I have Installed MySQL Server 5.6.23 on Centos server and the configuration of the server is

16 GB RAM 8 Core CPU op-mode(s): 32-bit, 64-bit

When I check the status of the server it is showing me Queries per second avg: 22112.191

enter image description here

Still my server is running fine and there is no issue in read & write operation but I would like to know what is the maximum Queries per second my server can handle and what can I do to reduce it.?

Thanks in advance.

2 Answers 2

8

22K QPS is nice; I rarely see that high a number on a production system. You must be doing rather trivial queries. (In a quick survey, 7K is the 90th percentile.)

Do you use a lot of Stored Routines? What is your Questions/Uptime?

It is unlikely that a high query rate will "crash" MySQL. It can slow things down to the point where it feels "crashed".

How many active connections do you have? When SHOW PROCESSLIST show more than a few dozen queries running (not "Sleep") at a time, you are threatening to get in this overburdened situation.

There have been benchmarks (carefully designed) that registered more than a million queries per second. I could devise a situation that could saturate all the system resources, yet achieve only 1 query per minute. Those extremes indicate that there are a lot of factors going on.

3
  • 1
    Thanks for your reply., Yes we are using Stored Routines for most of the cases, Question: 21244778510 & Up Time: 1293967. In my process list I found only 40 connection and most of them are in sleep state Jun 30, 2016 at 6:57
  • 1
    thanks. Can you provide more details about your statement "(In a quick survey, 7K is the 90th percentile.)" Our DBs (DBaaS) running at 1K QPS. Does that mean we have small deployments?
    – Sybil
    Aug 14, 2017 at 10:03
  • 1
    @FyodorGlebov - 1K QPS is about the 80th percentile. That is, your system is busier than most of the 116 that I polled. As for "small", there are many other factors -- dataset size, complexity of queries, etc. 1K may or may not be so busy that you are having performance problems. For more analysis, see this .
    – Rick James
    Aug 14, 2017 at 13:03
1

There's no silver bullet to tell you how many queries per second can you process. You can set up different benchmarks to try to estimate. A 'good enough' approach is to register the queries with tcpdump and replay them from external server(s) from multiple parallel threads. You can also play with the concurrency. Register the query response time and plot it as the function of queries / second. When response time is above acceptable threshold that's your maximum queries / seconds for specific concurrency level. I wrote a script some time ago to do the same thing with HTTP requests (the collection is not part of it) and report maximum requests / second: https://github.com/charlesnagy/web-toolkit/blob/master/captest/wt-capacity-test.py It shouldn't be hard to translate to mysql queries.

If you use replication to balance reads you can also start reducing the number of slaves until the query response time is above the threshold and check then the current query rate is your limit.

You can only reduce the number queries if you change the application that sending those queries. Introducing caching, rewriting queries, etc. There are plethora of options but that really depends on your case. Maybe it's completely valid from your application to send that amount of queries and you just need to scale up or out your infrastructure.

4
  • Thanks for reply, I just wanted to know did these much query can crash my database any time due to high number of query per second.? Jun 15, 2016 at 12:50
  • @user2173561 maybe most of your queries are really fast. Try checking show processlist; periodically during different times of day to see if there are many concurrent connections or even many queries running at the same time. If it stays relatively empty then it should be no problem.
    – jkavalik
    Jun 15, 2016 at 19:19
  • If you have monitoring then keep an eye on the thread_running status variables. This is the concurrent connections @jkavalik mentions. If this value doesn't spike and stays under a reasonable level (the same order of magnitude as CPU cores you have) you will have no problem. Jun 16, 2016 at 10:26
  • 40K opens in 2 hours is very high. SHOW VARIABLES LIKE '%open%' for further advice.
    – Rick James
    Jun 27, 2016 at 20:56

Your Answer

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

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