• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

QPs rate and com_insert rate for a busy server(discussion)

i am just trying to understand.. how you measure a busy server..( i m not asking for any explanation)

example :

if I measure our server.
com_insert , I see in pick we get 90 to 100  insert/s

and Qps is :  300-320/s

till now we not having any performance problem..

but what i want know :
if any of you use a heavy mysql server( heavy load)
what is your insert rate (IOPS) ?? and Qps rate ??

if i use script, i can insert 5000 to 8000 insert/s
so that not not a  problem..

i just want to compare our loads with other people's heavy mysql server load

if you have heavy mysql server.. can you tell me
in pick time... how  busy is your server ??

(note : i know the performace depends on your query structure.. table structure so it will never be same for eveyr one)  
i just want to get a general idea .thats it ...

thanks for sharing your statictics .
  • 2
3 Solutions
Okay, just to satisfy your curiosity (since this is the second time you asked this after nobody responded to the first one): the database I manage averages around 700 queries per second.  I don't know it's peak because I've never bothered - I'm more concerned about making sure the queries are optimized, concurrency is handled well, the backups are good, among other things.  I'll worry about queries per second when the database is consistently utilizing at least 40% of cpu.  Or maybe I won't as many other factors will come to play.

BTW, while we're at it, my numbers are nothing compared to that of Facebook, which reported a peak of 13M queries per second back in 2010 (google about it).  But you know what, the numbers means nothing unless you know what infrastructure supports it, which of course I doubt Facebook will tell anyone of us.
fosiul01Author Commented:
HI thanks for response..
really appreciate ..

this 700 queries per second .

did you take that value from mysql command line ??

type mysql
theyn \s

it shows the QPs.. or did you calculate that with conventional method

(a) questions/uptime


selct + insert+...+...+/uptime ?

about optimized that query : is it not Developers job ?? or as as DBA you have to do that aswell ??
I just used \s - as I said above, I don't spend much time looking at it.

In my opinion, ensuring the queries are optimized is mainly a DBA job.  Of course developers are asked to keep performance in mind, but per my experience they will occasionally miss something, like a needed index or the fact that adding a seemingly redundant filter will actually make it faster, because it will make use of an already existing index.  As database utilization grows, there will be techniques that you can employ that only the DBA can suggest, like using partitioning and modifying the query to take full advantage of it, or creating an integer hash column that will be used for look-ups instead of directly querying via a long string column, because the index for the integer hash column is much smaller and the resulting query is much faster.  The DBA works with the developers on this.
Muhammad WasifCommented:
You also need to consider the number of queries in locked state if you really care about performance. This is another way to know whether the server is busy or not. I am serving 1000+ q/sec. With the script you cannot exactly measure the capacity of IPS unless you define multiple queries on different tables. Insert query on one table may take less time but another query on another table takes too long depending on the data and index size of that table.

Optimizing query requires both DBA and developer time especially when the queries are being generated dynamically. DBAs are always in a good position to recommend optimized solution. As the tables size grow, we may need to drop an index on one column and create on another column (it may vary).

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now