queries get progressively slower....(0.46, declarative, mysql)

[bdb112]

I am looking for hints on avoiding SA inefficiency. I have two
apparently very different tasks which seem to "clog" SA after more
than one execution. One involves rather straightforward data storage
(but into many tables, using declarative SA). THe other involves
moderate sized joins. Both become much slower after multiplie
executions as if some overhead is building up. After processing ~100
chunkd of data, the data storage task execution time begins to scale
as the number of executions, so it is much faster to exit and start
again - otherwise a chunk that took 2 seconds will take minutes or
more. The join task only doubles in execution time once - successive
executions are t, 2t, 2t, 2t etc, but if I execute on differernt data,
it becomes fully cumulative, so the execution time goes from 3 seconds
to several minutes eventually.

I have enclosed a table which shows the mysql activity and the SA
delta cpu time (same as wall time) for a fresh data1, repeat the task
with the same data (time doubles or a bit more, but does not increase
further with repeats of the same dadta ),
Then do two similar tasks on differernt data (delta time gets
longer), then go back to data1 (delta time is not 5 times greater).
session.close() then do again, -> delta time is short.

mysql does not report any slow queries, and the answers are correct.
session.flush() does not seem to help.

Any advice would be welcome.

THe following should be read in monosapce font.

Action Uptime Threads Questions Slowqurs Opens Flushtbls Opentbls
Qupersec dcpu
data1 35 2 1594 0 27 1 20
45.542 2.6
repeat1 75 2 2161 0 27 1 20
28.813 7.7
data02 134 2 4984 0 27 1 20
37.194 14.5
data1 158 2 5545 0 27 1 20
35.94 17.7
session.close() then
data1 96 2 7130 0 27 1 20
74.270 2.47
speed is back to normal

[Michael Bayer]

On Nov 19, 2009, at 4:34 PM, bdb112 wrote:

>
> Any advice would be welcome.
>
> THe following should be read in monosapce font.
>
> Action Uptime Threads Questions Slowqurs Opens Flushtbls Opentbls
> Qupersec dcpu
> data1 35 2 1594 0 27 1 20
> 45.542 2.6
> repeat1 75 2 2161 0 27 1 20
> 28.813 7.7
> data02 134 2 4984 0 27 1 20
> 37.194 14.5
> data1 158 2 5545 0 27 1 20
> 35.94 17.7
> session.close() then
> data1 96 2 7130 0 27 1 20
> 74.270 2.47
> speed is back to normal

this behavior is likely due to a known issue that was fixed in version 0.5.4. It involves a full scan of all objects in the current unit of work each time flush() runs, so the more objects present in your session, the slower each flush() operation becomes. There are lots of speed improvements to be had if you get on the latest 0.5 and in particular this one is the most dramatic - 0.5.4 also greatly improved the memory usage of the session such that unreferenced objects are garbage collected more readily.

Within 0.4 your only option would be to keep the size of the session very small - this is possible but its a lot easier just to upgrade to 0.5.6.