InnoDB are "supposed" to be faster at Inserts and updates than MyISAM.
However, my dealings with InnoDB have made me think that unless you _need_
transactions, foreign keys, etc, MyISAM is still the way to go for speed on
large tables.
If you can go MyISAM (you may need that stuff), fixed width tables will
insert and update faster than variable width tables. You could turn all
those varchars into chars. There will be an increase in data size.
As for the delete, all you need is a key on the date column. That will
speed that up.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 22, 2002 4:22 AM
Subject: Performance issue: slow inserts
|
| 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
|
|
---------------------------------------------------------------------
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
