Обсуждение: Patch set under development to add usage reporting.
Developers,
I'm a senior developer for a small Company working on
modifications to both MySQL and PostgreSQL to support better
usage reporting for DBAs. The project is called ExtSQL.
Most of our work was originally done on MySQL and we've
just gotten into PostgreSQL during the last year. I don't
want to be accused of being a 'suck up' -- but I was impressed
with how well the code is layed out and the design documentation
that was available. Very nice and we hope to keep that up
in our contribution!
The project web site has a lot of info, but here is a quick
example of what it does:
thebook=# show statistics * from db history; db | minutes | Questions | Connections | Com_delete
---------+----------------+-----------+-------------+------------thebook | 10/26/09 09:45 | 1 | 0 | 0
thebook | 10/26/09 09:44 | 8 | 1 | 6 thebook | 10/26/09 09:43 | 0 | 1
| 0
We now have a basic patch set that works and is basically
stable (not recommended for production servers!). We've dedicated
a page at our web site and it hopefully has answers to most of
your questions, and also has the patch set for download. These
are for 7.4.19 - the version included with RHEL 4.
http://www.ExtSQL.com/postgres_notes.php
We certainly welcome your feedback and thoughts on this.
There is still some hard work to be done. We have an INFORMATION
SCHEMA implementation for MySQL 5.x, but not yet for PostgreSQL.
Why this? We were a web hosting Company and were absolutely
maddened that no simple tools existed to tell us who was causing
usage spikes on a DB server shared by multiple users. We now
know!
Best regards!
-- John
____________________________________________________________________
John Murtari Software Workshop Inc.
jmurtari@thebook.com "software that fits!" (TM)
(315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/
On Fri, 30 Oct 2009, John Murtari wrote: > We now have a basic patch set that works and is basically stable (not > recommended for production servers!). We've dedicated a page at our web > site and it hopefully has answers to most of your questions, and also > has the patch set for download. These are for 7.4.19 - the version > included with RHEL 4. This is kind of interesting, but targeting 7.4.19 isn't going to get you very far toward code anyone else will use. That release is 6 years old, it's filled with unsolvable limitations, it's basically at end of life. The fact that it's bundled with RHEL4 and there are some legacy installs still floating around are the only reason it's not completely gone from everyone's radar. In short, if you actually care about your data, you should be running a newer version of the database regardless of what RHEL ships. And you should be building patches against no earlier than 8.4 if you want something that has any hope of being accepted into mainstream development. Eventually the patch will need to apply to the 8.5 work in progress source code tree before it's even a candidate to merge. You can probably get away with developing against a more stable version like 8.4.1, if you must target something people can also deploy, but even that's not ideal and will eventually turn into a code merge hurdle. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Fri, Oct 30, 2009 at 11:02 AM, John Murtari <jmurtari@thebook.com> wrote: > > The project web site has a lot of info, but here is a quick > example of what it does: > > thebook=# show statistics * from db history; > db | minutes | Questions | Connections | Com_delete > ---------+----------------+-----------+-------------+------------ > thebook | 10/26/09 09:45 | 1 | 0 | 0 > thebook | 10/26/09 09:44 | 8 | 1 | 6 > thebook | 10/26/09 09:43 | 0 | 1 | 0 > seems interesting, the syntax needs a little work but... > We now have a basic patch set that works and is basically > stable (not recommended for production servers!). We've dedicated > a page at our web site and it hopefully has answers to most of > your questions, and also has the patch set for download. These > are for 7.4.19 - the version included with RHEL 4. > oops... we don't add new features to old branches just bug fixes... you need to make that patchset work in 8.5 (currently in development) > http://www.ExtSQL.com/postgres_notes.php > > We certainly welcome your feedback and thoughts on this. > There is still some hard work to be done. We have an INFORMATION > SCHEMA implementation for MySQL 5.x, but not yet for PostgreSQL. > we have an information schema since 7.4 (http://www.postgresql.org/docs/7.4/static/information-schema.html) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Greg
> > We now have a basic patch set that works and is basically stable (not > > recommended for production servers!).
We'vededicated a page at our web > > site and it hopefully has answers to most of your questions, and also > > has the
patchset for download. These are for 7.4.19 - the version > > included with RHEL 4.
http://www.ExtSQL.com/postgres_notes.php> > This is kind of interesting, but targeting 7.4.19 isn't going to
getyou > very far toward code anyone else will use. That release is 6 years old, > it's filled with unsolvable
limitations,it's basically at end of life. > The fact that it's bundled with RHEL4 and there are some legacy installs >
stillfloating around are the only reason it's not completely gone from > everyone's radar.> > In short, if you actually
careabout your data, you should be running a > newer version of the database regardless of what RHEL ships. And you >
shouldbe building patches against no earlier than 8.4 if you want > something that has any hope of being accepted into
mainstreamdevelopment. > Eventually the patch will need to apply to the 8.5 work in progress source > code tree before
it'seven a candidate to merge. You can probably get > away with developing against a more stable version like 8.4.1,
ifyou must > target something people can also deploy, but even that's not ideal and > will eventually turn into a code
mergehurdle.
Yes, thanks for the recommendation and I do agree. I think we
got started with 7.4.19 because we run RHEL4 and had a postgresql
installation in support of a Canit anti-SPAM system -- it gave us
something real to test against.
We were trying to decided what later release to target, looks
like we'll go for 8.4 and 8.5 as staff/work permits. Any feedback on
the syntax/output is welcome.
Best regards!
-- John
____________________________________________________________________
John Murtari Software Workshop Inc.
jmurtari@thebook.com "software that fits!" (TM)
(315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/
Jaime,
> > The project web site has a lot of info, but here is a quick> > example of what it does:> >
thebook=# show statistics * from db history; db | minutes | Questions | Connections | Com_delete
---------+----------------+-----------+-------------+------------thebook | 10/26/09 09:45 | 1 | 0 | 0
thebook | 10/26/09 09:44 | 8 | 1 | 6 thebook | 10/26/09 09:43 | 0 | 1
| 0
> > seems interesting, the syntax needs a little work but...> > > http://www.ExtSQL.com/postgres_notes.php> >> > We
certainlywelcome your feedback and thoughts on this.> > There is still some hard work to be done. ÃÂ We have an
INFORMATION>> SCHEMA implementation for MySQL 5.x, but not yet for PostgreSQL.> >> > we have an information schema
since7.4> (http://www.postgresql.org/docs/7.4/static/information-schema.html)
If you have time to give us any feedback on syntax/output
it is welcome. Regarding INFORMATION SCHEMA, we saw it in 7.4.19 but
are really struggling with the implementation. In MySQL 5.0.x, the
schema tables are created/populated when asked for as temporary
tables. There were some built in routines to create the schema tables
when needed. It was fairly easy for us to take data from our
structures and fill the tables.
My first scan on PosgreSQL gave me the impression the tables
have more stability and are updated on an ongoing basis? If you have
any pointers to schema table creation that would be great!
Best regards!
-- John
____________________________________________________________________
John Murtari Software Workshop Inc.
jmurtari@thebook.com "software that fits!" (TM)
(315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/
On fre, 2009-10-30 at 12:02 -0400, John Murtari wrote: > The project web site has a lot of info, but here is a quick > example of what it does: > > thebook=# show statistics * from db history; > db | minutes | Questions | Connections | Com_delete > ---------+----------------+-----------+-------------+------------ > thebook | 10/26/09 09:45 | 1 | 0 | 0 > thebook | 10/26/09 09:44 | 8 | 1 | 6 > thebook | 10/26/09 09:43 | 0 | 1 | 0 We already have various statistics views. They don't cover exactly what you are doing here (aggregate by user and host), but it would be simpler to extend and augment them instead of introducing a completely new syntax. > We certainly welcome your feedback and thoughts on this. > There is still some hard work to be done. We have an INFORMATION > SCHEMA implementation for MySQL 5.x, but not yet for PostgreSQL. The information schema is defined in the SQL standard. Unless this stuff one day appears there, the information schema is the wrong place to look. The patch itself appears to be licensed under the GPL, which means we can't even look at it.
Peter, > The patch itself appears to be licensed under the GPL, which means we > can't even look at it. We can look at it all we want, we just can't copy it. --Josh Berkus
Peter,
> > The project web site has a lot of info, but here is a quick> > example of what it does:> > > > thebook=# show
statistics* from db history;> > db | minutes | Questions | Connections | Com_delete > >
---------+----------------+-----------+-------------+------------>> thebook | 10/26/09 09:45 | 1 | 0
|0 > > thebook | 10/26/09 09:44 | 8 | 1 | 6 > > thebook | 10/26/09 09:43 | 0
| 1 | 0 > > We already have various statistics views. They don't cover exactly what> you are
doinghere (aggregate by user and host), but it would be simpler> to extend and augment them instead of introducing a
completelynew> syntax.> > > We certainly welcome your feedback and thoughts on this.> > There is still some hard
workto be done. We have an INFORMATION> > SCHEMA implementation for MySQL 5.x, but not yet for PostgreSQL.> > The
informationschema is defined in the SQL standard. Unless this> stuff one day appears there, the information schema is
thewrong place> to look.
> The patch itself appears to be licensed under the GPL, which means we> can't even look at it.>
I had taken a look at existing statistics reporting (at
least in the 7.4.x tree) and didn't see a good analog to what this
provides. Part of the key point of this is getting some standard
syntax.
You may not have had much time to review material at the
project site, http://www.ExtSQL.com/ -- but we would like to see
this as part of the SQL standard. I represent our Company on the
US ANSI (DM32) committee with responsibility for SQL.
We had proposed this as a comment on an earlier version of
the standard. From discussion with some senior committee members,
it would appear the best fit for this type of capability might
be in the INFORMATION SCHEMA part of the standard. We also had
developed the "SHOW STATISTICS" syntax for DB version that don't
support INFORMATION SCHEMA.
I had reviewed statistics reporting in DB2, Oracle, and
SQL server. It was VERY different between implementations, much
seemed geared toward query optimization (which makes sense) and
tends to be implementation specific.
As you can see, the goal here is reporting on activity
at the SQL level.
Regarding the GPL limitation. That will probably be
removed on code we release to PostgreSQL -- hadn't really thought
about the fact it would be a show stopper. Thanks for bringing
that up.
Best regards!
-- John
____________________________________________________________________
John Murtari Software Workshop Inc.
jmurtari@thebook.com "software that fits!" (TM)
(315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/
On lör, 2009-10-31 at 13:37 -0400, John Murtari wrote: > I had taken a look at existing statistics reporting (at > least in the 7.4.x tree) and didn't see a good analog to what this > provides. The statistics collector detailed at <http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html> appears to do pretty much the same, although on a slightly lower level. But that's the logical place where to add that in PostgreSQL. > We had proposed this as a comment on an earlier version of > the standard. From discussion with some senior committee members, > it would appear the best fit for this type of capability might > be in the INFORMATION SCHEMA part of the standard. We also had > developed the "SHOW STATISTICS" syntax for DB version that don't > support INFORMATION SCHEMA. I don't really get the point of the SHOW STATISTICS command. There is already a command whose purpose is to retrieve data in tabular form, namely SELECT.
Peter Eisentraut <peter_e@gmx.net> writes:
> On lör, 2009-10-31 at 13:37 -0400, John Murtari wrote:
>> ... We also had
>> developed the "SHOW STATISTICS" syntax for DB version that don't
>> support INFORMATION SCHEMA.
> I don't really get the point of the SHOW STATISTICS command. There is
> already a command whose purpose is to retrieve data in tabular form,
> namely SELECT.
I presume we need not worry about that, since the SQL committee are
certainly not going to standardize something that's only there for
DBs that don't support information_schema.
Actually, the idea of standardizing anything at all in this area seems
pretty bogus. The events that are interesting to measure are below the
semantic level of the standard --- for instance, how are you going to
count index searches, when the standard doesn't even recognize the
existence of indexes? Let alone things like buffer cache hits, which is
a concept that might not exist at all in some implementations. I think
we can safely assume that the proposed standardization effort will go
nowhere, and just look at whether this is interesting for Postgres.
regards, tom lane
Peter> > I had taken a look at existing statistics reporting (at> > least in the 7.4.x tree) and didn't see a goodanalog to what this> > provides.> > The statistics collector detailed at> <http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html>>appears to do pretty much the same, although on aslightly lower level.> But that's the logical place where to add that in PostgreSQL.> > > We had proposed this asa comment on an earlier version of> > the standard. From discussion with some senior committee members,> > it would appearthe best fit for this type of capability might> > be in the INFORMATION SCHEMA part of the standard. We also had>> developed the "SHOW STATISTICS" syntax for DB version that don't> > support INFORMATION SCHEMA.> > I don't really getthe point of the SHOW STATISTICS command. There is> already a command whose purpose is to retrieve data in tabular form,>namely SELECT. Okay, thank you. We will take another look at those items. Best regards! -- John ____________________________________________________________________ John Murtari Software Workshop Inc. jmurtari@thebook.com "software that fits!" (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/
Tom Lane writes:> Peter Eisentraut <peter_e@gmx.net> writes:
> > I don't really get the point of the SHOW STATISTICS command. There is> > already a command whose purpose is to
retrievedata in tabular form,> > namely SELECT.> > I presume we need not worry about that, since the SQL committee are>
certainlynot going to standardize something that's only there for> DBs that don't support information_schema.> >
Actually,the idea of standardizing anything at all in this area seems> pretty bogus. The events that are interesting
tomeasure are below the> semantic level of the standard --- for instance, how are you going to> count index searches,
whenthe standard doesn't even recognize the> existence of indexes? Let alone things like buffer cache hits, which is>
aconcept that might not exist at all in some implementations. I think> we can safely assume that the proposed
standardizationeffort will go> nowhere, and just look at whether this is interesting for Postgres.>
Thanks for the feedback. I do agree, and sorry for any
confusion, the SHOW syntax was not meant for standardization at the
SQL level. We will keep your other remarks in mind as we proceed.
Best regards!
-- John
____________________________________________________________________
John Murtari Software Workshop Inc.
jmurtari@thebook.com "software that fits!" (TM)
(315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/