I was using myisam tables and converted them to innodb
with Alter table <table> TYPE=INNODB; A query that used
to take 23 minutes, does not complete in hours. There
about 33M rows in the table and I was doing a count of
the rows. Some queries with more conditions seem fine.
Here is the table:
dspam_token_data | CREATE TABLE `dspam_token_data` (
`uid` smallint(5) unsigned default NULL,
`token` char(20) default NULL,
`spam_hits` int(11) default NULL,
`innocent_hits` int(11) default NULL,
`last_hit` date default NULL,
UNIQUE KEY `id_token_data_01` (`uid`,`token`),
KEY `id_token_data_02` (`innocent_hits`)
) TYPE=InnoDB |
Below is the my.cnf file, with comments removed.
Thanks,
Dale
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 512K
read_buffer_size = 2M
myisam_sort_buffer_size = 5M
thread_cache = 8
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2
wait_timeout=200
max_connections = 1000
# Replication Master Server (default)
# binary logging is required for replication
log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
innodb_data_home_dir = /private2/local/var/dspamdb/
innodb_data_file_path = ibdata1:3G:autoextend
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 800M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 20M
innodb_flush_method = nosync
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[mysqlhotcopy]
interactive-timeout
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
