Re: Statistics tab, "Tables" node
Re: Statistics tab, "Tables" node
От:
Magnus Hagander <magnus@hagander.net>
Дата:
Guillaume Lelarge wrote: > Hi, > > When one clicks on the statistics tab with "Tables" node selected, > very few statistics are shown : > * tuples inserted > * tuples updated > * tuples deleted > * size > > I'm wondering if we could put all pg_stat_all_tables columns here. > That's a lot of informations, but at least some informations seem to > be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, > last_analyze, last_autoanalyze. I would also like to add some other > stuff like the % of the table in cache (if pg_buffercache is > available). > > Comments, ideas ? +1 on the stuff from pg_stat (but make sure to check which columns are available in which version) -1 on the pg_buffercache stuff. That one can be populated using a special option I think, but since pg_buffercache takes out some fairly heavy locking, we shouldn't do that by default. //Magnus
Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Hi, When one clicks on the statistics tab with "Tables" node selected, very few statistics are shown : * tuples inserted * tuples updated * tuples deleted * size I'm wondering if we could put all pg_stat_all_tables columns here. That's a lot of informations, but at least some informations seem to be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze. I would also like to add some other stuff like the % of the table in cache (if pg_buffercache is available). Comments, ideas ? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Magnus Hagander a écrit :
> Guillaume Lelarge wrote:
>> [...]
>> I'm wondering if we could put all pg_stat_all_tables columns here.
>> That's a lot of informations, but at least some informations seem to
>> be worth it : n_live_tup, n_dead_tup, last_vacuum, last_autovacuum,
>> last_analyze, last_autoanalyze. I would also like to add some other
>> stuff like the % of the table in cache (if pg_buffercache is
>> available).
>>
>> Comments, ideas ?
>
> +1 on the stuff from pg_stat (but make sure to check which columns are
> available in which version)
>
The patch attached takes care of this.
> -1 on the pg_buffercache stuff. That one can be populated using a
> special option I think, but since pg_buffercache takes out some fairly
> heavy locking, we shouldn't do that by default.
>
I agree, it would be better to have an option (disabled by default).
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Index: pgadmin/schema/pgTable.cpp
===================================================================
--- pgadmin/schema/pgTable.cpp (révision 7274)
+++ pgadmin/schema/pgTable.cpp (copie de travail)
@@ -768,10 +768,27 @@
statistics->AddColumn(_("Tuples inserted"), 50);
statistics->AddColumn(_("Tuples updated"), 50);
statistics->AddColumn(_("Tuples deleted"), 50);
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ {
+ statistics->AddColumn(_("Tuples HOT updated"), 50);
+ statistics->AddColumn(_("Live tuples"), 50);
+ statistics->AddColumn(_("Dead tuples"), 50);
+ }
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ {
+ statistics->AddColumn(_("Last vacuum"), 50);
+ statistics->AddColumn(_("Last autovacuum"), 50);
+ statistics->AddColumn(_("Last analyze"), 50);
+ statistics->AddColumn(_("Last autoanalyze"), 50);
+ }
if (hasSize)
statistics->AddColumn(_("Size"), 60);
wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del");
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ sql += wxT(", n_tup_hot_upd, n_live_tup, n_dead_tup");
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ sql += wxT(", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze");
if (hasSize)
sql += wxT(", pg_size_pretty(pg_relation_size(st.relid)")
wxT(" + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END")
@@ -781,20 +798,35 @@
wxT(" JOIN pg_class cl on cl.oid=st.relid\n")
wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName())
+ wxT("\n ORDER BY relname");
-
+
pgSet *stats = GetDatabase()->ExecuteSet(sql);
if (stats)
{
long pos=0;
+ int i;
while (!stats->Eof())
{
+ i = 4;
statistics->InsertItem(pos, stats->GetVal(wxT("relname")), PGICON_STATISTICS);
statistics->SetItem(pos, 1, stats->GetVal(wxT("n_tup_ins")));
statistics->SetItem(pos, 2, stats->GetVal(wxT("n_tup_upd")));
statistics->SetItem(pos, 3, stats->GetVal(wxT("n_tup_del")));
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ {
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_tup_hot_upd")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_live_tup")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_dead_tup")));
+ }
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ {
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_vacuum")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autovacuum")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_analyze")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autoanalyze")));
+ }
if (hasSize)
- statistics->SetItem(pos, 4, stats->GetVal(wxT("size")));
+ statistics->SetItem(pos, i, stats->GetVal(wxT("size")));
stats->MoveNext();
pos++;
}
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Dave Page a écrit :
> On Mon, May 5, 2008 at 4:38 PM, Guillaume Lelarge
> wrote:
>>> +1 on the stuff from pg_stat (but make sure to check which columns are
>>> available in which version)
>>>
>>>
>> The patch attached takes care of this.
>
> It looks good to me, except the individual table stats should also
> show the new data (ie. when you click on a single table). BTW, HOT
> update count is cool - didn't know we added that :-)
>
Done. See attached patch.
> The display is getting a little wide - I think it's OK for now, but if
> anything else gets added we need to think about better ways to present
> the data I think.
>
I completely agree.
> What might be a nice enhancement, is some auto-sizing based on column
> heading width. Wanna take a peek at that as well?
>
I'll get a look at it tonight. Can I apply this patch first and then
work on the "auto-sizing columns" patch ?
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Index: pgadmin/schema/pgTable.cpp
===================================================================
--- pgadmin/schema/pgTable.cpp (révision 7278)
+++ pgadmin/schema/pgTable.cpp (copie de travail)
@@ -768,10 +768,27 @@
statistics->AddColumn(_("Tuples inserted"), 50);
statistics->AddColumn(_("Tuples updated"), 50);
statistics->AddColumn(_("Tuples deleted"), 50);
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ {
+ statistics->AddColumn(_("Tuples HOT updated"), 50);
+ statistics->AddColumn(_("Live tuples"), 50);
+ statistics->AddColumn(_("Dead tuples"), 50);
+ }
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ {
+ statistics->AddColumn(_("Last vacuum"), 50);
+ statistics->AddColumn(_("Last autovacuum"), 50);
+ statistics->AddColumn(_("Last analyze"), 50);
+ statistics->AddColumn(_("Last autoanalyze"), 50);
+ }
if (hasSize)
statistics->AddColumn(_("Size"), 60);
wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del");
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ sql += wxT(", n_tup_hot_upd, n_live_tup, n_dead_tup");
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ sql += wxT(", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze");
if (hasSize)
sql += wxT(", pg_size_pretty(pg_relation_size(st.relid)")
wxT(" + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END")
@@ -781,20 +798,35 @@
wxT(" JOIN pg_class cl on cl.oid=st.relid\n")
wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName())
+ wxT("\n ORDER BY relname");
-
+
pgSet *stats = GetDatabase()->ExecuteSet(sql);
if (stats)
{
long pos=0;
+ int i;
while (!stats->Eof())
{
+ i = 4;
statistics->InsertItem(pos, stats->GetVal(wxT("relname")), PGICON_STATISTICS);
statistics->SetItem(pos, 1, stats->GetVal(wxT("n_tup_ins")));
statistics->SetItem(pos, 2, stats->GetVal(wxT("n_tup_upd")));
statistics->SetItem(pos, 3, stats->GetVal(wxT("n_tup_del")));
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ {
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_tup_hot_upd")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_live_tup")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("n_dead_tup")));
+ }
+ if (GetConnection()->BackendMinimumVersion(8, 2))
+ {
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_vacuum")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autovacuum")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_analyze")));
+ statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autoanalyze")));
+ }
if (hasSize)
- statistics->SetItem(pos, 4, stats->GetVal(wxT("size")));
+ statistics->SetItem(pos, i, stats->GetVal(wxT("size")));
stats->MoveNext();
pos++;
}
@@ -816,9 +848,18 @@
wxT(", idx_scan AS ") + qtIdent(_("Index Scans")) +
wxT(", idx_tup_fetch AS ") + qtIdent(_("Index Tuples Fetched"))+
wxT(", n_tup_ins AS ") + qtIdent(_("Tuples Inserted"))+
- wxT(", n_tup_upd AS ") + qtIdent(_("Tuples Updated")) +
- wxT(", n_tup_del AS ") + qtIdent(_("Tuples Deleted")) +
- wxT(", heap_blks_read AS ") + qtIdent(_("Heap Blocks Read")) +
+ wxT(", n_tup_upd AS ") + qtIdent(_("Tuples Updated"))+
+ wxT(", n_tup_del AS ") + qtIdent(_("Tuples Deleted"));
+
+ if (GetConnection()->BackendMinimumVersion(8, 3))
+ {
+ sql +=
+ wxT(", n_tup_hot_upd AS ") + qtIdent(_("Tuples HOT Updated"))+
+ wxT(", n_live_tup AS ") + qtIdent(_("Live Tuples"))+
+ wxT(", n_dead_tup AS ") + qtIdent(_("Dead Tuples"));
+ }
+
+ sql += wxT(", heap_blks_read AS ") + qtIdent(_("Heap Blocks Read")) +
wxT(", heap_blks_hit AS ") + qtIdent(_("Heap Blocks Hit")) +
wxT(", idx_blks_read AS ") + qtIdent(_("Index Blocks Read")) +
wxT(", idx_blks_hit AS ") + qtIdent(_("Index Blocks Hit")) +
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Dave Page a écrit : > On Tue, May 6, 2008 at 2:49 PM, Guillaume Lelarge > wrote: >>> What might be a nice enhancement, is some auto-sizing based on column >>> heading width. Wanna take a peek at that as well? >>> >> I'll get a look at it tonight. Can I apply this patch first and then work on >> the "auto-sizing columns" patch ? > > Sure, go ahead. > Done, thanks. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Dave Page a écrit : > [...] > What might be a nice enhancement, is some auto-sizing based on column > heading width. Wanna take a peek at that as well? > I finally found some time to work on this. wxwidgets allows to specify two "default" values : * wxLIST_AUTOSIZE * wxLIST_AUTOSIZE_USEHEADER The last one does what we want, but our ctlListView::AddColumn method's handling of default values was buggy. The attached patch fixes this, and changes pgTableCollection::ShowStatistics method to handle the new default value (wxLIST_AUTOSIZE_USEHEADER). Do we want this for the 1.8 branch ? Obviously, this is a bug fix, but for the fix to really work, we need more changes (ie using the default value in each, at least some of them, call to ctlListView::AddColumn). Comments? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Guillaume Lelarge a écrit :
> [...]
> The last one does what we want, but our ctlListView::AddColumn method's
> handling of default values was buggy. The attached patch fixes this, and
> changes pgTableCollection::ShowStatistics method to handle the new
> default value (wxLIST_AUTOSIZE_USEHEADER).
>
/me wishes he has a mail client that would tell him he forgot the
"attached patch" :-/
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
Index: pgadmin/ctl/ctlListView.cpp
===================================================================
--- pgadmin/ctl/ctlListView.cpp (révision 7285)
+++ pgadmin/ctl/ctlListView.cpp (copie de travail)
@@ -45,7 +45,14 @@
void ctlListView::AddColumn(const wxChar *text, int size, int format)
{
- InsertColumn(GetColumnCount(), text, format, ConvertDialogToPixels(wxPoint(size,0)).x);
+ if (size == wxLIST_AUTOSIZE || size == wxLIST_AUTOSIZE_USEHEADER)
+ {
+ InsertColumn(GetColumnCount(), text, format, size);
+ }
+ else
+ {
+ InsertColumn(GetColumnCount(), text, format, ConvertDialogToPixels(wxPoint(size,0)).x);
+ }
}
Index: pgadmin/include/ctl/ctlListView.h
===================================================================
--- pgadmin/include/ctl/ctlListView.h (révision 7285)
+++ pgadmin/include/ctl/ctlListView.h (copie de travail)
@@ -28,7 +28,7 @@
void CreateColumns(wxImageList *images, const wxString &left, const wxString &right, int leftSize=60);
- void AddColumn(const wxChar *text, int size=-1, int format=wxLIST_FORMAT_LEFT);
+ void AddColumn(const wxChar *text, int size=wxLIST_AUTOSIZE_USEHEADER, int format=wxLIST_FORMAT_LEFT);
long AppendItem(int icon, const wxChar *val, const wxChar *val2=0, const wxChar *val3=0);
long AppendItem(const wxChar *val, const wxChar *val2=0, const wxChar *val3=0)
Index: pgadmin/schema/pgTable.cpp
===================================================================
--- pgadmin/schema/pgTable.cpp (révision 7285)
+++ pgadmin/schema/pgTable.cpp (copie de travail)
@@ -764,25 +764,25 @@
// Add the statistics view columns
statistics->ClearAll();
- statistics->AddColumn(_("Table"), 100);
- statistics->AddColumn(_("Tuples inserted"), 50);
- statistics->AddColumn(_("Tuples updated"), 50);
- statistics->AddColumn(_("Tuples deleted"), 50);
+ statistics->AddColumn(_("Table Name"));
+ statistics->AddColumn(_("Tuples inserted"));
+ statistics->AddColumn(_("Tuples updated"));
+ statistics->AddColumn(_("Tuples deleted"));
if (GetConnection()->BackendMinimumVersion(8, 3))
{
- statistics->AddColumn(_("Tuples HOT updated"), 50);
- statistics->AddColumn(_("Live tuples"), 50);
- statistics->AddColumn(_("Dead tuples"), 50);
+ statistics->AddColumn(_("Tuples HOT updated"));
+ statistics->AddColumn(_("Live tuples"));
+ statistics->AddColumn(_("Dead tuples"));
}
if (GetConnection()->BackendMinimumVersion(8, 2))
{
- statistics->AddColumn(_("Last vacuum"), 50);
- statistics->AddColumn(_("Last autovacuum"), 50);
- statistics->AddColumn(_("Last analyze"), 50);
- statistics->AddColumn(_("Last autoanalyze"), 50);
+ statistics->AddColumn(_("Last vacuum"));
+ statistics->AddColumn(_("Last autovacuum"));
+ statistics->AddColumn(_("Last analyze"));
+ statistics->AddColumn(_("Last autoanalyze"));
}
if (hasSize)
- statistics->AddColumn(_("Size"), 60);
+ statistics->AddColumn(_("Size"));
wxString sql=wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del");
if (GetConnection()->BackendMinimumVersion(8, 3))
Re: Statistics tab, "Tables" node
От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
Dave Page a écrit : > On Mon, May 12, 2008 at 10:21 AM, Guillaume Lelarge > wrote: >> Guillaume Lelarge a écrit : >> >>> [...] >>> >>> The last one does what we want, but our ctlListView::AddColumn method's >> handling of default values was buggy. The attached patch fixes this, and >> changes pgTableCollection::ShowStatistics method to handle the new default >> value (wxLIST_AUTOSIZE_USEHEADER). >>> >> /me wishes he has a mail client that would tell him he forgot the "attached >> patch" :-/ > > Yeah, me too :-) > AFAIK, gmail does this. Unfortunately, my Thunderbird is less smart. > Patch looks good to my eyeballs - please apply. > Thanks, done. Now working on the refresh code. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: Statistics tab, "Tables" node
От:
"Dave Page" <dpage@pgadmin.org>
Дата:
On Mon, May 5, 2008 at 4:38 PM, Guillaume Lelarge wrote: > > > > +1 on the stuff from pg_stat (but make sure to check which columns are > > available in which version) > > > > > > The patch attached takes care of this. It looks good to me, except the individual table stats should also show the new data (ie. when you click on a single table). BTW, HOT update count is cool - didn't know we added that :-) The display is getting a little wide - I think it's OK for now, but if anything else gets added we need to think about better ways to present the data I think. What might be a nice enhancement, is some auto-sizing based on column heading width. Wanna take a peek at that as well? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: Statistics tab, "Tables" node
От:
"Dave Page" <dpage@pgadmin.org>
Дата:
On Tue, May 6, 2008 at 2:49 PM, Guillaume Lelarge wrote: > > What might be a nice enhancement, is some auto-sizing based on column > > heading width. Wanna take a peek at that as well? > > > I'll get a look at it tonight. Can I apply this patch first and then work on > the "auto-sizing columns" patch ? Sure, go ahead. Thanks, Dave -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: Statistics tab, "Tables" node
От:
"Dave Page" <dpage@pgadmin.org>
Дата:
On Mon, May 12, 2008 at 10:21 AM, Guillaume Lelarge wrote: > Guillaume Lelarge a écrit : > > > [...] > > > > The last one does what we want, but our ctlListView::AddColumn method's > handling of default values was buggy. The attached patch fixes this, and > changes pgTableCollection::ShowStatistics method to handle the new default > value (wxLIST_AUTOSIZE_USEHEADER). > > > > > > /me wishes he has a mail client that would tell him he forgot the "attached > patch" :-/ Yeah, me too :-) Patch looks good to my eyeballs - please apply. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
