Solved

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

Posted on 2012-04-12
4
567 Views
Last Modified: 2012-04-25
hi
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 .
0
Comment
Question by:fosiul01
  • 2
4 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 400 total points
Comment Utility
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.
0
 
LVL 29

Author Comment

by:fosiul01
Comment Utility
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

or

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


about optimized that query : is it not Developers job ?? or as as DBA you have to do that aswell ??
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 400 total points
Comment Utility
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.
0
 
LVL 20

Assisted Solution

by:Muhammad Wasif
Muhammad Wasif earned 100 total points
Comment Utility
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).
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now