Hi,
Is there a way to speed up inserts on a table with three fulltext
indexes? I'm using the multiple value insert format like:
insert into blah (field1, field2) values ('val1', 'val2'), ('val2',
'val3'), etc..
Perhaps this is a bug in the current mysql4 bk snapshot, but inserts and
selects on a table with three fulltext indexes are taking up to 23
seconds!
On average it's still taking up to 2.5 seconds to insert 50 records into
a table with 15000 records already (Which I think is kinda slow). But
every now and then it'll really slow down. I just logged 9 seconds for a
fulltext select on a table with 15000 records, and 23 seconds for an
insert of 106 rows on the same table. The machine has 400 megs of RAM
and is a PIII 750 with IDE drives. (as opposed to SCSI). Running MySQL
version 4 (the latest bk snapshot).
Here are the entries from the 'slow.log':
##########
SET timestamp=1016613725;
select SQL_CACHE
id,search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home,jobsite_con
/usr/local/mysql/libexec/mysqld, Version: 4.0.2-alpha-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 020320 9:01:40
# User@Host: root[root] @ localhost []
# Query_time: 9 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use workzoo;
SET timestamp=1016614900;
insert into search_cache (ctime,
search_category,search_jobtype,joblink,title,d_date_posted,n_date_posted,jobsite,jobsite_home
# Time: 020320 9:02:04
# User@Host: root[root] @ localhost []
# Query_time: 23 Lock_time: 0 Rows_sent: 106 Rows_examined: 13081
#############
My my.cnf is included below. Based on the config above, particularly the
amount of RAM, is this the ideal config for my machine? (considering
that I do large inserts, updates and select on all three fulltext
indexes on the same table)
btw. I'm getting great performance on multiple sequential updates (over
200 records takes under .1 seconds) by locking the table.
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
memlock
set-variable = key_buffer=100M
set-variable = max_allowed_packet=5M
set-variable = table_cache=256
set-variable = record_buffer=1M
set-variable = sort_buffer=20M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=20M
set-variable = long_query_time=1
set-variable = ft_min_word_len=1
#Query cache configured for on demand only
set-variable = query_cache_limit=2M
set-variable = query_cache_size=2M
set-variable = query_cache_startup_type=2
bind-address=10.1.1.1
log-bin
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
log = /var/log/mysql/general.log
log-slow-queries = /var/log/mysql/slow.log
Thanks,
Mark.
---------------------------------------------------------------------
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
