I have a lot of tables, and not all of them are filled
equally.
Inserts to tables that have a lot of entries(see the
count below), take a long time (about .06 secs on the
average in mysql, over 0.09-0.1 in DBI), for example
mysql> INSERT INTO T1 VALUES
('3CCF571C1A881188010403000002','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.06 sec)
inserts into tables like T2 which have few elements
take a much shorter time:
mysql> INSERT INTO T2 VALUES
('3CCF571C1A881188010403000002','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM T2;
+----------+
| COUNT(*) |
+----------+
| 509 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM T1;
+----------+
| COUNT(*) |
+----------+
| 782910 |
+----------+
1 row in set (7.43 sec)
Of course DELETES are similar(actually much worse with
a where), but deletes are not an issue here
mysql> DELETE FROM T1 WHERE date ='2002-09-01';
Query OK, 1 row affected (40.19 sec)
mysql> DELETE FROM T2 WHERE date ='2002-09-01';
Query OK, 1 row affected (0.24 sec)
I presume the problem is that in the table definitions
I have a couple of unique constraints and
the INSERT time difference is due to mysql
checking all entries in the table for a unique
violation, hence the insert would be slow:
CREATE TABLE T1 (
corrno varchar(43) default NULL,
a varchar(30) default NULL,
b varchar(30) default NULL,
c varchar(30) default NULL,
oc varchar(30) default NULL,
red varchar(30) default NULL,
sanum varchar(30) default NULL,
sbnum varchar(30) default NULL,
tnum varchar(30) default NULL,
o int(5) default NULL,
d int(5) default NULL,
cic int(6) default NULL,
date date default NULL,
start_time time default NULL,
value decimal(30,2) default NULL,
iamtoacm decimal(7,4) default NULL,
iamtoans decimal(7,4) default NULL,
reltorlc decimal(7,4) default NULL,
duration decimal(12,3) default NULL,
rcv int(3) default NULL,
rcvabn int(1) default '0',
loc int(3) default NULL,
reldir int(1) default NULL,
secode int(3) default NULL,
trans int(3) default NULL,
tc int(3) default NULL,
rsp 0 int(3) default NULL,
ccq int(5) default NULL,
ccr int(3) default NULL,
ccc int(3) default NULL,
ccp int(3) default NULL,
ccsan int(3) default NULL,
nata int(2) default NULL,
natb int(2) default NULL,
natc int(2) default NULL,
natocn int(2) default NULL,
natredir int(2) default NULL,
natsccpa int(2) default NULL,
natsccpbint(2) default NULL,
nattrue int(2) default NULL,
acat int(2) default NULL,
bcat int(1) default NULL,
upi char(1) default NULL,
upp int(3) default NULL,
acind char(1) default NULL,
inter int(1) default NULL,
ech int(1) default NULL,
length int(3) default '0',
file varchar(30) default NULL,
link varchar(8) default NULL,
fileind int(11) default NULL,
unicor varchar(100) NOT NULL default '',
stp int(1) default '0',
alarm int(1) default '0',
UNIQUE KEY unicor (unicor),
UNIQUE KEY corrno (corrno)
) TYPE=InnoDB;
The question is what can be done about this?
I guess getting rid of the unique constraints
would help, but then I would be getting some
unwanted duplicates. I should add that currently
I need to do about 100,000
inserts/10 minutes and the system is too slow for this
as it is, while it used to be very fast. So, I am
looking fro what can be done to avoid performance
degradation
as the tables fill.
The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
machine but the inserts are done from a client(a 1000
PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
# Example mysql config file for very large systems.
> #
> # This is for large system with memory of 1G-2G where
> the system runs mainly
> # MySQL.
> #
> # You can copy this file to
> # /etc/mf.cnf to set global options,
> # mysql-data-dir/my.cnf to set server-specific options
> (in this
> # installation this directory is /var/lib/mysql) or
> # ~/.my.cnf to set user-specific options.
> #
> # One can in this file use all long options that the
> program supports.
> # If you want to know which options a program support,
> run the program
> # with --help option.
>
> # The following options will be passed to all MySQL
> clients
> [client]
> #password = your_password
> port = 3306
> socket = /var/lib/mysql/mysql.sock
>
> # Here follows entries for some specific programs
>
> # The MySQL server
> [mysqld]
> port = 3306
> socket = /var/lib/mysql/mysql.sock
> skip-locking
> set-variable = key_buffer_size=500M
> set-variable = key_buffer=100M
> set-variable = max_allowed_packet=1M
> set-variable = table_cache=512
> set-variable = max_connections=10
> set-variable = tmp_table_size=400M
> set-variable = sort_buffer=2M
> set-variable = record_buffer=2M
> set-variable = thread_cache=8
> set-variable = thread_concurrency=4 # Try number of
> CPU's*2
> #set-variable = myisam_sort_buffer_size=64M
> set-variable = myisam_sort_buffer_size=4M
> innodb_data_home_dir=
> #and then use absolute file paths
>
#innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdat
>
a3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdat
> a7:2000M;ibdata8:2000M
>
innodb_data_file_path=/var/lib/mysql/ibdata1:2000M;/var/
>
lib/mysql/ibdata2:2000M;/var/lib/mysql/ibdata3:2000M;/va
>
r/lib/mysql/ibdata4:2000M;/var/lib/mysql/ibdata5:2000M;/
>
var/lib/mysql/ibdata6:2000M;/var/lib/mysql/ibdata7:2000M
> ;/var/lib/mysql/ibdata8:2000M;/usr/ibdata/ibdata9:2000
M;
> /usr/ibdata/ibdata10:2000M;/usr/ibdata/ibdata11:2000M;
/u
>
sr/ibdata/ibdata12:2000M;/ibdata/ibdata13:2000M;/ibdata/
>
ibdata14:2000M;/ibdata/ibdata15:2000M;/var/lib/mysql/ibd
>
ata16:2000M;/var/lib/mysql/ibdata17:2000M;/var/lib/mysql
> /ibdata18:2000M;/var/lib/mysql/ibdata19:2000M;/var/lib
/m
> ysql/ibdata20:2000M;/var/lib/mysql/ibdata21:2000M
> #;/usr/ibdata/ibdata9:2000M;/usr/ibdata/ibdata10:2000M
> ## Comment next line if you do not need recovery(the
> hostname-bin.xxx files)
> #log-bin
> ##
> server-id = 1
> #set-variable = open-files-limit=8192 #ulimit is
> 1024,hard 8192
> set-variable = innodb_buffer_pool_size=1100M
> # so that innodb_buffer_pool
> size+key_buffer+max_connections*
> (sort_buffer+record_buffer+2M)=1100+100+10*(2+2+2)
> =1260<2000M=RAM
> set-variable = innodb_additional_mem_pool_size=700M
> set-variable = innodb_log_file_size=1000M
> set-variable = innodb_log_buffer_size=20M
> # helps for large transactions
> # Uncomment the following if you are using BDB tables
> #set-variable = bdb_cache_size=384M
> #set-variable = bdb_max_lock=100000
>
> # Point the following paths to different dedicated
disks
> #tmpdir = /tmp/
> #log-update = /path-to-dedicated-directory/hostname
>
> [mysqldump]
> quick
> set-variable = max_allowed_packet=256M
>
> [mysql]
> no-auto-rehash
> #safe-updates # Remove the comment character if you
> are not familiar with SQL
>
> [isamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [myisamchk]
> #set-variable = key_buffer=256M
> #set-variable = sort_buffer=256M
> #set-variable = read_buffer=2M
> #set-variable = write_buffer=2M
> set-variable = key_buffer=25M
> set-variable = sort_buffer=25M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [mysqlhotcopy]
> interactive-timeout
> ------------------------------------------------------
=====================
Inserts are done via DBI; the relevant routine is below:
Basically three things are done:
1)an insert via $sth
in the 'storage' tables
2) an insert into a summary table, i.e.
we check if summary data for one of the values in the
entry exists (I think the fastes way is via
a SELECT date from tablename WHERE date=$date LIMIT 1;
rather via a count which needs to find all occurences
If there is already an entry, we use an undate handle
else an insert handle
sub dumpknown_out{
my $tablname=shift;#table in which to insert,only used
for diagnostics
my $ansc=shift; #a variable to decide on what to compute
#and insert
my $dbh=shift; #needed for the commit;
my $sth=shift; #statement handle for insert
my $action1=shift;
my $action2=shift;
my $ndestact=shift;
my $cntprep=shift;
my $sthupdate=shift;
my $sthsumins=shift; my $sthsumtst=shift;
my $outincupdate=shift; my $outincins=shift; my
$outinctst=shift;
my $dest=shift; my $finaldest=shift;
my $relpos=shift; my $locpos=shift;
my $rmask=shift;
#die "unknown $tablname\\n" unless
($tablname=~/CDR_OUTGOING_(.*)_\\d/);
#my $dest=$1;
my @values=();
my $redirectmark=0;
#my $ansc=0;
#$ansc=1 if $tablname=~/_1$/;
$redirectmark=1 if($_[4]=~/\\w/ or $_[5]=~/\\w/);#$CB_OCN
or CB_REDIRECTINGNUM
my $count=0;
my $destcnt=0;
my $flag=0;
foreach my $ii(@_){#foreach field
unless( $rmask->[$count]){$ii=0 unless $ii;}
push @values,$ii;
$count++;}
eval {
$destcnt += $ndestact->execute($values[1],$dest);
$sth->execute(@values) ;
$cntprep->execute($values[1]);my $countq=$cntprep-
>fetchrow_array();
unless($countq){
$action1->execute($values[1],1,$values[14],$values
[18],$ansc,1,$values[53],$redirectmark) or die "could
not execute action countq= $countq anum=$values[1]
value= $values[14] dur= $values[18] ansc=$ansc
alarm=$values[53] redirectmark=$redirectmark
table=$tablname values=@values er is $DBI::errstr\\n";
}#unless countq
else{
##print "updating NDEST\\n";
#update Ndest if necessary
$action2->execute($ansc,$values[14],$values
[18],$destcnt,$values[53],$redirectmark,$values[1]) or
die "could not execute action2 countq=$countq
$DBI::errstr\\n";
}#
$dbh->commit;
};#end of eval
if ($@) {$dbh->rollback; $flag=1;
print main::DUMP "Transaction @values aborted
because $@"; #keep temporarily
# add other application on-error-clean-up
code here
}
else{# initiate or update SUMMARY REPORT
# does this date, hour, type, link exist?
my $cnt=0;
my $time=substr($values[13],0,2) .':00';
$sthsumtst->execute($values[49],'OUTGOING',$values
[12],$time,$dest);
#"SELECT COUNT(*) FROM SUMMARYDATA WHERE link=? AND
type=?
# AND date=? and time=? ");
my @returned=(0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0); #132 0's
unless ($ansc){
$returned[$relpos+55]=1 if $relpos=~/\\d/; #release cause
$returned[$locpos+124]=1 if $locpos=~/\\d/; #location
}
# now find the location of the duartion interval
else{
$outinctst->execute($dest, $values[49], $values[12]);
#add to income
if( $outinctst->fetchrow_array)
{ #update
$outincupdate->execute($values[14], $dest, $values[49],
$values[12]);
}else{#initialize
$outincins->execute($dest, $values[49], $values
[12],1,$values[14] ); }
my $dur=int($values[18] /10);
if($dur <1){$dur=0;} #count negative durations as 0
$returned[27]= $values[18];# this is sumcount
if($dur < 25){$returned[$dur]=$values[18]; $returned
[$dur+28]=1;}
elsif($dur >30){$returned[26]=$values[18]; $returned[54]
=1;}
else{#in 250-300
$returned[25]=$values[18]; $returned[53]=1;}
}#else ansc=1
eval{#now for SUMMARY DATA
my $iam1=1000 * $values[15]; my $iam2=1000 * $values
[16];
if($sthsumtst->fetchrow_array){
#&reportstd(\\@returned );
$sthupdate->execute($ansc,$iam2,$iam1,@returned,
$values[49],'OUTGOING',$values[12],$time ,$dest );
}else{#initialize
$sthsumins->execute($values
[49],'OUTGOING', $dest ,$finaldest,
$values
[12],$time,1,$ansc,$iam2,$iam1,@returned);
}
$dbh->commit;};#end of 2nd eval
if ($@) {$dbh->rollback;
print main::DUMP "SUMMARYDATA Transaction @values
aborted because $@"; #keep temporarily
# add other application on-error-clean-up
code here
}
}#end of else initiate or update
#if($flag){print main::DUMP "failed with @values\\n";}
}#end of S/R dumpknown_out
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
