Do Not use Mysql 5.1.23-rc on FreeBSD from ports/packages!!! Use 5.1.22.
I ran into an issue where order by's were causing data from the result
sets to be dropped. This was causing a lot of issues with dbmail imapd
clients. There was a bug open on an issue with this with MySQL team.
Essentially something in the ports build process for 5.1.23 was
corrupting the sorting.
I always compile from source and I think that I had that issue with a
manual compile as well on 5.1.23.
I am running 7-STABLE with MySQL 5.1.22-rc built from ports.
To build the older version from ports modify the version number in the
Makefile to 5.1.22 and use the following for the distinfo file
MD5 (mysql-5.1.22-rc.tar.gz) = 87be813d1b2d76b1c756de0a16fa29fb
SHA256 (mysql-5.1.22-rc.tar.gz) =
1bce64c7e8fe476828644cbc05157fc84fe02e9c13379d1611fbd7d27c0721ec
SHA1 (mysql-5.1.22-rc.tar.gz) = 97c1396e0f0561485df120cce2092192aecd12a0
My Mysql cconfig is as follows and is probably pretty bad, but access
times are pretty fast for my few users. I have 8 GB of ram and quad
opterons, so you may need to drop some of the numbers down
-BEGIN-
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
myisam_recover
tmpdir=/usr/mysql.tmp
server-id=1
log-bin=mysql-bin
log-slow-queries=/var/log/mysqld-slow.log
log-output=FILE
character-set-server=utf8
innodb_force_recovery=0
innodb_file_per_table
skip-locking
default_table_type=INNODB
thread_concurrency=64
max_connections=512
max_allowed_packet=16M
#table_cache=1024
key_buffer_size=2G
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=256M
innodb_autoinc_lock_mode=2
innodb_commit_concurrency=0
innodb_concurrency_tickets=32
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=32
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
table_cache=1024
thread_cache=32
query_cache_size=256M
sort_buffer_size=256M
read_buffer_size=256M
read_rnd_buffer_size=256M
myisam_sort_buffer_size=256M
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 3072
-END-
Also - My custom kernel config has to following non-standard options
options HZ=2000
options SCHED_ULE # ULE scheduler
options PREEMPTION # Enable kernel thread preemption
options FULL_PREEMPTION # Enable kernel thread preemption
options IPI_PREEMPTION # Enable kernel thread preemption
options SMP
last pid: 77763; load averages: 0.12, 0.19,
0.15
up 1+05:13:58 22:38:57
187 processes: 1 running, 186 sleeping
CPU states: 0.2% user, 0.0% nice, 0.0% system, 0.2% interrupt, 99.6%
idle
Mem: 5365M Active, 805M Inact, 450M Wired, 293M Cache, 214M Buf, 216M Free
Swap: 8192M Total, 388M Used, 7804M Free, 4% Inuse
PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
30517 nobody 1 4 0 55548K 16612K sbwait 0 1:23 0.29%
dbmail-imapd
965 mysql 41 4 0 7216M 4605M sbwait 2 37:53 0.00% mysqld
Just a little bit of ram in use on mysql!
Hope that helps.
-Jon
UEMURA (fka. MAENAKA) Tetsuya wrote:
Hi Khanh
I don't know the exact scenario how your MySQL halts though, it may be
caused by the bug in the MySQL's query optimizer.
If you see a query "SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = NNN ORDER BY messageblk_idnr" doing a full table
scan, the bug is your cause, and the attached patch (against 2.2 branch)
should fix.
Note that how the bug affecting can be seen in the attached mysql
session log. In which, the 2nd "EXPLAIN SELECT ..." chose the incorrect
key requiring a full table scan.
FreeBSD 7.0-BETA2 as of Nov. 3 2007
MySQL 5.1.23-rc as of Dec. 14 2007
DBMail 2.2 as of Dec. 13 2007
------------------------------------------------------------------------
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
