I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records.
-- Table "mail_queue" DDL
CREATE TABLE `mail_queue` (
`id` bigint(20) NOT NULL DEFAULT '0',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`time_to_send` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`sent_time` datetime DEFAULT NULL,
`id_user` bigint(20) NOT NULL DEFAULT '0',
`ip` varchar(20) NOT NULL DEFAULT 'unknown',
`sender` varchar(50) NOT NULL DEFAULT '',
`recipient` text NOT NULL,
`headers` text NOT NULL,
`body` mediumblob NOT NULL,
`try_sent` tinyint(4) NOT NULL DEFAULT '0',
`delete_after_send` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I have tried using a text as well as a blob for the email body field, but no noticeable performance change, either way.
Thanks for any help.
Results of "SHOW VARIABLES LIKE 'innodb%';":
Variable_name Value
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 134217728
innodb_change_buffering all
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_format Antelope
innodb_file_format_check ON
innodb_file_format_max Antelope
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery 0
innodb_io_capacity 200
innodb_large_prefix OFF
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 75
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_old_blocks_pct 37
innodb_old_blocks_time 0
innodb_open_files 300
innodb_purge_batch_size 20
innodb_purge_threads 0
innodb_random_read_ahead OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads 4
innodb_replication_delay 0
innodb_rollback_on_timeout OFF
innodb_rollback_segments 128
innodb_spin_wait_delay 6
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages 8
innodb_strict_mode OFF
innodb_support_xa ON
innodb_sync_spin_loops 30
innodb_table_locks ON
innodb_thread_concurrency 0
innodb_thread_sleep_delay 10000
innodb_use_native_aio ON
innodb_use_sys_malloc ON
innodb_version 1.1.8
innodb_write_io_threads 4
Version: 5.5.22-cll
SHOW VARIABLES LIKE 'innodb%';andSELECT VERSION();and post them in the question. – RolandoMySQLDBA Dec 12 '12 at 8:40id, your primary key is an index. Indexes degrade performance on inserts (even though I doubt this is your biggest problem in this case). Recipient, in singular, hardly requires a text field? Bigint is a lot bigger than int, do you really expect more than 2 billion rows in this table? Apart from that - we need more information about your setup, like Rolando suggested. – Bing Dec 12 '12 at 10:23