At 05:53 AM 3/22/2002, you wrote: >Hi, I'm using a db with few tables, one of which is reported below: > > CREATE TABLE `keywords` ( > `keyword` varchar(128) NOT NULL default '', > `codice` varchar(16) NOT NULL default '', > `timestamp` int(11) NOT NULL default '0', > `soundekw` varchar(32) NOT NULL default '', > PRIMARY KEY (`keyword`,`codice`), > KEY `codice` (`codice`) >) TYPE=InnoDB | > > >My problem is that after having inserted more than 2-3 times roughly >700000 records in it, every operation such as > >DELETE from keywords; > or >SELECT count(*) from keywords; > >slows to a crawl: it takes 15 minutes or so to complete.
"Delete from Table" and and "Select count(*) from Table" works differently in InnoDb than MyISAM tables. With MyISAM both of these operations are instantaneous. With InnoDb they must delete each record individually or count each record individually and is much slower. If you need to delete all the rows from a large InnoDb table it is recommended that you drop the table and recreate it. (This is what "Delete from MyISAMTable" does behind the scenes.) How much faster are these operations after you re-import the data? Have you tried a show table status to determine how much data is actually being used? I noticed your configuration is set to "innodb_data_file_path = ibdata1:512M" and there is no reserve space. Granted you're probably using only 256MB of your 512MB, it is customary to define at least 2 file spaces as in: "innodb_data_file_path = ibdata1:512M;ibdata2:512M" You could as a last resort, dump all your data using MySQLDump, then shut down the MySQL server, backup your ibdata files to another drive, then drop your InnoDb tables (to get rid of the MySQL FRM files), drop the database, then delete the ibdata files, DEFRAG your drive, then recreate them from scratch using the 2 filespaces outlined above. Technically you don't have to blow away your database before expanding the InnoDb files, but starting from scratch will eliminate any prior problems with the disk space. One thing to watch out for with InnoDb is you can't drop a database without first dropping all the tables, otherwise the table space is still there. I don't know if this still applies to the latest version or not but it was going to be fixed sometime in the future. So starting from scratch after your volume has been defragged may speed things up for you. It will take you an hour or two, but then you will be starting from a clean slate. Now unless this table is going to have a lot of updates from a large number of users at the same time, you might be happier with a MyISAM table. InnoDB is great for multiple updates because of its record locking, but is much slower for updates from a single user and selects from multiple users. And from what I can remember, MyISAM tables don't require table locks when inserting new data. You only need table locks when deleting or updating existing rows. So if you're not doing a lot of deletes or updates, MyISAM might be a better choice. Just something to consider. Brent >Sometimes I even have to dump, zap, recreate and reimport the db. > >My system is redhat linux 7.2, Mysql-Max 3.23.46 (official rpm) on a Dual >PIII 550, 256 MB ram, Raid 1 on a Mylex Raid card and the following >configuration for Innodb: > > >innodb_data_home_dir = /home/share/innodb/ >innodb_data_file_path = ibdata1:512M >set-variable = innodb_mirrored_log_groups=1 >innodb_log_group_home_dir = /home/share/innodb/log/ >set-variable = innodb_log_files_in_group=3 >set-variable = innodb_log_file_size=5M >set-variable = innodb_log_buffer_size=8M >innodb_flush_log_at_trx_commit=0 >innodb_log_arch_dir = /home/share/innodb/log/ >innodb_log_archive=1 >set-variable = innodb_buffer_pool_size=16M >set-variable = innodb_additional_mem_pool_size=2M >set-variable = innodb_file_io_threads=4 >set-variable = innodb_lock_wait_timeout=50 > >I know this configurations doesn't look aggressive at all, but it >shouldn't even lead to such a slow-down. > >Can anyone help me please? > >Thanks, > Nico > > > > >--------------------------------------------------------------------- >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 <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
