Mailing List Archive: Optimizations

Optimizations

Mar 24, 2004, 2:13 AM

Post #1 of 7 (464 views)

Hi!

I posted a message earlier, but to the wrong mailinglist, where I said that
I would be happy to help with optimisations regarding the database.

At work I do a lot of optimization where I reduce the number of queries, and
speed up queries that are to slow.

I would be happy to help in any way I can if someone could point me in a
direction where I should start.

I almost solely work with MySQL at work.

If someone feels like giving me some directions I’d take a look at it as
soon as I have some time over.

Regards, Joakim

Re: Optimizations [ In reply to ]

aaron at serendipity

Mar 24, 2004, 8:36 AM

Post #2 of 7 (467 views)

Permalink

The queries are all in the file "db.c" -- take a look either at 2.0rc4 or the
current CVS, as no further major changes to the 1.2 series are planned.

Aaron

""Jocke"" <dbmail@softmagic.nu> said:

> Content-Type: multipart/alternative;
> boundary="----=_NextPart_000_001F_01C41188.ABFF5B80"
>
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_001F_01C41188.ABFF5B80
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi!
>
> =20
>
> I posted a message earlier, but to the wrong mailinglist, where I said =
> that
> I would be happy to help with optimisations regarding the database.
>
> =20
>
> At work I do a lot of optimization where I reduce the number of queries, =
> and
> speed up queries that are to slow.
>
> I would be happy to help in any way I can if someone could point me in a
> direction where I should start.
>
> =20
>
> I almost solely work with MySQL at work.
>
> =20
>
> If someone feels like giving me some directions I=92d take a look at it =
> as
> soon as I have some time over.
>
> =20
>
> =20
>
> Regards, Joakim
>
>
> ------=_NextPart_000_001F_01C41188.ABFF5B80
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
> xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
> xmlns=3D"http://www.w3.org/TR/REC-html40">
>
> <head>
> <meta http-equiv=3DContent-Type content=3D"text/html; =
> charset=3Diso-8859-1">
> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
> <style>
> 
> </style>
>
> </head>
>
> <body lang=3DSV link=3Dblue vlink=3Dpurple>
>
> <div class=3DSection1>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>Hi!<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'><o:p> </o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>I posted a message earlier, but to the wrong =
> mailinglist,
> where I said that I would be happy to help with optimisations regarding =
> the
> database.<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'><o:p> </o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>At work I do a lot of optimization where I reduce the =
> number
> of queries, and speed up queries that are to =
> slow.<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>I would be happy to help in any way I can if someone =
> could
> point me in a direction where I should =
> start.<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'><o:p> </o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>I almost solely work with MySQL at =
> work.<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>         =
>             &=
> nbsp;           &n=
> bsp;           &nb=
> sp;     
> <o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>If someone feels like giving me some directions =
> I’d
> take a look at it as soon as I have some time =
> over.<o:p></o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'><o:p> </o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'><o:p> </o:p>
>
> style=3D'font-size:10.0pt;
> font-family:Arial'>Regards, Joakim<o:p></o:p>
>
> </div>
>
> </body>
>
> </html>
>
> ------=_NextPart_000_001F_01C41188.ABFF5B80--
>
>
>

--

Re: Optimizations [ In reply to ]

matthew at zeut

Mar 24, 2004, 11:32 AM

Post #3 of 7 (461 views)

Permalink

Jocke wrote:

>
>
> I posted a message earlier, but to the wrong mailinglist, where I said
> that I would be happy to help with optimisations regarding the database.
>
>
>
> At work I do a lot of optimization where I reduce the number of
> queries, and speed up queries that are to slow.
>
> I would be happy to help in any way I can if someone could point me in
> a direction where I should start.
>
>
>
> I almost solely work with MySQL at work.
>

I think there is probably lots of room for optimization and as noted by
someone else earlier, I think reducing the number of individual queries
should be some nice low hanging fruit. My only other comment is that we
need to continue to keep things as database independent as possible.
Any optimization you make needs to at least work on PostgreSQL with
hurting it's performance, and visa-versa.

While dbmail is much better than flat file email storage in many ways, I
think it still gets outperformed in some operations. There is
discussion of this in the archives if you can find it. I find it
frustrating that the email archives for this project are not
searchable. I would think that would be a natural for a database email
storage system. BTW, I would be interested in working on resolving
this, I know PostgreSQL has an FTI engine that could is used by the
PostgreSQL project to search their archives.

Matthew

Re: Optimizations [ In reply to ]

leander at ic-s

Mar 25, 2004, 1:22 AM

Post #4 of 7 (453 views)

Permalink

Hi,

On 24-mrt-04, at 19:32, Matthew T. O'Connor wrote:

> There is discussion of this in the archives if you can find it. I
> find it frustrating that the email archives for this project are not
> searchable. I would think that would be a natural for a database
> email storage system.

we're working on this. The mailing-lists are actually not stored in
DBMail and we still have some issues with the htdig-patches for
mailman.

Should be fixed soon!

Kind regards,
Leander

Re: Optimizations [ In reply to ]

matthew at zeut

Mar 25, 2004, 7:50 PM

Post #5 of 7 (459 views)

Permalink

On Thursday 25 March 2004 03:22 am, Leander Koornneef, IC&S wrote:
> Hi,
>
> On 24-mrt-04, at 19:32, Matthew T. O'Connor wrote:
> > There is discussion of this in the archives if you can find it. I
> > find it frustrating that the email archives for this project are not
> > searchable. I would think that would be a natural for a database
> > email storage system.
>
> we're working on this. The mailing-lists are actually not stored in
> DBMail and we still have some issues with the htdig-patches for
> mailman.

What are you planning to use? It would be nice to have it be searched right
from a dbmail database using an FTI engine. I know postgresql has one, and
I think mysql does.

Re: Optimizations [ In reply to ]

leander at ic-s

Mar 26, 2004, 2:55 AM

Post #6 of 7 (462 views)

Permalink

On 26-mrt-04, at 3:50, Matthew T. O'Connor wrote:

> On Thursday 25 March 2004 03:22 am, Leander Koornneef, IC&S wrote:
>> Hi,
>>
>> On 24-mrt-04, at 19:32, Matthew T. O'Connor wrote:
>>> There is discussion of this in the archives if you can find it. I
>>> find it frustrating that the email archives for this project are not
>>> searchable. I would think that would be a natural for a database
>>> email storage system.
>>
>> we're working on this. The mailing-lists are actually not stored in
>> DBMail and we still have some issues with the htdig-patches for
>> mailman.
>
> What are you planning to use? It would be nice to have it be searched
> right
> from a dbmail database using an FTI engine. I know postgresql has
> one, and
> I think mysql does.

Well, like I said: the list-messages are currently not stored in DBMail.
The lists are completely (well, almost) handled by Mailman.
We are using patches for mailman to incorporate the htdig
search-facility in the archives.
(see
http://www.python.org/cgi-bin/faqw-mm.py?req=show&file=faq01.011.htp)

I guess we could use a database, but Mailman does the job quite well
and I don't really feel
like writing a new web-interface to replace something that already
works fine (ehr.....or at least will be working fine, once we've fixed
it :-) )

Leander

Re: Optimizations [ In reply to ]

xndbmail at xerus

Mar 26, 2004, 9:32 AM

Post #7 of 7 (461 views)

Permalink

On Thu, Mar 25, 2004 at 09:22:05AM +0100, Leander Koornneef, IC&S wrote:
> On 24-mrt-04, at 19:32, Matthew T. O'Connor wrote:
>
> >There is discussion of this in the archives if you can find it. I
> >find it frustrating that the email archives for this project are not
> >searchable. I would think that would be a natural for a database
> >email storage system.
>
> we're working on this. The mailing-lists are actually not stored in
> DBMail and we still have some issues with the htdig-patches for
> mailman.

I consider Google to be a pretty good index to the mailing list. Of
course, it's not updated in real-time, but it's not too far behind.

http://www.google.com/search?q=dbmail+optimization+site%3Atwister.fastxs.net