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
Solved

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A procedure for exporting installed hotfix details of remote computers using powershell
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

809 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