Hello Martin and Stefan,
its Sunday and time for some server fun! ;-)
On 25.02.2016 09:47, Stefan Manegold
wrote:
@Martin:
The issue you mention where never release;
hence, I do not think they play a role, here.
OK, but for the sake of completeness: There is nothing suspicious to
spot in the merovingian.log of this DB farm. Nothing in general and
especially nothing when such a slow query is running...
@Andreas:
Most important question:
Which version of MonetDB did work fine before, and
which does no longer after the upgrade?
I have no idea how to find out which version was the last. Sorry! I
regularly install the Debian updates, it was the last "official"
packet before the one I installed a few days before... My current
version of monetdb is shown as: “MonetDB Database Server Toolkit
v1.1 (Jul2015-SP2)” and im running Debian GNU/Linux 7 (wheezy). To
make it also clear: there was no change to the DB Farm between
software updates. Probably it is a specific problem with that
current MonetDB5-Debian Package?
For the new version, you could profile your quer(y|ies)
by prefixing them with TRACE --- feel free to share the
results for analysis --- unfortunately, you cannot (easily)
downgrade to the older version to also profile that one.
Would you be to share all ingredients to reproduce the problem?
I can give you the monetdb version, the logs of the 3 traces
("lower", "ilike" and "simple", attached to this mail) unfortunately
not the data itself... By the way, there is no difference in the
behavior when I search other strings. The Breakdown comes when using
LOWE(). The Table is extreme simple (filld with some million
strings, the longest "ua" with some 100 chars). Its structure is
build as follow: CREATE TABLE "agents" ("ua_id" int, "ua_hash"
varchar(32),"ua" text, "class" varchar(3), "date" date);
Also, do I understand correctly that you tested the alternative
omitting LOWER() and using ILIKE, instead, and that works fine (also)
with the new version of MonetDB?
Yes! Absolutely. Using ILIKE instead of LOWER() in the current
version works fine.
If so, you might also want to profile (TRACE) that one to compare
the trace with that of the "slow" query.
I attached the 3 traces and I hope you gain insight from it! :-)
Best,
Stefan
Thank you in advance,
and greetings from Germany,
Andreas (theafh)