Thread: It takes a long time for pgAdmin to retrieve database schema details

<div class="Section1"><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">Hi,</span><pclass="MsoNormal" style="margin-left:18.0pt"><span lang="EN-US"> </span><p class="MsoNormal"
style="margin-left:18.0pt"><spanlang="EN-US">we use gpAdminIII 1.10.3 with Greeenplum database 3.3.5.0 on Solaris X64
10u7,one of our databases has 36 schemas, one of which has 716 tables, and the whole database size is about 4~5T. When
usersopen pgAdminIII and click the database node under the Databases node it takes a long time for pgAdminIII to
“restoringprevious environment….”, when users click one of the schemas, it also takes a long time for pgAdminIII to
“retrievingSchema details…”, and the whole Interface is haling, I think this is due to this operation is single
threaded.Another suggestion is can you improve gpAdmin to retrieve as little detail information as possible, just to
retrieveit when user really need it, especially for high cost operations such as select count(*) from table.</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Xiaobo Gu</span></div> 

Le 14/07/2010 17:48, 顾小波 a écrit :
> [...]
> we use gpAdminIII 1.10.3 with Greeenplum database 3.3.5.0 on Solaris X64
> 10u7, one of our databases has 36 schemas, one of which has 716 tables,
> and the whole database size is about 4~5T.

Would it be possible to get a copy of your schema? (not the data of
course, but the objects definition) The idea is to restore them on my
computer, and fix the performance issue you have.

> When users open pgAdminIII
> and click the database node under the Databases node it takes a long
> time for pgAdminIII to “restoring previous environment….”, when users
> click one of the schemas, it also takes a long time for pgAdminIII to
> “retrieving Schema details…”, and the whole Interface is haling, I think
> this is due to this operation is single threaded. Another suggestion is
> can you improve gpAdmin to retrieve as little detail information as
> possible, just to retrieve it when user really need it, especially for
> high cost operations such as select count(*) from table.
> 

Yes, it's single threaded. There's not a lot of count(*) uses, mainly on
system catalogs. There's one on the data (which would cause you bad
issues on a 4TB database), but it's only launched if you have bad
statistics.

Regards.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Hi Guillaume,Have you received our DDL database dump?

Xiaobo.Gu

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Thursday, July 15, 2010 5:37 AM
To: 顾小波
Cc: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] It takes a long time for pgAdmin to retrieve database schema details

Le 14/07/2010 17:48, 顾小波 a écrit :
> [...]
> we use gpAdminIII 1.10.3 with Greeenplum database 3.3.5.0 on Solaris X64
> 10u7, one of our databases has 36 schemas, one of which has 716 tables,
> and the whole database size is about 4~5T.

Would it be possible to get a copy of your schema? (not the data of
course, but the objects definition) The idea is to restore them on my
computer, and fix the performance issue you have.

> When users open pgAdminIII
> and click the database node under the Databases node it takes a long
> time for pgAdminIII to “restoring previous environment….”, when users
> click one of the schemas, it also takes a long time for pgAdminIII to
> “retrieving Schema details…”, and the whole Interface is haling, I think
> this is due to this operation is single threaded. Another suggestion is
> can you improve gpAdmin to retrieve as little detail information as
> possible, just to retrieve it when user really need it, especially for
> high cost operations such as select count(*) from table.
>

Yes, it's single threaded. There's not a lot of count(*) uses, mainly on
system catalogs. There's one on the data (which would cause you bad
issues on a 4TB database), but it's only launched if you have bad
statistics.

Regards.


--
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 19/07/2010 15:01, 顾小波 a écrit :
> Hi Guillaume,
>     Have you received our DDL database dump?
> 

Yes, I received it. I'll work on it ASAP. I'll probably have a hard time
to restore it. The encoding (GB18030) does not make that task easy :-/

If you have any idea on how to make this encoding available on
Debian/Ubuntu, I'm open for ideas :)


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 19/07/2010 15:10, Guillaume Lelarge a écrit :
> Le 19/07/2010 15:01, 顾小波 a écrit :
>> Hi Guillaume,
>>     Have you received our DDL database dump?
>>
> 
> Yes, I received it. I'll work on it ASAP. I'll probably have a hard time
> to restore it. The encoding (GB18030) does not make that task easy :-/
> 
> If you have any idea on how to make this encoding available on
> Debian/Ubuntu, I'm open for ideas :)
> 

BTW, thanks for sending me your DDL database dump. It's been a long time
I wanted to work on big schemas, but it's difficult to create one and to
get one. So, thanks. I'll see what I can do with this.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Can you reinstall your Debian/Ubuntu, and install/set default encoding to GH18030? And you can use vmware virtual
machinetechnoloyies.  



Xiaobo.Gu


-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Monday, July 19, 2010 9:11 PM
To: 顾小波
Cc: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] It takes a long time for pgAdmin to retrieve database schema details

Le 19/07/2010 15:01, 顾小波 a écrit :
> Hi Guillaume,
>     Have you received our DDL database dump?
>

Yes, I received it. I'll work on it ASAP. I'll probably have a hard time
to restore it. The encoding (GB18030) does not make that task easy :-/

If you have any idea on how to make this encoding available on
Debian/Ubuntu, I'm open for ideas :)


--
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 19/07/2010 15:18, 顾小波 a écrit :
> Can you reinstall your Debian/Ubuntu, and install/set default encoding to GH18030? And you can use vmware virtual
machinetechnoloyies. 
 
> 

I'll install a VM with this encoding. Should be simpler.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 19/07/2010 19:25, Guillaume Lelarge a écrit :
> Le 19/07/2010 15:18, 顾小波 a écrit :
>> Can you reinstall your Debian/Ubuntu, and install/set default encoding to GH18030? And you can use vmware virtual
machinetechnoloyies. 
 
>>
> 
> I'll install a VM with this encoding. Should be simpler.
> 

What I completely forgot is that you were talking about a *Greenplum*
database. And that you have specific Greenplum objets in it. Which can't
be restored in a vanilla PostgreSQL. I'm afraid I won't be able to help
with this.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 28/07/2010 03:20, Amber a écrit :
> [...]
> You can download and install Greenplum Single Node Edition which is freely
> available from Greenplum, you can get 3.3.6.1 from
> http://www.greenplum.com/database/server-downloads/
> 

Well, I'll certainly do when I'll be interested to give a look at
Greenplum. But right now, sorry, it is just way too much work.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 28/07/2010 11:16, Amber a écrit :
> [...]
> But if you already know some obvious issues, can you fix them. I'll try to
> fix the ddl to allow it to be loaded into PostgreSQL.
> 

If I know some issues, I would already work on them. If you can fix the
DDL, it would help. Not sure I will find any issue though. It may be
that your schema is too big. IOW, I'm still interested to work on your
issue, but not if it requires me to install a completely unknown software.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com