[Zope] Experience with Zope and Access ?

[Chris Kratz]

I hate to ask the obvious, but have you tried running your your queries
directly against the database and take Zope out of the picture?  Is there
any chance that there is a configuration problem with the database that is
causing it to thrash a lot?  Of course that doesn't address the caching
issue.  Also, does MySQL have the ability to log the queries to a file as
they are submitted?  I have used this to debug caching issues in our app.
In our situation we found that the database actually wasn't the bottleneck
in certain cases we thought it was.  With logging, it has been invaluable to
be able to watch the query streams as they are executed because Zope doesn't
give a good way to see what SQL is passed to the database.  This would be
the best way to prove that the caches aren't working.

When we moved from ODBC/Access 97 to Postgres, we noticed a very significant
(at least 2x to 3x) performance increase and with appropriate indexes built,
we have increased it even further.  I have heard that MySQL is somewhat
faster then Postgres, so I would assume that given everything else is the
same for zope, the database performance should be faster with MySQL then
with ODBC/Access.  ODBC is wonderfull, but native access *should* always be
faster.  In a previous job we found that native drivers for MS SQL Server
and Oracle were significantly faster then their ODBC counterparts due to the
additional layer imposed by ODBC.  Perhaps some of that has been addressed
in newer versions of Windows, but that was the way it was back then.  So, in
your case, it would make one wonder if there isn't a problem with the
database.

Also, it's a hack, but we have been able to use the ZopeTime calls to do
rough profiling to find where the bottleneck is.  We get it at the beginning
of the query and then print out the elapsed time at various points.  Real
helpfull when you are trying to figure out why your page went from .02s to
5.9s.

Good Luck,

-Chris
------------------------------
Chris Kratz
chris.kratz@vistashare.com

----- Original Message -----
From: "Schmidt, Allen J." <aschmidt@nv.cc.va.us>
To: "'Tino Wildenhain'" <tino@wildenhain.de>; <zope@zope.org>
Sent: Tuesday, November 13, 2001 8:02 AM
Subject: RE: [Zope] Experience with Zope and Access ?


> I know...I did not mean to imply it was Zope's problem...I really think
Zope
> is very fast using Access/ODBC. In fact, I am trying to convert one of
> 'products' from Access/ODBC to MySQL and there is a significant speed
> decrease and increased load on the MySQL server. Not sure why. I copied
the
> folder containing everything, added a MySQL adapter and change the ZSQL
> methods to use the new DA. Then renamed the folders to put the new one
into
> production and everything came to a crawl. All the queries had caches one
> them and it seemed they were not being used. Today, I deleted the queries
> and recreated them to make sure they were not 'holding' onto
something...no
> difference. Even just me accessing it, it seems much slower than the
> Access/ODBC site in production being pounded on by users.
>
> If anyone has any ideas, I would be glad to hear them...
>
> Allen
>
> -----Original Message-----
> From: Tino Wildenhain [mailto:tino@wildenhain.de]
> Sent: Tuesday, November 13, 2001 9:41 AM
> To: Schmidt, Allen J.; 'Marc Gehling'; zope@zope.org
> Subject: RE: [Zope] Experience with Zope and Access ?
>
>
> Hi,
>
> --On Tuesday, November 13, 2001 07:12:33 -0500 "Schmidt, Allen J."
> <aschmidt@nv.cc.va.us> wrote:
>
> > we have been using Zope/Access for almost a year now. Works really well
> > and have had no problems. The only thing I don't like is that Zope locks
> > the MDB file so you can't copy it or open it with anything else. We
>
> This is not the problem of Zope but the problem of Access and its ODBC
> driver :-)
>
>
> > converted one system to MySQL and use a remote tool to gain access to
the
> > remote DB on the server. Works well.
>
> Regards
> Tino Wildenhain
>
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
>