ljackson at jjcons
Apr 3, 2009, 6:39 PM
Post #10 of 11
(877 views)
Permalink
|
|
Re: OT: DBMail Administrator (DBMA) Performance Fix
[In reply to]
|
|
All,
Been a long time since I posted anything but I wanted to give some number
for the group as well.
Just an FYI it also maybe hardware and version dependent, that is to say
that the performance measurements are not cpu bound therefor it may be IO
that is saturated which will happen on either mysql or postgresql but as
show below on MySQL 5.0.67-community on my customer's production dbmail
cluster sql server
Dell 6850 quad 3.2Ghz cpus
2.6.18-92.1.18.el5.centos.plus #1 SMP Wed Nov 26 07:28:20 EST 2008 x86_64
x86_64 x86_64 GNU/Linux
CentOS 5.2
16G ram
Dell MD3000 sas raid5 array 200G storage over 13 drives at 15k rpm using
LVM2 and ext3
InnoDB, binary logging turned on
+----------+
| COUNT(1) |
+----------+
| 997728 |
+----------+
1 row in set (0.58 sec)
+----------+
| COUNT(1) |
+----------+
| 997728 |
+----------+
1 row in set (0.00 sec)
+----------+
| COUNT(*) |
+----------+
| 997728 |
+----------+
1 row in set (0.57 sec)
+----------+
| COUNT(*) |
+----------+
| 997728 |
+----------+
1 row in set (0.00 sec)
However it should be said that this box is very large and the customer never
fully utilized it so it is unloaded being Friday night at 9pm and he has
business customers but still it shows the time to load a similar dataset's
indexs into memory where mysql innodb has 8G of ram dedicated to it.
I would say that untill dbmail sql processes are CPU bound these kinds of
discussions on performance are not the most critical.
The slow query log on this box shows stuff like (taken with the fact that
this schema has been thru upgrades and maybe missing something, but I am
fairly certian it is up to date with indexs):
# Time: 090402 4:02:33
# User[at]Host: mail[mail] @ dbmail00 [10.1.1.204]
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 493594
SELECT p.id FROM dbmail_physmessage p LEFT JOIN dbmail_headervalue h
ON p.id= h.physmessage_id WHERE h.physmessage_id IS NULL;
# Time: 090403 4:02:24
# User[at]Host: mail[mail] @ dbmail00 [10.1.1.204]
# Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 994156
SELECT MIN(messageblk_idnr),MAX(is_header) FROM dbmail_messageblks GROUP BY
physmessage_id HAVING MAX(is_header)=0;
# Time: 090403 4:02:33
# User[at]Host: mail[mail] @ dbmail00 [10.1.1.204]
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 1022067
SELECT usr.user_idnr, sum(pm.messagesize), usr.curmail_size FROM
dbmail_users usr LEFT JOIN dbmail_mailboxes mbx ON mbx.owner_idnr =
usr.user_idnr LEFT JOIN dbmail_messages msg ON msg.mailbox_idnr =
mbx.mailbox_idnr LEFT JOIN dbmail_physmessage pm ON pm.id =
msg.physmessage_id AND msg.status < 2 GROUP BY usr.user_idnr,
usr.curmail_size HAVING ((SUM(pm.messagesize) <> usr.curmail_size) OR (NOT
(SUM(pm.messagesize) IS NOT NULL) AND usr.curmail_size <> 0));
# Time: 090403 4:02:37
# User[at]Host: mail[mail] @ dbmail00 [10.1.1.204]
# Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 1679722
SET timestamp=1238745757;
SELECT message_idnr FROM dbmail_messages m JOIN dbmail_physmessage p ON
m.physmessage_id=p.id JOIN dbmail_headervalue v ON
v.physmessage_id=p.idJOIN dbmail_headername n ON v.headername_id=
n.id WHERE m.mailbox_idnr=1816 AND n.headername IN
('resent-message-id','message-id') AND
v.headervalue='<20090403080233.C12443BF50[at]dbmail00.XXX>' AND p.internal_date
> NOW() - INTERVAL 3 DAY;
the last one seems to be the common but these three are consistant this may
be a better place to look in opimizations, I was under the impression that
any time you use a HAVING clause it will cause a table scan?
Also for interest of comparison to the explain details below:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dbmail_messageblks
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 5428069
Extra: Using index
1 row in set, 1 warning (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dbmail_messageblks
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 5428069
Extra: Using index
1 row in set, 1 warning (0.00 sec)
On Fri, Apr 3, 2009 at 2:04 PM, Wallace Tan <wally[at]pacific.net.sg> wrote:
> Michael Monnerie wrote:
> > On Freitag 03 April 2009 Wallace Tan wrote:
> >> select count(1) from t1;
> >
> > That would have been my next question. I've spoken once to Paul, because
> > dbmail uses lots of count(*), but PostgreSQL optimizes this out. Now it
> > seems MySQL would have a performance boost using count(1).
> >
> > Could you please try:
> > 1) first, SELECT COUNT(1) FROM dbmail_messageblks;
> > and afterwards
> > 2) SELECT COUNT(*) FROM dbmail_messageblks;
> >
> > The order is important: After the first select(), the table will be
> > cached, so the 2nd query will be faster. That, BTW, is part of the
> > explanation why your 2nd query was much faster than the 1st.
> > Still, count(1) should be faster than count(*) I would expect from the
> > thread you posted. I do not have a MySQL db with enough data to test
> > around. We're using PostgreSQL because things like that happen to exist
> > in MySQL since years, and I don't need a DBMS where I have to think for
> > it. I wonder why the devs don't manage to work around those problems.
> > But no flames please, everybody should use what they prefer.
> >
> > mfg zmi
>
> In MySQL (using InnoDB engine) there is no difference for between COUNT(*)
> or COUNT(1)
> because it is 'optimized' to use the PRIMARY index.
>
> The InnoDB PRIMARY key is a clustered index. See previous post.
>
> If I understand this correctly, the PRIMARY key (clustered index) is THE
> problem.
> So the only viable solution is to force the query to use a non-clustered
> index.
>
>
> > SELECT COUNT(1) FROM dbmail_messageblks;
> +----------+
> | COUNT(1) |
> +----------+
> | 263339 |
> +----------+
> 1 row in set (2 min 30.44 sec)
>
> > SELECT COUNT(*) FROM dbmail_messageblks;
> +----------+
> | COUNT(*) |
> +----------+
> | 263357 |
> +----------+
> 1 row in set (2 min 25.91 sec)
>
> > EXPLAIN EXTENDED SELECT COUNT(1) FROM dbmail_messageblks\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: dbmail_messageblks
> type: index
> possible_keys: NULL
> key: PRIMARY
> key_len: 8
> ref: NULL
> rows: 6574840
> Extra: Using index
>
> > EXPLAIN EXTENDED SELECT COUNT(*) FROM dbmail_messageblks\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: dbmail_messageblks
> type: index
> possible_keys: NULL
> key: PRIMARY
> key_len: 8
> ref: NULL
> rows: 6574840
> Extra: Using index
>
>
>
>
> --
> Regards,
> Wallace
> M:94500905
> _______________________________________________
> DBmail mailing list
> DBmail[at]dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
|
