Solved

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

Posted on 2012-04-12
4
572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 400 total points
ID: 37840442
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
ID: 37841395
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
ID: 37841448
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
ID: 37848195
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
rds question 5 40
Windows Server Event Log DSM Error-1000 10 34
Local admin account 3 43
How to use 2 ON statements in inner join 3 27
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

726 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