10 inserts a sec into redhat box

--0-1058909565-1257452702=:88985
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

At 05:57 AM 11/5/2009, you wrote:=0A>Hello, I am currently inserting about =
10 rows (2 varchar 50 fields ) a sec =0A>into modern 4 processor RH 5.x bo=
x using perl Net::Mysql from another box =0A>of similar spec. This is rathe=
r less than expected. I suspect I =0A>could push the inserts from the sour=
ce box much faster than that. And =0A>that it is the target box that is the=
problem. Top reveals it has a load =0A>average of 2 to 3. How can I look i=
nto this performance problem? TIA Syd You should be getting inserts mu=
ch faster than that. Is the machine disk =0Abound or CPU bound? Are yo=
u using InnoDb or MyISAM tables? If you run the same test on the same =0Ama=
chine as the MySQL server, how fast is it then? If it is much faster then =
=0Athe problem is likely your network and you'll need to monitor the networ=
k =0Abandwidth. It could be a faulty NIC or cable. If it is the same s=
peed on the server box then the problem is with your =0Adatabase. You can o=
f course insert multiple lines with one Insert statement =0A(see manual) or=
use a Load Data Infile to load the data from a CSV file. =0AHow many index=
es does the table have and how many rows are in the table? =0AThe more inde=
xes then the slower the inserts. You could post your update =0ASQL statemen=
t and the table structure. Mike ==========
=================== Thanks Raj and =
Mike!=0AThis is what iostat looks like: $ iostat=0ALinux 2.6.18-128.1.=
10.el5 ( 11/05/09 avg-cpu: %user %nice %system %iowait %stea=
l %idle=0A 0.53 0.01 0.15 1.98 0.00 97.32 De=
vice: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn=0Aiss/=
c0d0 25.11 55.54 376.33 706674627 4788699936=0Aiss/c0d0=
p1 0.00 0.06 0.00 772662 2866=0Aiss/c0d0p2 =
25.11 55.48 376.33 705901765 4788697070=0Adm-0 =
5.19 10.23 39.02 130181410 496524528=0Adm-1 3=
5.42 43.61 279.33 554908708 3554371472=0Adm-2 0.=
08 0.82 0.45 10464234 5776760=0Adm-3 7.24=
0.81 57.53 10343778 732015712=0Adm-4 0.00 =
0.00 0.00 3288 8592 Not sure what this mean=
s but dont seem to be using much CPU < 10% normally.=0A..=0AENGINE=3DInnoDB=
DEFAULT CHARSET=3Dutf8;=0A.. How many indexes does the table have and=
how many rows are in the table? CREATE TABLE `Claude` (=0A `metaNam=
e` varchar(50) DEFAULT NULL,=0A `metaId` int(10) NOT NULL,=0A `oldMetaNam=
e` varchar(40) DEFAULT NULL,=0A ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8;=
Yup that's right no indexes, there will be more fields and more table=
s but there are no PKs or FKs - strange I know but this is the data I have =
to store and later fetch.=0Aperhaps MySql is doing a autoinc behind the sce=
nes? =0AProbably will need an index for fast retrieval, but not before=
I have arranged for a fast insert.=0Ajust do a insert into Claude (metaNam=
e, metaId, oldMetaName) values ('$metaName','$metaId ','$oldMetaName')=0A=
=0ATables empty when I start, cos I do a delete from Claude, and about 100k=
rows when finished. Any thoughts? Syd
--0-1058909565-1257452702=:88985--