Wierd link works perfect for me. Ohh well. Waste of bandwidth follows:
James Pooton dbmail@dbmail.org Fri, 19 Apr 2002 10:03:34 -0600 Previous message: [Dbmail] MySQL tuning? Next message: [Dbmail] MySQL tuning? Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] Hello! You have a healthy machine. Off hand I would suggest running a version of linux with a 2.4 kernel version like Redhat 7.2 to optimize the use of the four processors. We've put FreeBSD and Redhat side by side on the same multi-processor boxes and were never able to tweak the BSD box to be as quick with MySQL. I'm assuming you'll be RAIDing the data drive with something like RAID 5. A hardware RAID controller will give you a little better performance, but we've had great results with Linux software raid under Redhat. Anyway, beyond that your 2 most important things are: 1) Index all you DB's properly for the queries you'll be doing. Common sense, but really it's the key to super MySQL performance. There are lots of good resources on the net for this. I went through and re-indexed the DBmail tables for all of the queries I saw in the CVS as of about a week ago. I'm attaching it to this if you want to give it a try. 2) Give MySQL some room to breath with all that RAM. The settings you're most interested in would be like: set-variable = key_buffer=1024M set-variable = max_connections=200 set-variable = record_buffer=8M set-variable = table_cache=127 set-variable = sort_buffer=32M set-variable = join_buffer_size=16M set-variable = myisam_sort_buffer_size=16M set-variable = query_buffer_size=8M -James # MySQL dump 8.14 # # Host: webdb2 Database: dbmail2 #-------------------------------------------------------- # Server version 3.23.40-log # # Table structure for table 'aliases' # CREATE TABLE aliases ( alias_idnr bigint(21) NOT NULL auto_increment, alias varchar(100) NOT NULL default '', deliver_to varchar(250) NOT NULL default '', client_idnr bigint(21) NOT NULL default '0', PRIMARY KEY (alias_idnr), KEY idx_alias (alias,deliver_to) ) TYPE=MyISAM; # # Table structure for table 'config' # CREATE TABLE config ( configid int(11) NOT NULL default '0', item varchar(128) NOT NULL default '', value varchar(128) NOT NULL default '', UNIQUE KEY idx_item (item) ) TYPE=MyISAM; # # Table structure for table 'mailboxes' # CREATE TABLE mailboxes ( mailbox_idnr bigint(21) NOT NULL auto_increment, owner_idnr bigint(21) NOT NULL default '0', name varchar(100) NOT NULL default '', seen_flag tinyint(1) NOT NULL default '0', answered_flag tinyint(1) NOT NULL default '0', deleted_flag tinyint(1) NOT NULL default '0', flagged_flag tinyint(1) NOT NULL default '0', recent_flag tinyint(1) NOT NULL default '0', draft_flag tinyint(1) NOT NULL default '0', no_inferiors tinyint(1) NOT NULL default '0', no_select tinyint(1) NOT NULL default '0', permission tinyint(1) default '2', is_subscribed tinyint(1) NOT NULL default '0', PRIMARY KEY (mailbox_idnr), KEY idx_subscribed (owner_idnr,is_subscribed), KEY idx_owner_idnr (owner_idnr,name) ) TYPE=MyISAM; # # Table structure for table 'messageblks' # CREATE TABLE messageblks ( messageblk_idnr bigint(21) NOT NULL auto_increment, message_idnr bigint(21) NOT NULL default '0', messageblk longtext NOT NULL, blocksize bigint(21) NOT NULL default '0', PRIMARY KEY (messageblk_idnr), KEY idx_message_idnr (message_idnr) ) TYPE=MyISAM; # # Table structure for table 'messages' # CREATE TABLE messages ( message_idnr bigint(21) NOT NULL auto_increment, mailbox_idnr bigint(21) NOT NULL default '0', messagesize bigint(21) NOT NULL default '0', rfcsize bigint(21) NOT NULL default '0', seen_flag tinyint(1) NOT NULL default '0', answered_flag tinyint(1) NOT NULL default '0', deleted_flag tinyint(1) NOT NULL default '0', flagged_flag tinyint(1) NOT NULL default '0', recent_flag tinyint(1) NOT NULL default '0', draft_flag tinyint(1) NOT NULL default '0', unique_id varchar(70) NOT NULL default '', internal_date datetime NOT NULL default '0000-00-00 00:00:00', status tinyint(3) unsigned zerofill NOT NULL default '000', PRIMARY KEY (message_idnr), KEY idx_mailbox_idnr (mailbox_idnr,status,unique_id,seen_flag), KEY idx_status (status), KEY idx_unique_id (unique_id), KEY idx_expunge (mailbox_idnr,deleted_flag,status), KEY idx_flag (message_idnr,unique_id,mailbox_idnr,status), KEY idx_answered_flag (mailbox_idnr,status,unique_id,answered_flag), KEY idx_deleted_flag (mailbox_idnr,status,unique_id,deleted_flag), KEY idx_flagged_flag (mailbox_idnr,status,unique_id,flagged_flag), KEY idx_recent_flag (mailbox_idnr,status,unique_id,recent_flag), KEY idx_draft_flag (mailbox_idnr,status,unique_id,draft_flag) ) TYPE=MyISAM; # # Table structure for table 'pbsp' # CREATE TABLE pbsp ( idnr bigint(21) NOT NULL auto_increment, since datetime NOT NULL default '0000-00-00 00:00:00', ipnumber varchar(40) NOT NULL default '', PRIMARY KEY (idnr), KEY idx_ipnumber (ipnumber), KEY idx_since (since) ) TYPE=MyISAM; # # Table structure for table 'tmpmessage' # CREATE TABLE tmpmessage ( message_idnr bigint(21) NOT NULL auto_increment, mailbox_idnr int(21) NOT NULL default '0', messagesize bigint(21) NOT NULL default '0', seen_flag tinyint(1) NOT NULL default '0', answered_flag tinyint(1) NOT NULL default '0', deleted_flag tinyint(1) NOT NULL default '0', flagged_flag tinyint(1) NOT NULL default '0', recent_flag tinyint(1) NOT NULL default '0', draft_flag tinyint(1) NOT NULL default '0', unique_id varchar(70) NOT NULL default '', internal_date datetime NOT NULL default '0000-00-00 00:00:00', status tinyint(3) unsigned zerofill NOT NULL default '000', PRIMARY KEY (message_idnr) ) TYPE=MyISAM; # # Table structure for table 'tmpmessageblk' # CREATE TABLE tmpmessageblk ( messageblk_idnr bigint(21) NOT NULL auto_increment, message_idnr bigint(21) NOT NULL default '0', messageblk longtext NOT NULL, blocksize bigint(21) NOT NULL default '0', PRIMARY KEY (messageblk_idnr), KEY idx_message_idnr (message_idnr) ) TYPE=MyISAM; # # Table structure for table 'users' # CREATE TABLE users ( user_idnr bigint(21) NOT NULL auto_increment, userid varchar(100) NOT NULL default '', passwd varchar(32) NOT NULL default '', client_idnr bigint(21) NOT NULL default '0', maxmail_size bigint(21) NOT NULL default '0', PRIMARY KEY (user_idnr), UNIQUE KEY idx_userid (userid,passwd) ) TYPE=MyISAM; On Thursday 15 August 2002 01:12 pm, Sam Przyswa wrote: > Bret Baptist ([EMAIL PROTECTED]) écrivait: > >I tried the MySQL indexing that is show here > >https://mailman.fastxs.nl/pipermail/dbmail/2002-April/000435.html. > > The link it's not working for me, can you give the exact modification to do > in the indexing. > > I noticed the same thing with dbmail-imapd and a PHP Webmail, but not with > Outlook, Netscape, Mozila. > > We have MySQL+InnoDB with a messageblks about 4.8Gb size on bi-proc 1.3Ghz > pentium machine with 512Mb memory and UW-SCSI RAID 15Gb disk, there is a > log of an IMAP request: > > -------------------------------------------------- > Aug 15 20:10:43 groupware dbmail/imap4[6468]: SS_WaitAndProcess(): client @ > socket 3 (IP: 127.0.0.1) accepted > Aug 15 20:10:43 groupware dbmail/imap4[6468]: COMMAND: [00000000 > CAPABILITY] Aug 15 20:10:43 groupware dbmail/imap4[6468]: COMMAND: > [00000001 AUTHENTICATE LOGIN] > Aug 15 20:10:43 groupware dbmail/imap4[6468]: IMAPD [PID 6468]: user (id 3, > name [EMAIL PROTECTED]) login accepted @ 2002-08-15 20:10:43^M > Aug 15 20:10:43 groupware dbmail/imap4[6468]: COMMAND: [00000002 > CAPABILITY] Aug 15 20:10:43 groupware dbmail/imap4[6468]: COMMAND: > [00000003 SELECT INBOX] Aug 15 20:10:44 groupware dbmail/imap4[6468]: > COMMAND: [00000004 STATUS INBOX (MESSAGES RECENT UNSEEN UIDNEXT > UIDVALIDITY)] > Aug 15 20:10:44 groupware dbmail/imap4[6468]: COMMAND: [00000005 FETCH > 1,2:21 (UID ENVELOPE BODY.PEEK[HEADER.FIELDS (Path Message-ID Newsgroups > Followup-To References)] INTERNALDATE RFC822.SIZE FLAGS)] > Aug 15 20:10:45 groupware dbmail/imap4[6468]: COMMAND: [00000006 FETCH > 22,23:39 (UID ENVELOPE BODY.PEEK[HEADER.FIELDS (Path Message-ID Newsgroups > Followup-To References)] INTERNALDATE RFC822.SIZE FLAGS)] > Aug 15 20:10:46 groupware dbmail/imap4[6468]: COMMAND: [00000007 FETCH 39 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:46 groupware dbmail/imap4[6468]: COMMAND: [00000008 FETCH 39 > BODY.PEEK [HEADER]] > Aug 15 20:10:47 groupware dbmail/imap4[6468]: COMMAND: [00000009 FETCH 38 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:47 groupware dbmail/imap4[6468]: COMMAND: [0000000a FETCH 38 > BODY.PEEK [HEADER]] > Aug 15 20:10:47 groupware dbmail/imap4[6468]: COMMAND: [0000000b FETCH 37 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:47 groupware dbmail/imap4[6468]: COMMAND: [0000000c FETCH 37 > BODY.PEEK [HEADER]] > Aug 15 20:10:47 groupware dbmail/imap4[6468]: COMMAND: [0000000d FETCH 36 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [0000000e FETCH 36 > BODY.PEEK [HEADER]] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [0000000f FETCH 35 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [00000010 FETCH 35 > BODY.PEEK [HEADER]] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [00000011 FETCH 34 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [00000012 FETCH 34 > BODY.PEEK [HEADER]] > Aug 15 20:10:48 groupware dbmail/imap4[6468]: COMMAND: [00000013 FETCH 33 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:49 groupware dbmail/imap4[6468]: COMMAND: [00000014 FETCH 33 > BODY.PEEK [HEADER]] > Aug 15 20:10:49 groupware dbmail/imap4[6468]: COMMAND: [00000015 FETCH 32 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:49 groupware dbmail/imap4[6468]: COMMAND: [00000016 FETCH 32 > BODY.PEEK [HEADER]] > Aug 15 20:10:49 groupware dbmail/imap4[6468]: COMMAND: [00000017 FETCH 31 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:49 groupware dbmail/imap4[6468]: COMMAND: [00000018 FETCH 31 > BODY.PEEK [HEADER]] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [00000019 FETCH 30 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [0000001a FETCH 30 > BODY.PEEK [HEADER]] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [0000001b FETCH 29 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [0000001c FETCH 29 > BODY.PEEK [HEADER]] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [0000001d FETCH 28 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:50 groupware dbmail/imap4[6468]: COMMAND: [0000001e FETCH 28 > BODY.PEEK [HEADER]] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [0000001f FETCH 27 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [00000020 FETCH 27 > BODY.PEEK [HEADER]] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [00000021 FETCH 26 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [00000022 FETCH 26 > BODY.PEEK [HEADER]] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [00000023 FETCH 25 > (BODYSTRUCTURE FLAGS)] > Aug 15 20:10:51 groupware dbmail/imap4[6468]: COMMAND: [00000024 FETCH 25 > BODY.PEEK [HEADER]] > Aug 15 20:10:52 groupware dbmail/imap4[6468]: COMMAND: [00000025 LOGOUT] > Aug 15 20:10:52 groupware dbmail/imap4[6468]: IMAPD [PID 6468]: user (id:3) > logging out @ (null)^M > Aug 15 20:10:52 groupware dbmail/imap4[6468]: imap_process(): Closing > connection for client from IP [127.0.0.1] > Aug 15 20:10:52 groupware dbmail/imap4[6468]: SS_WaitAndProcess(): client @ > socket 3 (IP: 127.0.0.1) logged out, connection closed > ------------------------------------- > > It take 9 Sec to fetch 15 messages within a mailbox of about 40 messages. > > I got best results with Courier-IMAP... > > Sam. -- Bret Baptist Systems and Technical Support Specialist [EMAIL PROTECTED] Internet Exposure, Inc. http://www.iexposure.com (612)676-1946 x17 Web Development-Web Marketing-ISP Services ------------------------------------------ Today is the tomorrow you worried about yesterday.
