Обсуждение: Slow connect due to some queries
Hi,<br /><br /><br />I've been using pgAdmin for a long time but over time it has become more and more frustrating to useit.<br />It's a great application that performs everything I need, however, when first connecting to servers with manydatabases, each with many tables, views and other objects (over a VPN) it can often take 20-30 seconds while pgAdminis hung before it completes running all the prefetch queries.<br /><br />I've analyzed its logs and it appears thatsome queries are the main culprit.<br /><br /><br />For example (15-20 seconds to run this):<br />SELECT oid, format_type(oid,NULL) AS typname FROM pg_type<br /><br />We have quite a few entries there (every toasted table, every viewand every table have a couple of entries there).<br />SELECT count(*) AS typname FROM pg_type<br />35468<br /><br />Also,this same query gets apparently issued twice:<br />2010-01-13 09:44:15 QUERY : Set query (pgdb:5432): SELECT oid,format_type(oid, NULL) AS typname FROM pg_type<br />2010-01-13 09:44:31 QUERY : Set query (pgdb:5432): SELECT oid, format_type(oid,NULL) AS typname FROM pg_type<br />2010-01-13 09:44:52 STATUS : Restoring previous environment... (37.31secs)<br /><br />Causing the application a long time to "connect".<br />I was wondering if there's either a way tobring a partial subset of the data (is all of it needed?) or perhaps fetch it lazily (on demand).<br /><br />Thanks,<br/> Sefer.<br /><br /><br /><br /><br /><hr />Hotmail: Free, trusted and rich email service. <a href="http://clk.atdmt.com/GBL/go/196390708/direct/01/"target="_new">Get it now.</a>
Hi, Le 13/01/2010 17:16, Tzvi R a écrit : > [...] > I've been using pgAdmin for a long time but over time it has become more and more frustrating to use it. > It's a great application that performs everything I need, however, when first connecting to servers with many databases,each with many tables, views and other objects (over a VPN) it can often take 20-30 seconds while pgAdmin is hungbefore it completes running all the prefetch queries. > Actually, pgAdmin only looks at your maintenance database or at your last connected database. How many databases/objects do you have? > I've analyzed its logs and it appears that some queries are the main culprit. > > For example (15-20 seconds to run this): > SELECT oid, format_type(oid, NULL) AS typname FROM pg_type > > We have quite a few entries there (every toasted table, every view and every table have a couple of entries there). > SELECT count(*) AS typname FROM pg_type > 35468 > That, for sure, is a huge number. A newly created database only have 283 types (on 8.4). > Also, this same query gets apparently issued twice: > 2010-01-13 09:44:15 QUERY : Set query (pgdb:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type > 2010-01-13 09:44:31 QUERY : Set query (pgdb:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type > 2010-01-13 09:44:52 STATUS : Restoring previous environment... (37.31 secs) > > Causing the application a long time to "connect". > I was wondering if there's either a way to bring a partial subset of the data (is all of it needed?) or perhaps fetch itlazily (on demand). > Partial subset, no. On demand... don't know. What I do know is that we have a fix for the 1.12 release that will make you happy: Cache datatypes in dlgTable, so they don't have to be reloaded by dlgColumn, which may be used multiple times when creating a table [Sachin Srivastava]. We still load all pgtypes, but we use a cache to stop loading all the data each time a new column is added; -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Hi,<br /><br /><br />A quick overview of our database server:<br />* Four databases.<br />* Each database has about 20 schemas.<br/><br /><br />The largest database contains:<br />* select count(*) from pg_class where relkind = 'v'<br /> 101<br/>* select count(*) from pg_class where relkind = 'r'<br /> 11911 (about 500 tables in each schema, I know, it'sa lot - but I'd bet it's not uncommon)<br />* About 10 sequences.<br />* About 150 functions.<br /><br /><br />selectcount(*) from pg_class<br />> 36444<br /><br /><br />All these tables are large ones and have some toasted rows(you can see it in pg_type).<br /><br /><br />Those queries are rather fast, it's just that operating over a (relatively)slow network exposes us to latencies of shipping that much traffic.<br />I was wondering if the need to accessthat table can be delayed, so queries would join against it instead of prefetching it? Or perhaps cache it locallyon disk and fetch only higher OID values (I'm guessing here, possibly incorrectly, that rows are not updated but onlyadded) this would enable one full fetch and incremental updates since.<br /><br /><br />Thanks,<br /> Sefer.<br /><br/><br /><br /><hr />Hotmail: Powerful Free email with security by Microsoft. <a href="http://clk.atdmt.com/GBL/go/196390710/direct/01/"target="_new">Get it now.</a>
Le 15/01/2010 00:30, Tzvi R a écrit : > [...] > A quick overview of our database server: > * Four databases. > * Each database has about 20 schemas. > > > The largest database contains: > * select count(*) from pg_class where relkind = 'v' > 101 > * select count(*) from pg_class where relkind = 'r' > 11911 (about 500 tables in each schema, I know, it's a lot - but I'd bet it's not uncommon) > * About 10 sequences. > * About 150 functions. > > > select count(*) from pg_class >> 36444 > > > All these tables are large ones and have some toasted rows (you can see it in pg_type). > > > Those queries are rather fast, it's just that operating over a (relatively) slow network exposes us to latencies of shippingthat much traffic. > I was wondering if the need to access that table can be delayed, so queries would join against it instead of prefetchingit? Or perhaps cache it locally on disk and fetch only higher OID values (I'm guessing here, possibly incorrectly,that rows are not updated but only added) this would enable one full fetch and incremental updates since. > > That would need quite a lot of work. I know there are a lot of things to do to behave better with a database containing a lot of objects. Not sure we'll have time to address this for the next release. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com