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
70 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
