Re: [HACKERS] Patch set under development to add usage reporting.
Greg 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 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. 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 MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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 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) 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 MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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 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. 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 MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
Peter 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. Okay, thank you. We will take another look at those items. Best regards! -- John John MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
Tom Lane writes: Peter Eisentraut pete...@gmx.net writes: 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. 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 MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 MurtariSoftware Workshop Inc. jmurt...@thebook.com software that fits! (TM) (315) 944-0999 (x-211) http://www.SoftwareWorkshop.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
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 gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch set under development to add usage reporting.
On Fri, Oct 30, 2009 at 11:02 AM, John Murtari jmurt...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
