Solved

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

Posted on 2012-04-12
4
574 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

624 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