No, aspseek doesn't use transactions as mysql 3.23 in the standard
distro doesn't have them.
The version I wrote for postgres does however.
I'm not sure if the mysql mailing lists was consulted, but it was a
known problem on the aspseek mailing lists.
... John
-----Original Message-----
From: dbmail-dev-admin@dbmail.org [mailto:dbmail-dev-admin@dbmail.org]
On Behalf Of Chris Nolan
Sent: Sunday, February 15, 2004 9:56 PM
To: dbmail-dev@dbmail.org
Subject: RE: [Dbmail-dev] some speed tests
Hmm...does ASPSeek utilise transactions at all?
The fact that it happened with both points to some glaring bug inside
MySQL's upper layers or a hardware problem, but since PostgreSQL was
fine (I'm assuming on the same hardware), that's a totally discounted
possibility.
Did anyone on the MySQL mailing list give you any joy?
Regards,
Chris
On Sun, 2004-02-15 at 21:40, John Hansen wrote:
> We tried both innodb and myisam with the same results, except of
> course performance suffered when using the innodb type. However both
> types resulted in corrupted tables after a few days starting from
> scratch.
>
> Following that corruption happened daily.
>
> I abandoned mysql early last year as I rewrote the application to use
> postgresql and haven't had any problems since.
>
> Other reasons I abandoned mysql was that to my dismay I discovered
> that statements such as select .. From table for update; is allowed.
> This should definately throw an error since for update is not
> supported and thus would lead to inconsistent data.
>
> I have not attempted to use mysql 4.
>
> -----Original Message-----
> From: dbmail-dev-admin@dbmail.org [mailto:dbmail-dev-admin@dbmail.org]
> On Behalf Of Chris Nolan
> Sent: Sunday, February 15, 2004 9:31 PM
> To: dbmail-dev@dbmail.org
> Subject: RE: [Dbmail-dev] some speed tests
>
>
> Now you've peaked my curiosity!
>
> Do you have any additional details regarding table types used and
> whether or not the same thing occurs in the 4.0.x series?
>
> Regards,
>
> Chris
>
> On Sun, 2004-02-15 at 21:14, John Hansen wrote:
> > Download and install aspseek-1.2.10 from http://www.aspseek.org Run
> > this against mysql 3.23.49
> >
> > Run it for a few days, indexing about 1million urls,
> > Then keep reindexing them and watch your database corrupt...
> >
> > Regards,
> >
> > John
> >
> > -----Original Message-----
> > From: dbmail-dev-admin@dbmail.org
> > [mailto:dbmail-dev-admin@dbmail.org]
> > On Behalf Of Chris Nolan
> > Sent: Sunday, February 15, 2004 6:37 PM
> > To: dbmail-dev@dbmail.org
> > Subject: RE: [Dbmail-dev] some speed tests
> >
> >
> > Table corruptions?
> >
> > I'd be very interested to hear the story around your grief, if for
> > nothing else, for reference purposes. :-)
> >
> > Regards,
> >
> > Chris
> >
> > On Sun, 2004-02-15 at 17:19, John Hansen wrote:
> > > Personally, I recent the statement; 'Considering MySQL has proven
> > > itself time and time again in terms of reliability, licencing
> > > flexibility and performance, such statements are baseless
> > > regardless
>
> > > of what arguments you make for which features'; based on
> > > experience,
>
> > > mysql proved that it was not reliable, tho performance was top of
> the
> > > line.
> > >
> > > In my opinion, if you're concerned with reliability, mysql should
> > > not
> > > be an option. I had daily table corruptions, and thus one of my
main
>
> > > tasks was to run mysql repair jobs regularly, just to keep
> everything
> > > running.
> > >
> > > Granted, postgresql has it's own problems, mainly with performance
> > > and
> >
> > > lack of a proper master / slave replication model, but that's a
> > > small
> > > price to pay for reliability.
> > >
> > > Just my $0.02 worth.
> > >
> > > Regards,
> > >
> > > John Hansen
> > >
> > > -----Original Message-----
> > > From: dbmail-dev-admin@dbmail.org
> > > [mailto:dbmail-dev-admin@dbmail.org]
> > > On Behalf Of Chris Nolan
> > > Sent: Sunday, February 15, 2004 4:42 PM
> > > To: dbmail-dev@dbmail.org
> > > Subject: Re: [Dbmail-dev] some speed tests
> > >
> > >
> > > Dear Aaron,
> > >
> > > My comments are also inline. :-)
> > >
> > > On Sun, 2004-02-15 at 12:27, Aaron Stone wrote:
> > > > Comments inline...
> > > >
> > > > Chris Nolan <chris@imi.com.au> said:
> > > >
> > > > > Forgive the bluntness of the statement, but why is anyone even
> > > > > worrying about transactions as they relate to MySQL???
> > > >
> > > > Because we are currently structured to have a single set of
> > > > mid-level database operations that are translated into specific
> > > > low-level database function calls. The upside is that there
> > > > isn't any
> database
> >
> > > > specific handling outside of these mid-level functions. The
> > > > downside
> >
> > > > is that we need to make sure that function calls for certain
> > features
> > > > are consistent with analogous features in each database and that
> > they
> > > > carry with them enough information to make the appropriate
> > > > low-level
> >
> > > > call. A good example is the last inserted id number. In MySQL,
> > > > you only need the database connection identifier to get this. In
> > > > PostgreSQL, yo need both the database connection and the table
> > > > identifier. For DBMail to have a mid-level function that worked
> for
> > > > both, we'd have to make sure that it took both arguments and
> > > > used
> > them
> > >
> > > > as needed for whichever database's low-level calls were being
> > > > used. (Those were off the top of my head, so they may be
> > > > incorrect, but
> > they
> > >
> > > > do illustrate my point.)
> > >
> > > I've looked through the DBMail source code on a few occassions and
> > > even released a dodgy tool to convert Cyrus mailboxes to DBMail a
> > > while ago. The fact that I can use the calls present in the DB
> modules
> >
> > > and avoid having to write queries by hand for each different DB is
> > > an
> > > excellent feature of the DBMail API. I have nothing negative to
say
> > > here at all.
> > >
> > > >
> > > > > COMMIT in MySQL is passed to the table handler. In the case of
> > > > > MyISAM tables, the handler disregards the statement. For
InnoDB
> > > > > and BDB
> > > tables,
> > > > > COMMIT acts as it does in PostgreSQL.
> > > >
> > > > So then we have THREE different configurations to consider, and
> > > > need to be sure to design the mid-level interface appropriately.
> > >
> > > My statement was meant to say that the MyISAM table handler will
> > > just
> > > disregard BEGIN and COMMIT statements, just as it parses but
ignores
>
> > > CHECK and FOREIGN KEY constraints in table creation, thus
> illustrating
> >
> > > that you wouldn't need to add a mysql-with-transactions directory
> > > to the source tree.
> > >
> > > >
> > > > > So various people in this thread implying that MySQL isn't
> > > > > really
> > > > > a database need to do some more reading.
> > > >
> > > > If you're referring to my suggestion that the transaction
> > > > functions are a noop for MySQL, then you're reading too
deeply...
> > > > I just
> > didn't
> > > > realize that InnoDB would handle transactions entirely normally.
> > >
> > > My apologies, I was not referring to you at all! Yourself and your
> > > collegues have provided the world with a very, very funky mail
> > > repository!
> > >
> > > I was referring to someone who said in the body of their message
> > > posted to another branch of this thread "the supposed database
> mysql".
> >
> > > Considering MySQL has proven itself time and time again in terms
> > > of reliability, licencing flexibility and performance, such
> > > statements are baseless regardless of what arguments you make for
> > > which
> features.
> >
> > > For example, Visual FoxPro's backend is technically a database but
> > > provides nothing in the way of a privilege system (you need write
> > > access to record locking, so filesystem-level controls are no good
> > > either).
> > >
> > > >
> > > > > In summary, just encapsulate everything in transaction blocks
> > > > > and the underlaying database will act appropriately.
> > > >
> > > > Right. That's what we're talking about.
> > > >
> > > > Is the entire point of your comment that we can safely use
> > > >
> > > > BEGIN;
> > > >
> > > > .. whatever dbmail does...
> > > >
> > > > COMMIT;
> > > >
> > > > regardless of whether or not we know if the host database
> > > > actually
>
> > > > supports these keywords? You could have just said that.
> > >
> > > I could have, but then I would have risked putting an end to the
> > > thread.
> > > :-)
> > >
> > > Please accept my apology - I never meant to offend yourself or
> > > anyone
> > > else who contributes to DBMail. It seems my reading of the post I
> > > mentioned above caused me to be a bit heavier than I should have
> been.
> > >
> > > But yes, you have perfectly summarised the point of my comment.
> > > >
> > > > > Regards,
> > > > >
> > > > > Chris
> > > > >
> > > > > Aaron Stone wrote:
> > > > >
> > > > > >I don't even know where to begin in terms of designing the
> > > > > >delivery
> > >
> > > > > >chain around transactions. Could we do it as simply as adding
> > > > > >functions...
> > > > > >
> > > > > > void db_begin_transaction(void);
> > > > > > void db_flush_transaction(void); (or db_commit_...?)
> > > > > >
> > > > > >and then calling these functions before and after each major
> > > > > >section of database code? For the delivery chain, we could do
> > > > > >it inside of insert_message(). For dbmail-smtp, this
basically
> > > > > >means
> >
> > > > > >that the execution of the whole program is within one
> > transaction.
> > > > > >For dbmail-lmtpd, it means that each message is delivered
> > > > > >within
> > a
> > > > > >transaction but the miscellaneous queries before the main
> > > > > >message
> >
> > > > > >delivery chain are not transacted. For MySQL, these functions
> > would
> > >
> > > > > >be noops.
> > > > > >
> > > > > >Thing that might work?
> > > > > >
> > > > > >Aaron
> > > > > >
> > > > > >
> > > > > >Thomas Mueller <dbmail@tmueller.com> said:
> > > > > >
> > > > > >
> > > > > >
> > > > > >>Hi Aaron,
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >>>Do you have any way of narrowing this down to specific
> > > > > >>>queries that are taking the longest and/or are being
executed
>
> > > > > >>>the most?
> >
> > > > > >>>That would identify which low-level database functions are
> > being
> > > > > >>>called, then we can just trace our way up the call chain to
> > > > > >>>see
> >
> > > > > >>>who's misbehaving or acting on a flawed design. Also, if
> > > > > >>>you could run similar tests against the latest 1.2, it
> > > > > >>>would help
> > to
> > > > > >>>give a frame of reference, particularly for my delivery
> > > > > >>>chain design.
> > > > > >>>
> > > > > >>>
> > > > > >>That's simple: the main design flaw (actually that's no
> > > > > >>design
>
> > > > > >>flaw I think, that's because the so called database MySQL
> > couldn't
> > >
> > > > > >>do transactions in the past) is that dbmail doesn't use
> > > > > >>transaction.
> > > > > >>
> > > > > >>Because of that AutoCommit is used and whenever dbmail does
> > > > > >>anySqlQuery Postgres does 'BEGIN; anySqlQuery; COMMIT;' -
and
> > that
> > >
> > > > > >>is terribly slow. To ensure the Durability in ACID the
> > > > > >>database has to fflush() every transaction to stable
storage!
> > > > > >>That's why there is only one solution: we have to use
> > > > > >>transaction.
> > > > > >>
> > > > > >>With transactions we could remove the integrity checks of
> > > > > >>dbmail-maintenance too, because the database guarantees
> > integrity.
> > > > > >>
> > > > > >>Anyway, I did a trace of all SQL queries when a mail is
> > > > > >>copied
>
> > > > > >>using IMAP. I got 35 SELECT, 4 INSERT, 5 UPDATE (44 db
> > operations
> > > > > >>to insert one mail?).
> > > > > >>
> > > > > >>When searching for the sequential scan I found something
> > > > > >>quite
>
> > > > > >>interesting in the docs: the planer decides for every scan
> > > > > >>if
> > > > > >>a seqScan is cheaper that an index scan, and does a seqScan
> > > > > >>even
> > if
> > > > > >>an index
> > > > > >>exists:
> > > > > >>
> > > > > >>dbmail=> explain SELECT mailbox_idnr FROM mailboxes WHERE
> > > owner_idnr=2;
> > > > > >> QUERY PLAN
> > > > > >>---------------------------------------------------------
> > > > > >> Seq Scan on mailboxes (cost=0.00..1.06 rows=3 width=8)
> > > > > >> Filter: (owner_idnr = 2)
> > > > > >>(2 rows)
> > > > > >>
> > > > > >>The table has an index on owner_idnr.
> > > > > >>
> > > > > >>So I should repeat this test with a database with several
> > > > > >>hundred to thousand user, several dozen mailboxes for each
> > > > > >>user and several dozen mails in each mailbox to find out if
> > > > > >>all
> required
> > > > > >>indizes are there. Did anyone write a script to create such
> > > > > >>a
> > > > > >>database?
> > > > > >>
> > > > > >>But I found a strange query:
> > > > > >>SELECT mailbox_idnr FROM mailboxes WHERE mailbox_idnr = '4'
> > > > > >>AND owner_idnr = '2' mailbox_idnr is the primary key so that
> > > > > >>could
> > be
> > > > > >>optimized to: SELECT 4
> > > > > >>;-)
> > > > > >>
> > > > > >>
> > > > > >>I don't have a 1.2 installation, I'm sorry.
> > > > > >>
> > > > > >>
> > > > > >>--
> > > > > >>MfG Thomas Mueller - http://www.tmueller.com for pgp key
> > > (95702B3B)
> > > > > >>_______________________________________________
> > > > > >>Dbmail-dev mailing list
> > > > > >>Dbmail-dev@dbmail.org
> > > > > >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > _______________________________________________
> > > > > Dbmail-dev mailing list
> > > > > Dbmail-dev@dbmail.org
> > > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > > >
> > > >
> > > >
> > >
> > > _______________________________________________
> > > Dbmail-dev mailing list
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > _______________________________________________
> > > Dbmail-dev mailing list
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
distro doesn't have them.
The version I wrote for postgres does however.
I'm not sure if the mysql mailing lists was consulted, but it was a
known problem on the aspseek mailing lists.
... John
-----Original Message-----
From: dbmail-dev-admin@dbmail.org [mailto:dbmail-dev-admin@dbmail.org]
On Behalf Of Chris Nolan
Sent: Sunday, February 15, 2004 9:56 PM
To: dbmail-dev@dbmail.org
Subject: RE: [Dbmail-dev] some speed tests
Hmm...does ASPSeek utilise transactions at all?
The fact that it happened with both points to some glaring bug inside
MySQL's upper layers or a hardware problem, but since PostgreSQL was
fine (I'm assuming on the same hardware), that's a totally discounted
possibility.
Did anyone on the MySQL mailing list give you any joy?
Regards,
Chris
On Sun, 2004-02-15 at 21:40, John Hansen wrote:
> We tried both innodb and myisam with the same results, except of
> course performance suffered when using the innodb type. However both
> types resulted in corrupted tables after a few days starting from
> scratch.
>
> Following that corruption happened daily.
>
> I abandoned mysql early last year as I rewrote the application to use
> postgresql and haven't had any problems since.
>
> Other reasons I abandoned mysql was that to my dismay I discovered
> that statements such as select .. From table for update; is allowed.
> This should definately throw an error since for update is not
> supported and thus would lead to inconsistent data.
>
> I have not attempted to use mysql 4.
>
> -----Original Message-----
> From: dbmail-dev-admin@dbmail.org [mailto:dbmail-dev-admin@dbmail.org]
> On Behalf Of Chris Nolan
> Sent: Sunday, February 15, 2004 9:31 PM
> To: dbmail-dev@dbmail.org
> Subject: RE: [Dbmail-dev] some speed tests
>
>
> Now you've peaked my curiosity!
>
> Do you have any additional details regarding table types used and
> whether or not the same thing occurs in the 4.0.x series?
>
> Regards,
>
> Chris
>
> On Sun, 2004-02-15 at 21:14, John Hansen wrote:
> > Download and install aspseek-1.2.10 from http://www.aspseek.org Run
> > this against mysql 3.23.49
> >
> > Run it for a few days, indexing about 1million urls,
> > Then keep reindexing them and watch your database corrupt...
> >
> > Regards,
> >
> > John
> >
> > -----Original Message-----
> > From: dbmail-dev-admin@dbmail.org
> > [mailto:dbmail-dev-admin@dbmail.org]
> > On Behalf Of Chris Nolan
> > Sent: Sunday, February 15, 2004 6:37 PM
> > To: dbmail-dev@dbmail.org
> > Subject: RE: [Dbmail-dev] some speed tests
> >
> >
> > Table corruptions?
> >
> > I'd be very interested to hear the story around your grief, if for
> > nothing else, for reference purposes. :-)
> >
> > Regards,
> >
> > Chris
> >
> > On Sun, 2004-02-15 at 17:19, John Hansen wrote:
> > > Personally, I recent the statement; 'Considering MySQL has proven
> > > itself time and time again in terms of reliability, licencing
> > > flexibility and performance, such statements are baseless
> > > regardless
>
> > > of what arguments you make for which features'; based on
> > > experience,
>
> > > mysql proved that it was not reliable, tho performance was top of
> the
> > > line.
> > >
> > > In my opinion, if you're concerned with reliability, mysql should
> > > not
> > > be an option. I had daily table corruptions, and thus one of my
main
>
> > > tasks was to run mysql repair jobs regularly, just to keep
> everything
> > > running.
> > >
> > > Granted, postgresql has it's own problems, mainly with performance
> > > and
> >
> > > lack of a proper master / slave replication model, but that's a
> > > small
> > > price to pay for reliability.
> > >
> > > Just my $0.02 worth.
> > >
> > > Regards,
> > >
> > > John Hansen
> > >
> > > -----Original Message-----
> > > From: dbmail-dev-admin@dbmail.org
> > > [mailto:dbmail-dev-admin@dbmail.org]
> > > On Behalf Of Chris Nolan
> > > Sent: Sunday, February 15, 2004 4:42 PM
> > > To: dbmail-dev@dbmail.org
> > > Subject: Re: [Dbmail-dev] some speed tests
> > >
> > >
> > > Dear Aaron,
> > >
> > > My comments are also inline. :-)
> > >
> > > On Sun, 2004-02-15 at 12:27, Aaron Stone wrote:
> > > > Comments inline...
> > > >
> > > > Chris Nolan <chris@imi.com.au> said:
> > > >
> > > > > Forgive the bluntness of the statement, but why is anyone even
> > > > > worrying about transactions as they relate to MySQL???
> > > >
> > > > Because we are currently structured to have a single set of
> > > > mid-level database operations that are translated into specific
> > > > low-level database function calls. The upside is that there
> > > > isn't any
> database
> >
> > > > specific handling outside of these mid-level functions. The
> > > > downside
> >
> > > > is that we need to make sure that function calls for certain
> > features
> > > > are consistent with analogous features in each database and that
> > they
> > > > carry with them enough information to make the appropriate
> > > > low-level
> >
> > > > call. A good example is the last inserted id number. In MySQL,
> > > > you only need the database connection identifier to get this. In
> > > > PostgreSQL, yo need both the database connection and the table
> > > > identifier. For DBMail to have a mid-level function that worked
> for
> > > > both, we'd have to make sure that it took both arguments and
> > > > used
> > them
> > >
> > > > as needed for whichever database's low-level calls were being
> > > > used. (Those were off the top of my head, so they may be
> > > > incorrect, but
> > they
> > >
> > > > do illustrate my point.)
> > >
> > > I've looked through the DBMail source code on a few occassions and
> > > even released a dodgy tool to convert Cyrus mailboxes to DBMail a
> > > while ago. The fact that I can use the calls present in the DB
> modules
> >
> > > and avoid having to write queries by hand for each different DB is
> > > an
> > > excellent feature of the DBMail API. I have nothing negative to
say
> > > here at all.
> > >
> > > >
> > > > > COMMIT in MySQL is passed to the table handler. In the case of
> > > > > MyISAM tables, the handler disregards the statement. For
InnoDB
> > > > > and BDB
> > > tables,
> > > > > COMMIT acts as it does in PostgreSQL.
> > > >
> > > > So then we have THREE different configurations to consider, and
> > > > need to be sure to design the mid-level interface appropriately.
> > >
> > > My statement was meant to say that the MyISAM table handler will
> > > just
> > > disregard BEGIN and COMMIT statements, just as it parses but
ignores
>
> > > CHECK and FOREIGN KEY constraints in table creation, thus
> illustrating
> >
> > > that you wouldn't need to add a mysql-with-transactions directory
> > > to the source tree.
> > >
> > > >
> > > > > So various people in this thread implying that MySQL isn't
> > > > > really
> > > > > a database need to do some more reading.
> > > >
> > > > If you're referring to my suggestion that the transaction
> > > > functions are a noop for MySQL, then you're reading too
deeply...
> > > > I just
> > didn't
> > > > realize that InnoDB would handle transactions entirely normally.
> > >
> > > My apologies, I was not referring to you at all! Yourself and your
> > > collegues have provided the world with a very, very funky mail
> > > repository!
> > >
> > > I was referring to someone who said in the body of their message
> > > posted to another branch of this thread "the supposed database
> mysql".
> >
> > > Considering MySQL has proven itself time and time again in terms
> > > of reliability, licencing flexibility and performance, such
> > > statements are baseless regardless of what arguments you make for
> > > which
> features.
> >
> > > For example, Visual FoxPro's backend is technically a database but
> > > provides nothing in the way of a privilege system (you need write
> > > access to record locking, so filesystem-level controls are no good
> > > either).
> > >
> > > >
> > > > > In summary, just encapsulate everything in transaction blocks
> > > > > and the underlaying database will act appropriately.
> > > >
> > > > Right. That's what we're talking about.
> > > >
> > > > Is the entire point of your comment that we can safely use
> > > >
> > > > BEGIN;
> > > >
> > > > .. whatever dbmail does...
> > > >
> > > > COMMIT;
> > > >
> > > > regardless of whether or not we know if the host database
> > > > actually
>
> > > > supports these keywords? You could have just said that.
> > >
> > > I could have, but then I would have risked putting an end to the
> > > thread.
> > > :-)
> > >
> > > Please accept my apology - I never meant to offend yourself or
> > > anyone
> > > else who contributes to DBMail. It seems my reading of the post I
> > > mentioned above caused me to be a bit heavier than I should have
> been.
> > >
> > > But yes, you have perfectly summarised the point of my comment.
> > > >
> > > > > Regards,
> > > > >
> > > > > Chris
> > > > >
> > > > > Aaron Stone wrote:
> > > > >
> > > > > >I don't even know where to begin in terms of designing the
> > > > > >delivery
> > >
> > > > > >chain around transactions. Could we do it as simply as adding
> > > > > >functions...
> > > > > >
> > > > > > void db_begin_transaction(void);
> > > > > > void db_flush_transaction(void); (or db_commit_...?)
> > > > > >
> > > > > >and then calling these functions before and after each major
> > > > > >section of database code? For the delivery chain, we could do
> > > > > >it inside of insert_message(). For dbmail-smtp, this
basically
> > > > > >means
> >
> > > > > >that the execution of the whole program is within one
> > transaction.
> > > > > >For dbmail-lmtpd, it means that each message is delivered
> > > > > >within
> > a
> > > > > >transaction but the miscellaneous queries before the main
> > > > > >message
> >
> > > > > >delivery chain are not transacted. For MySQL, these functions
> > would
> > >
> > > > > >be noops.
> > > > > >
> > > > > >Thing that might work?
> > > > > >
> > > > > >Aaron
> > > > > >
> > > > > >
> > > > > >Thomas Mueller <dbmail@tmueller.com> said:
> > > > > >
> > > > > >
> > > > > >
> > > > > >>Hi Aaron,
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >>>Do you have any way of narrowing this down to specific
> > > > > >>>queries that are taking the longest and/or are being
executed
>
> > > > > >>>the most?
> >
> > > > > >>>That would identify which low-level database functions are
> > being
> > > > > >>>called, then we can just trace our way up the call chain to
> > > > > >>>see
> >
> > > > > >>>who's misbehaving or acting on a flawed design. Also, if
> > > > > >>>you could run similar tests against the latest 1.2, it
> > > > > >>>would help
> > to
> > > > > >>>give a frame of reference, particularly for my delivery
> > > > > >>>chain design.
> > > > > >>>
> > > > > >>>
> > > > > >>That's simple: the main design flaw (actually that's no
> > > > > >>design
>
> > > > > >>flaw I think, that's because the so called database MySQL
> > couldn't
> > >
> > > > > >>do transactions in the past) is that dbmail doesn't use
> > > > > >>transaction.
> > > > > >>
> > > > > >>Because of that AutoCommit is used and whenever dbmail does
> > > > > >>anySqlQuery Postgres does 'BEGIN; anySqlQuery; COMMIT;' -
and
> > that
> > >
> > > > > >>is terribly slow. To ensure the Durability in ACID the
> > > > > >>database has to fflush() every transaction to stable
storage!
> > > > > >>That's why there is only one solution: we have to use
> > > > > >>transaction.
> > > > > >>
> > > > > >>With transactions we could remove the integrity checks of
> > > > > >>dbmail-maintenance too, because the database guarantees
> > integrity.
> > > > > >>
> > > > > >>Anyway, I did a trace of all SQL queries when a mail is
> > > > > >>copied
>
> > > > > >>using IMAP. I got 35 SELECT, 4 INSERT, 5 UPDATE (44 db
> > operations
> > > > > >>to insert one mail?).
> > > > > >>
> > > > > >>When searching for the sequential scan I found something
> > > > > >>quite
>
> > > > > >>interesting in the docs: the planer decides for every scan
> > > > > >>if
> > > > > >>a seqScan is cheaper that an index scan, and does a seqScan
> > > > > >>even
> > if
> > > > > >>an index
> > > > > >>exists:
> > > > > >>
> > > > > >>dbmail=> explain SELECT mailbox_idnr FROM mailboxes WHERE
> > > owner_idnr=2;
> > > > > >> QUERY PLAN
> > > > > >>---------------------------------------------------------
> > > > > >> Seq Scan on mailboxes (cost=0.00..1.06 rows=3 width=8)
> > > > > >> Filter: (owner_idnr = 2)
> > > > > >>(2 rows)
> > > > > >>
> > > > > >>The table has an index on owner_idnr.
> > > > > >>
> > > > > >>So I should repeat this test with a database with several
> > > > > >>hundred to thousand user, several dozen mailboxes for each
> > > > > >>user and several dozen mails in each mailbox to find out if
> > > > > >>all
> required
> > > > > >>indizes are there. Did anyone write a script to create such
> > > > > >>a
> > > > > >>database?
> > > > > >>
> > > > > >>But I found a strange query:
> > > > > >>SELECT mailbox_idnr FROM mailboxes WHERE mailbox_idnr = '4'
> > > > > >>AND owner_idnr = '2' mailbox_idnr is the primary key so that
> > > > > >>could
> > be
> > > > > >>optimized to: SELECT 4
> > > > > >>;-)
> > > > > >>
> > > > > >>
> > > > > >>I don't have a 1.2 installation, I'm sorry.
> > > > > >>
> > > > > >>
> > > > > >>--
> > > > > >>MfG Thomas Mueller - http://www.tmueller.com for pgp key
> > > (95702B3B)
> > > > > >>_______________________________________________
> > > > > >>Dbmail-dev mailing list
> > > > > >>Dbmail-dev@dbmail.org
> > > > > >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > _______________________________________________
> > > > > Dbmail-dev mailing list
> > > > > Dbmail-dev@dbmail.org
> > > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > > >
> > > >
> > > >
> > >
> > > _______________________________________________
> > > Dbmail-dev mailing list
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > > _______________________________________________
> > > Dbmail-dev mailing list
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev