List:General Discussion« Previous MessageNext Message »
From:Sydney Puente Date:November 5 2009 8:25pm
Subject:10 inserts a sec into redhat box
View as plain text  
At 05:57 AM 11/5/2009, you wrote:
>Hello, I am currently inserting about 10 rows (2 varchar 50 fields ) a sec 
>into modern 4 processor RH 5.x  box using perl Net::Mysql from another box 
>of similar spec. This is rather less than expected. I suspect I 
>could  push the inserts from the source box much faster than that. And 
>that it is the target box that is the problem. Top reveals it has a load 
>average of 2 to 3. How can I look into this performance problem? TIA Syd

You should be getting inserts much faster than that. Is the machine disk 
bound or CPU bound?

Are you using InnoDb or MyISAM tables? If you run the same test on the same 
machine as the MySQL server, how fast is it then? If it is much faster then 
the problem is likely your network and you'll need to monitor the network 
bandwidth. It could be a faulty NIC or cable.

If it is the same speed on the server box then the problem is with your 
database. You can of course insert multiple lines with one Insert statement 
(see manual) or use a Load Data Infile to load the data from a CSV file. 
How many indexes does the table have and how many rows are in the table? 
The more indexes then the slower the inserts. You could post your update 
SQL statement and the table structure.

Thanks Raj and Mike!
This is what iostat looks like:

$ iostat
Linux 2.6.18-128.1.10.el5 (       11/05/09

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.53    0.01    0.15    1.98    0.00   97.32

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
iss/c0d0       25.11        55.54       376.33  706674627 4788699936
iss/c0d0p1      0.00         0.06         0.00     772662       2866
iss/c0d0p2     25.11        55.48       376.33  705901765 4788697070
dm-0              5.19        10.23        39.02  130181410  496524528
dm-1             35.42        43.61       279.33  554908708 3554371472
dm-2              0.08         0.82         0.45   10464234    5776760
dm-3              7.24         0.81        57.53   10343778  732015712
dm-4              0.00         0.00         0.00       3288       8592

Not sure what this means but dont seem to be using much CPU < 10% normally.

How many indexes does the table have and how many rows are in the table? 

  `metaName` varchar(50) DEFAULT NULL,
  `metaId` int(10) NOT NULL,
  `oldMetaName` varchar(40) DEFAULT NULL,

Yup that's right no indexes, there will be more fields and more tables but there are no
PKs or FKs - strange I know but this is the data I have to store and later fetch.
perhaps MySql is doing a autoinc behind the scenes?

Probably will need an index for fast retrieval, but not before I have arranged for a fast
just do a insert into Claude (metaName, metaId, oldMetaName) values ('$metaName','$metaId

Tables empty when I start, cos I do a delete from Claude, and about 100k rows when

Any thoughts?


10 inserts a sec into redhat boxSydney Puente5 Nov