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.
Mike
============================
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.
..
ENGINE=InnoDB DEFAULT CHARSET=utf8;
..
How many indexes does the table have and how many rows are in the table?
CREATE TABLE `Claude` (
`metaName` varchar(50) DEFAULT NULL,
`metaId` int(10) NOT NULL,
`oldMetaName` varchar(40) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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 insert.
just do a insert into Claude (metaName, metaId, oldMetaName) values
('$metaName','$metaId ','$oldMetaName')
Tables empty when I start, cos I do a delete from Claude, and about 100k rows
when finished.
Any thoughts?
Syd
