Daniel Urstöger wrote:
Here is the funny thing:
select messageblk_idnr from dbmail_messageblks where
physmessage_id=xxx order by messageblk_idnr;
==> takes about 0.05 sec
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx ORDER BY messageblk_idnr;
==> takes about 4 minutes
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx;
==> takes about 0.10 sec
Well that is certainly odd, that the ordering adds that much time.
Anyhow, I ran the
querries on my 10GB database of dbmail and it does take less then 0,05
seconds.
My dbmail db is about 18.5 GB.
I think you should try to increase your innodb_buffer_pool within your
mysql config.
I've set innodb_buffer_pool_size to 1500M and the db creates 3 files of that
size. I can't have them any bigger because the system complains that
there is a 4 GB limit, even though the system is full 64 (and I've
compiled mysql
on it)
You can also check mysql.com for "database tuning", they have a few
guides and some
nice web cast on that topic, including stuff from Jay Pipes, just
Google the name, you will find
it for sure. Also mysql performance blog is a good point to start, and
focus on the innodb
related parameters.
I'll try that...
Also if you post your hardware configuration and the my.cnf here
somebody might have a
hint for you.
Ok, here it is:
------------------------------------------------------------------------------------------------
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
back_log = 50
max_connections = 10000
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 100M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 80M
join_buffer_size = 80M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1500M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 1000M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 80000
------------------------------------------------------------------------------------------------
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
