Thread: mysql proxy
Hi, I am sure most of you have seen some blog post [1] on the mysql proxy [2]. It basically allows you to proxy calls to the server. Inside the proxy you can write scripts (language is LUA) to do all sorts of funky things like auditing, r/w splitting by master/slave [2], connection pooling etc. I am not the one to judge the feasibility of taking the code and adding postgresql (or if mysql ab would even be willing to accept such a code contribution). Of course the code could also get forked. Either way the end result would be a GPL'ed app. A proxy always seems like a slightly hacky solution to various problems that could be addressed on a different level. But it does enable a lot of things what probably take much more work to do "properly". regards, Lukas [1] http://jan.kneschke.de/2007/6/25/mysql-proxy-0-5-0-released [2] http://dev.mysql.com/downloads/mysql-proxy [3] http://jan.kneschke.de/2007/8/26/mysql-proxy-more-r-w-splitting
Lukas Kahwe Smith wrote: > Hi, > > I am sure most of you have seen some blog post [1] on the mysql proxy > [2]. It basically allows you to proxy calls to the server. Inside the > proxy you can write scripts (language is LUA) to do all sorts of funky > things like auditing, r/w splitting by master/slave [2], connection > pooling etc. Sounds like mysql-proxy tries to mimic what pgsql's triggers and rules and pgpool already do. Regards, Andreas
Andreas Pflug wrote: > Lukas Kahwe Smith wrote: >> Hi, >> >> I am sure most of you have seen some blog post [1] on the mysql proxy >> [2]. It basically allows you to proxy calls to the server. Inside the >> proxy you can write scripts (language is LUA) to do all sorts of funky >> things like auditing, r/w splitting by master/slave [2], connection >> pooling etc. > Sounds like mysql-proxy tries to mimic what pgsql's triggers and rules > and pgpool already do. If that is the case, it might be worth it to write a comparison blog post illustrating that this is available for PostgreSQL. I wasnt aware that there is a scripting language embedded in pgpool. regards, Lukas
Andreas, Lukas, > >> I am sure most of you have seen some blog post [1] on the mysql proxy > >> [2]. It basically allows you to proxy calls to the server. Inside the > >> proxy you can write scripts (language is LUA) to do all sorts of funky > >> things like auditing, r/w splitting by master/slave [2], connection > >> pooling etc. > > > > Sounds like mysql-proxy tries to mimic what pgsql's triggers and rules > > and pgpool already do. No, I don't think that's accurate. > If that is the case, it might be worth it to write a comparison blog > post illustrating that this is available for PostgreSQL. I wasnt aware > that there is a scripting language embedded in pgpool. There isn't AFAIK. The current "best of" proxies for PostgreSQL is pgBouncer. I'd be interested in seeing us add scripting capabilities to it like MySQL has; it seems like a cool idea. Of course, I personally have no plans to do the work. -- Josh Berkus PostgreSQL @ Sun San Francisco
On Mon, Aug 27, 2007 at 10:40:33AM -0700, Josh Berkus wrote:
> in seeing us add scripting capabilities to it like MySQL has; it
> seems like a cool idea.
Just because you can do something doesn't mean you should. The idea
of running scripts in between the "real" client and the database that
is ostensibly handling the presumably valuable data makes me airsick.
Anything that you could do in such a script, you could do with a
stored procedure, and you'd get the benefits of ACID along with it.
I have to agree with whoever it was upthread who suggested that this
is a MySQL-ish way of getting access to capabilities PostgreSQL has
had for some time (and, as usual, in a more usual, safer, complete
way).
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
--On Montag, August 27, 2007 15:23:16 -0400 Andrew Sullivan
<ajs@crankycanuck.ca> wrote:
> The idea
> of running scripts in between the "real" client and the database that
> is ostensibly handling the presumably valuable data makes me airsick.
> Anything that you could do in such a script, you could do with a
> stored procedure, and you'd get the benefits of ACID along with it.
I attended a talk at Froscon 2007 where Guiseppe Maxxia shows a "global log
server" as a use case for his proxy. In my opinion, it's because exactly of
this reason you are describing that this will never work reliably and i
can't think of any useful "production" use case where someone has a real
need for that.
--
Thanks
Bernd
Bernd Helmle wrote: >> The idea >> of running scripts in between the "real" client and the database that >> is ostensibly handling the presumably valuable data makes me airsick. >> Anything that you could do in such a script, you could do with a >> stored procedure, and you'd get the benefits of ACID along with it. > > I attended a talk at Froscon 2007 where Guiseppe Maxxia shows a "global > log server" as a use case for his proxy. In my opinion, it's because > exactly of this reason you are describing that this will never work > reliably and i can't think of any useful "production" use case where > someone has a real need for that. Well there are some things postgresql most definately already has which mysql did not before the proxy: - connection pooling - flexible choice of language for embedding database level logic (mysql only has partial sql 2003 stored procedure syntax) .. and yes LUA as a stored proc language inside the proxy is a cheap hild over compared to what postgresql has to offer But something I am not so sure about is stuff like: - app independent read/write splitting for master/slave setups - app independent auditing Both of the above can be done in postgresql of course using triggers, but you would at the very least need to modify the app schema to add the triggers .. regards, Lukas
On Tue, Aug 28, 2007 at 11:15:32AM +0200, Lukas Kahwe Smith wrote:
> But something I am not so sure about is stuff like:
> - app independent read/write splitting for master/slave setups
There is more than one replication system that does this sort of
thing already. They all have the same problem: what do you do when
something commits on one box and fails on the other? In such a case,
you lose consistency. You could of course do it with two phase
commit; but if you're going to pay that cost, then you don't need
this additional scripting language thing anyway.
> - app independent auditing
You could do some of this with the query logs. But. . .
> Both of the above can be done in postgresql of course using triggers,
> but you would at the very least need to modify the app schema to add the
> triggers ..
. . .yes. And I don't think that's a bad thing: auditing, if it's
needed, should be designed into an application, not something that
you hang on the side.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
Andrew Sullivan wrote: > . . .yes. And I don't think that's a bad thing: auditing, if it's > needed, should be designed into an application, not something that > you hang on the side. I disagree. Auditing should ideally be done by a different system and be implemented by someone else than the original developers. Also with the many OSS apps out there it would require essentially a fork to add auditing. regards, Lukas
On Tue, Aug 28, 2007 at 03:51:54PM +0200, Lukas Kahwe Smith wrote:
>
> I disagree. Auditing should ideally be done by a different system and be
> implemented by someone else than the original developers. Also with the
> many OSS apps out there it would require essentially a fork to add auditing.
Note that I wasn't suggesting that the person who wrote the code
should also do the auditing; just that it should be _built in_ to the
application. It doesn't strike me as hugely complicated to add
triggers to a system that captures changes (even in a generic way --
we know this from the Slony project, which does exactly that). It's
not free from performance worries, though.
Adding some piece of intermediate stuff that could interfere with the
ACIDity of the system (and that's what this scripty thing is
advertised as) surely can't be the remedy for the problem where you
think your developed code needs an audit trail?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
On 8/28/07, Lukas Kahwe Smith <smith@pooteeweet.org> wrote: > I disagree. Auditing should ideally be done by a different system and be > implemented by someone else than the original developers. Also with the > many OSS apps out there it would require essentially a fork to add auditing. Agreed. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 8/28/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > Note that I wasn't suggesting that the person who wrote the code > should also do the auditing OK > just that it should be _built in_ to the application. Why build it specifically into the app when lower level systems, like databases or their drivers, can log it for you? > It doesn't strike me as hugely complicated to add > triggers to a system that captures changes (even in a generic way -- > we know this from the Slony project, which does exactly that). It doesn't do exactly that. PostgreSQL cannot provide true auditing without significant custom work. Audit triggers must be autonomous, which can only be performed in PostgreSQL via a dblink. > Adding some piece of intermediate stuff that could interfere with the > ACIDity of the system (and that's what this scripty thing is > advertised as) surely can't be the remedy for the problem where you > think your developed code needs an audit trail? I wouldn't personally take that approach, but it would work. I may be mistaken, but on one hand you seem to basically be saying is that one should research their app and design what is appropriate for them. On the other hand, you're saying that even if they research their app and choose to use the MySQL proxy method, they're wrong in doing so. I'd just leave it simply as, if MySQL proxy meets your needs and design objectives, and you are aware of its issues and limitations, go ahead and use it. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On Tue, Aug 28, 2007 at 10:31:32AM -0400, Jonah H. Harris wrote:
>
> Why build it specifically into the app when lower level systems, like
> databases or their drivers, can log it for you?
If the database is doing this for you in any processed way, then to
me it's part of the application. I wouldn't advocate putting extra
code out in the database client to do it, if that's what you're
asking.
> It doesn't do exactly that.
It does exactly what I suggested: logs all changes to a table in a
generic way -- i.e. Slony doesn't need to know about your application
in order to replicate your data.
> PostgreSQL cannot provide true auditing
> without significant custom work. Audit triggers must be autonomous,
> which can only be performed in PostgreSQL via a dblink.
Well, wait a minute. You're now arguing that auditing under Postgres
requires writing stuff to an independent system, which entails
significant custom work. But it isn't at all obvious to me that a
proxy-based approach won't require significant custom work too. It
doesn't exist today -- the framework does, and that's it. My point
was merely that making a generic auditing framework that fits inside
PostgreSQL is possible, and it doesn't require any proxy between the
database client and the back end.
> I may be mistaken, but on one hand you seem to basically be saying
> is that one should research their app and design what is
> appropriate for them. On the other hand, you're saying that even
> if they research their app and choose to use the MySQL proxy
> method, they're wrong in doing so.
I don't care what people do with their data, especially if they're
using MySQL. What I am arguing against is adding something like this
proxy capability to Postgres. The reason I'm arguing against such a
capability is that it looks to me like an immense man in the middle
attack just itching to happen. I think it's a misfeature, for that
reason.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz
Andrew Sullivan wrote: > I don't care what people do with their data, especially if they're > using MySQL. What I am arguing against is adding something like this > proxy capability to Postgres. The reason I'm arguing against such a > capability is that it looks to me like an immense man in the middle > attack just itching to happen. I think it's a misfeature, for that > reason. Fair enough, I now understand much better how PostgreSQL approaches making similar (or better) capabilities available. regards, Lukas
Andrew Sullivan wrote: > I don't care what people do with their data, especially if they're > using MySQL. What I am arguing against is adding something like this > proxy capability to Postgres. The reason I'm arguing against such a > capability is that it looks to me like an immense man in the middle > attack just itching to happen. I think it's a misfeature, for that > reason. Fair enough, I now understand much better how PostgreSQL approaches making similar (or better) capabilities available. regards, Lukas
Andrew Sullivan wrote: > I don't care what people do with their data, especially if they're > using MySQL. What I am arguing against is adding something like this > proxy capability to Postgres. The reason I'm arguing against such a > capability is that it looks to me like an immense man in the middle > attack just itching to happen. I think it's a misfeature, for that > reason. Fair enough, I now understand much better how PostgreSQL approaches making similar (or better) capabilities available. regards, Lukas
Lukas Kahwe Smith wrote: > Andrew Sullivan wrote: > >> I don't care what people do with their data, especially if they're >> using MySQL. What I am arguing against is adding something like this >> proxy capability to Postgres. The reason I'm arguing against such a >> capability is that it looks to me like an immense man in the middle >> attack just itching to happen. I think it's a misfeature, for that >> reason. > > Fair enough, I now understand much better how PostgreSQL approaches > making similar (or better) capabilities available. Doh, sorry for the triple post. Probably some proxy interference :) regards, Lukas
On 8/28/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > It does exactly what I suggested: logs all changes to a table in a > generic way That is not proper auditing. Proper auditing must log attempts to access and modify data... which PostgreSQL cannot > Well, wait a minute. You're now arguing that auditing under Postgres > requires writing stuff to an independent system, which entails > significant custom work. But it isn't at all obvious to me that a > proxy-based approach won't require significant custom work too. Agreed, there is no free lunch. > I don't care what people do with their data, especially if they're > using MySQL. What I am arguing against is adding something like this > proxy capability to Postgres. I agree on that point. I certainly wouldn't like to see anyone expend significant effort to make this work for Postgres. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
All, > > I don't care what people do with their data, especially if they're > > using MySQL. What I am arguing against is adding something like this > > proxy capability to Postgres. > > I agree on that point. I certainly wouldn't like to see anyone expend > significant effort to make this work for Postgres. I wasn't focussing on the auditing capabilities, although there's performance tuning/troubleshooting reasons why auditing just the proxy would be useful. I was thinking of scripting capabilities for a connection pooler in general. Sort of like an iptables for PostgreSQL connections. I don't know if MySQL proxy does this, but I can see how it would be useful for large multi-application PostgreSQL installations. For example, you could script the proxy to accept a certain number of connection errors over a certain period of time and then switch automatically to the failover server, maybe even dispatching slony commands in the process. Or to clamp down on DOSes by simply discarding too many connections in too short a time. Or to pass through all connections from a specific appserver to a test copy of the database temporarily. I think you get the idea. Mind you, this is hardly our #1 priority but it would make a nice SoC project. -- Josh Berkus PostgreSQL @ Sun San Francisco