Date: Fri, 04 Jul 2014 12:17:55 +0200 From: Michelle Sullivan <michelle@sorbs.net> To: Matthew Seaman <matthew@FreeBSD.org> Cc: ports@freebsd.org, demon@FreeBSD.org Subject: Re: Patch (not perfect but half way there) for DBIx::SearchBuilder... Message-ID: <53B67F53.9000903@sorbs.net> In-Reply-To: <53B4F7DE.9090300@FreeBSD.org> References: <53B4C581.5060508@sorbs.net> <53B4F7DE.9090300@FreeBSD.org>
next in thread | previous in thread | raw e-mail | index | archive | help
First, Thanks Matthew and Dimitry, and indeed Jesse is still listed as the maintainer - however I have had no reply from him over the last few weeks, I could be wrong but I think he no longer works for Best Practical. Matthew Seaman wrote: > On 03/07/2014 03:52, Michelle Sullivan wrote: > >> Matthew, "Demon" (cc'd you two specifically because the patch affects >> ports you maintain directly) >> >> I created a patch a while ago and sent it off to Jesse and the RT-Users >> mailing list to fix extremely slow loading of tickets for Request-Tracker. >> >> The patch is for DBIx::Searchbuilder->Fields() and you can see it here: >> https://rt.cpan.org/Public/Ticket/Attachment/WithHeaders/733854 (bug: >> https://rt.cpan.org/Public/Bug/Display.html?id=96902 ) >> >> If it were to be shipped as an 'option' in FreeBSD ports would it be >> attached to DBIx::SearchBuilder or RT 4.x? (the patch is against >> DBIx::SearchBuilder, but very specifically affects RT) >> > > The rt4x ports can't patch files belonging to DBIx::Searchbuilder -- > Thought so but thought I'd check... would be a bit complex to do a slave port... > that's not allowed. The options are: > > * Unconditionally patch DBIx::Searchbuilder > > -- given the patch hasn't been accepted upstream and it looks > like it could have a deleterious impact in some setups, this > doesn't seem a good idea to me > Well actually I could dix it to make it case sensitive, (which would be the correct thing to do) but it's likely others are using it in a non case sensitive way - this would affect MySQL users in that multiple calls would be made for each case (but not deleterious because it'll just store the same results under different cases) ... the main problem with the whole thing is if applications are using it and are not using the correct case then it won't return the correct information (if anything at all) ... however the flipside is if people have multiple schema's with the same table names in different case then results will be unpredictable... which brings to another issue... 2 schemas with the same table names in the same DB and with or without the patch the returned results would be unpredictable. > * Add an option to DBIx::SearchBuilder > > -- Apply the patch only by user choice. Doesn't help with > pre-compiled packages, but seems like a reasonable alternative > to me. > Sounds good to me - I'll make a patch if I don't get any response from Jesse within a reasonable time frame. > * Do nothing > > -- Means you'ld have to maintain a locally modified version of > DBIx::SearchBuilder > Not preferred - it caused me huge amounts of pain and I'm sure others will have had the same issue - I should share my results. > I think if you prepared a patch implementing the second case for the > databases/p5-DBIx-SearchBuilder port to "improve performance under high > network latency" (which should be off by default, so not changing > behaviour for other users unexpectedly) there's a good chance we'd > accept it. > Yup - as above.. will give Jesse time now that I have 'officially' logged a bug against DBIx::SearchBuilder. > >> For a brief background on it... I've been trying to upgrade SORBS >> Support from RT3.8 to RT4.0 and ticket loads are 4-6 minutes in RT4.0 >> compared to my old 3.8 system.. The cause being that the new System has >> a Pg cluster of 4 servers which are in their own datacentres.. the RT >> 'Front Ends' being in public network space in other datacentres. This >> is a fairly standard security model.. put your application servers in a >> DMZ (or public network) and keep the Database Servers locked in their >> own network.. Just a modest 20ms Ping time and 3 schemas (RT (public), >> Bucardo and Postgres) causes the ticket load to be 4-6 minutes per >> ticket - single user, 2 frontends. Patching DBIx::SearchBuilder with >> the patch in the bug, drops that down to 7 seconds. >> > > Yes, the patch is an obvious win for your circumstances. The question > is what will it do for the majority of installations where the > PostgreSQL database is on the same machine as the web frontend, or on a > very nearby machine? > It still speeds it up - but it's not as noticeable.... depending on the database setup ... if it's a user with 119 schema's or *lots* of tables in just one or 2 schema's it will make a huge difference... in my DBs a ticket load using the original call - with nothing but bucardo and RT in the DB causes over 1000 sequential queries.. the the pg_constraints table it hits only has 38 rows! > >> Best Practical have been less than helpful, so thinking about writing a >> patch for FreeBSD ports as a new 'option' as most of my servers are >> FreeBSD with my own Pkg Repo... Thoughts? >> > > In this case you can quite easily maintain a local patch in your > checked-out ports tree. You know about 'make makepatch' ? That will > create a files/patch-Mumble file which will be applied automatically > every time you re-build p5-DBIx-SearchBuilder. > Yes I'm the maintainer on several ports already... (and https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=190104 is an example of my patches/requests) > >> (Read the bug for the 'half way there' bit - the whole ->Fields() call >> has a bad flaw.. and FWIW, a user running MySQL will not be affected by >> my patch in any negative way as MySQL is case insensitive for table >> names - unlike PostgreSQL and others.) >> > > Your patch does seem to be fixing the problem --- DBIx::SearchBuilder > pulling data out of too many schemas --- in a fairly non-obvious way. > It's not clear to me that it is a generic fix for everyone whose > databases are a long way away from their frontends either. > It's not a fix for 'long way away' it's a fix for excessive queries which a long latency results in massive query times (as the same query can be made 1000's of time for no apparent rational reason - especially if you're only interested in the fields available in one table, why try and cache *everything* (particularly as the cache is not even case-safe)) Michelle -- Michelle Sullivan http://www.mhix.org/
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?53B67F53.9000903>