top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How to know the Average load per second when we insert data into MySQL table?

+2 votes
96 views

How I 'll check how many rows inserted into every second on an average in a table of MySQL database?

posted Sep 29, 2013 by Vivek Singh

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+2 votes

You can monitor it in real time by simply doing something like this

Count the number of rows
Wait x seconds
Count the number of rows, find delta y between two counts
rate = y/x

If you don't want to do it in real time, you can could parse the MySQL binary log to see how many insert statements were executed and obtain their timestamps.
Or you could give each row a timestamp, and then find the average insertion rate for any arbitrary period by counting the rows inserted in that period. For example, to get a count of rows inserted in last hour

SELECT count(*) AS inserted FROM table 
WHERE unix_timestamp(created) 
BETWEEN unix_timestamp()-3600 AND unix_timestamp();

Finally, if you want some general graphing of insertions (and other operations) you could use something like munin (though by default this would be tracking all inserts, not just those in a particular table).

answer Sep 29, 2013 by Satyabrata Mahapatra
Similar Questions
+1 vote

I have a table in Mysql DB like following

mysql> describe APPTBL;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| APP_ID      | int(11)     | NO   | PRI | NULL    | auto_increment |
| CREATE_DATE | date        | YES  |     | NULL    |                |
| CLOSE_DATE  | date        | YES  |     | NULL    |                |
| ACTION      | text        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

While Starting of App, i am inserting the app details with CREATE_DATE and when it is closing, i am updating its CLOSE_DATE. But in case if user is restarting the app, i want to update NULL in place of CLOSE_DATE column. Here is my query,

mysql> update APPTBL set CLOSE_DATE = NULL where APP_ID = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select * from APPTBL ;
+-----------+-------------+------------+------------+
|   APP_ID  | CREATE_DATE | CLOSE_DATE |   ACTION   |
+-----------+-------------+------------+------------+
|    1      |  2013-10-08 | NULL       | XXXXXYYYYY |
+-----------+-------------+------------+------------+
1 row in set (0.00 sec)

But why i am not getting any result in this??

mysql> select * from APPTBL where CLOSE_DATE = NULL;
Empty set (0.00 sec)

Also, as i am writhing a java program using this where

rs = st.executeQuery("update APPTBL set CLOSE_DATE = NULL where APP_ID = 1");
if(!rs.wasNull()){
   // Why it is coming here??
}
0 votes

How can I insert data into one table from two other tables where i have three tables namely users, role and userrole.
Now I want to insert the data into userrole table from users table and role table with a single statement.

+5 votes

How can I insert the content of excel file into MySQL Table, any pointer?

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...