MySQL Forums
Forum List  »  Performance

INSERT performance
Posted by: Alan Stone
Date: December 11, 2004 08:33AM

Hi

Firstly, I'm running 4.1.7 on Win2K. 0.5Gb Memory, single 550Mhz PIII processor. Database access is via php/PEAR DB classes. Running apache 2.

I am seeing execution times increasing dramatically during insert operations on three linked tables. with only 1200 records i'm seeing 50 seconds plus to insert 100 records in each table. Is this normal?

The inserts are being done under LOCK TABLE ... for WRITE conditions to prevent other unsequenced inserts.

Code fragment follows...
$sql="INSERT INTO tblworkorder " .
"(prefix, workorderid, customerid, woidseries, returntype, symptomcode, technicianname)".
" VALUES ".
"('ACC',{$Woid},{$cid},{$Woidser},'AR',{$scode},'{$tname}'); \n";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
$sql="SELECT @@IDENTITY FROM tblworkorder;";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here

<<<<<<<<<CODE REMOVED>>>>>>>>

$popcsv = date("Y/m/d",fArrayItem($DateArray, $DateOffset));
$popdate = $popcsv . " " . fRandomTime();
$recdate = date("Y/m/d H:m:s");
$shipdate = $recdate;
$sql="INSERT INTO tblitem ".
"(workorderid,productcode,popdate,warrstatus,receivingdate,shippingdate,receiveditemcode) ".
"VALUES ".
"({$woid_inx},\"{$Accpartno}\", \"{$popdate}\", 1, \"{$recdate}\", \"{$shipdate}\",\"{$AccRIC}\"); \n";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
$sql="SELECT @@IDENTITY FROM tblItem;";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
<<<<<<<<<CODE REMOVED>>>>>>>>
$sql="INSERT INTO tblrepair".
"(itemid,repairsymptomcode,faultcode,keyrepair,partnumber,partreplaced) ".
"VALUES ".
"({$item_inx}, \"{$scode}\", \"{$AccFC}\", 1, \"{$Accpartno}\", 1)";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
_____________________________________________________________
End of CODE

my.ini settings are:

[client]

port=3306
[mysqld]
port=3306
basedir=E:/mysql/
datadir=E:/mysql/Data/
default-character-set=latin1
default-storage-engine=INNODB
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=8M
thread_cache=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
#skip-innodb
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8

Thanks

Alan

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERT performance
6699
December 11, 2004 08:33AM
2770
December 15, 2004 01:56PM
2560
December 20, 2004 12:58PM
2419
December 20, 2004 04:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.