diff options
| -rw-r--r-- | doc/FAQ | 61 | ||||
| -rw-r--r-- | doc/src/FAQ/FAQ.html | 64 |
2 files changed, 54 insertions, 71 deletions
@@ -1,7 +1,7 @@ Frequently Asked Questions (FAQ) for PostgreSQL - Last updated: Tue Feb 12 12:18:09 EST 2002 + Last updated: Thu Feb 14 12:14:47 EST 2002 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) @@ -48,13 +48,11 @@ Why? 3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors. Why? - 3.5) How do I prevent other hosts from accessing my PostgreSQL - database? - 3.6) Why can't I connect to my database from another machine? - 3.7) How do I tune the database engine for better performance? - 3.8) What debugging features are available? - 3.9) Why do I get "Sorry, too many clients" when trying to connect? - 3.10) What are the pg_sorttempNNN.NN files in my database directory? + 3.5) How do I control connections from other hosts? + 3.6) How do I tune the database engine for better performance? + 3.7) What debugging features are available? + 3.8) Why do I get "Sorry, too many clients" when trying to connect? + 3.9) What are the pg_sorttempNNN.NN files in my database directory? Operational Questions @@ -484,7 +482,7 @@ Administrator's Guide for more detailed information about shared memory and semaphores. - 3.5) How do I prevent other hosts from accessing my PostgreSQL database? + 3.5) How do I control connections from other hosts? By default, PostgreSQL only allows connections from the local machine using Unix domain sockets. Other machines will not be able to connect @@ -492,14 +490,7 @@ authentication by modifying the file $PGDATA/pg_hba.conf accordingly. This will allow TCP/IP connections. - 3.6) Why can't I connect to my database from another machine? - - The default configuration allows only Unix domain socket connections - from the local machine. To enable TCP/IP connections, make sure - postmaster has been started with the -i option, and add an appropriate - host entry to the file pgsql/data/pg_hba.conf. - - 3.7) How do I tune the database engine for better performance? + 3.6) How do I tune the database engine for better performance? Certainly, indexes can speed up queries. The EXPLAIN command allows you to see how PostgreSQL is interpreting your query, and which @@ -530,7 +521,7 @@ You can also use the CLUSTER command to group data in tables to match an index. See the CLUSTER manual page for more details. - 3.8) What debugging features are available? + 3.7) What debugging features are available? PostgreSQL has several features that report status information that can be valuable for debugging purposes. @@ -577,7 +568,7 @@ pgsql/data/base/dbname directory. The client profile file will be put in the client's current directory. - 3.9) Why do I get "Sorry, too many clients" when trying to connect? + 3.8) Why do I get "Sorry, too many clients" when trying to connect? You need to increase postmaster's limit on how many concurrent backend processes it can start. @@ -601,7 +592,7 @@ was 64, and changing it required a rebuild after altering the MaxBackendId constant in include/storage/sinvaladt.h. - 3.10) What are the pg_sorttempNNN.NN files in my database directory? + 3.9) What are the pg_sorttempNNN.NN files in my database directory? They are temporary files generated by the query executor. For example, if a sort needs to be done to satisfy an ORDER BY, and the sort @@ -671,29 +662,31 @@ 4.6) How much database disk space is required to store data from a typical text file? - A PostgreSQL database may need six-and-a-half times the disk space - required to store the data in a flat file. - - Consider a file of 300,000 lines with two integers on each line. The - flat file is 2.4 MB. The size of the PostgreSQL database file - containing this data can be estimated at 14 MB: + A PostgreSQL database may require up to five times the disk space to + store data from a text file. + + As an example, consider a file of 100,000 lines with an integer and + text description on each line. Suppose the text string avergages + twenty characters in length. The flat file would be 2.8 MB. The size + of the PostgreSQL database file containing this data can be estimated + as 6.6 MB: 36 bytes: each row header (approximate) - + 8 bytes: two int fields @ 4 bytes each + 26 bytes: two int fields @ 4 bytes each + 4 bytes: pointer on page to tuple ---------------------------------------- - 48 bytes per row + 66 bytes per row The data page size in PostgreSQL is 8192 bytes (8 KB), so: 8192 bytes per page - ------------------- = 171 rows per database page (rounded up) - 48 bytes per row + ------------------- = 124 rows per database page (rounded down) + 66 bytes per row - 300000 data rows - -------------------- = 1755 database pages - 171 rows per page + 100000 data rows + -------------------- = 807 database pages (rounded up) + 124 rows per page -1755 database pages * 8192 bytes per page = 14,376,960 bytes (14 MB) +807 database pages * 8192 bytes per page = 6,610,944 bytes (6.6 MB) Indexes do not require as much overhead, but do contain the data that is being indexed, so they can be large also. diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index 40ad62917a7..9ad2d5089c2 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -14,7 +14,7 @@ alink="#0000ff"> <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1> - <P>Last updated: Tue Feb 12 12:18:09 EST 2002</P> + <P>Last updated: Thu Feb 14 12:14:47 EST 2002</P> <P>Current maintainer: Bruce Momjian (<A href= "mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR> @@ -72,16 +72,13 @@ get <I>IpcMemoryCreate</I> errors. Why?<BR> <A href="#3.4">3.4</A>) When I try to start <I>postmaster</I>, I get <I>IpcSemaphoreCreate</I> errors. Why?<BR> - <A href="#3.5">3.5</A>) How do I prevent other hosts from - accessing my PostgreSQL database?<BR> - <A href="#3.6">3.6</A>) Why can't I connect to my database from - another machine?<BR> - <A href="#3.7">3.7</A>) How do I tune the database engine for + <A href="#3.5">3.5</A>) How do I control connections from other hosts?<BR> + <A href="#3.6">3.6</A>) How do I tune the database engine for better performance?<BR> - <A href="#3.8">3.8</A>) What debugging features are available?<BR> - <A href="#3.9">3.9</A>) Why do I get <I>"Sorry, too many + <A href="#3.7">3.7</A>) What debugging features are available?<BR> + <A href="#3.8">3.8</A>) Why do I get <I>"Sorry, too many clients"</I> when trying to connect?<BR> - <A href="#3.10">3.10</A>) What are the <I>pg_sorttempNNN.NN</I> + <A href="#3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I> files in my database directory?<BR> @@ -633,8 +630,8 @@ PostgreSQL Administrator's Guide for more detailed information about shared memory and semaphores.</P> - <H4><A name="3.5">3.5</A>) How do I prevent other hosts from - accessing my PostgreSQL database?</H4> + <H4><A name="3.5">3.5</A>) How do I control connections from other + hosts?</H4> <P>By default, PostgreSQL only allows connections from the local machine using Unix domain sockets. Other machines will not be able @@ -643,16 +640,7 @@ <I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP connections.</P> - <H4><A name="3.6">3.6</A>) Why can't I connect to my database from - another machine?</H4> - - <P>The default configuration allows only Unix domain socket - connections from the local machine. To enable TCP/IP connections, - make sure <I>postmaster</I> has been started with the <I>-i</I> - option, and add an appropriate host entry to the file - <I>pgsql/data/pg_hba.conf</I>.</P> - - <H4><A name="3.7">3.7</A>) How do I tune the database engine for + <H4><A name="3.6">3.6</A>) How do I tune the database engine for better performance?</H4> <P>Certainly, indexes can speed up queries. The @@ -689,7 +677,7 @@ data in tables to match an index. See the <SMALL>CLUSTER</SMALL> manual page for more details.</P> - <H4><A name="3.8">3.8</A>) What debugging features are + <H4><A name="3.7">3.7</A>) What debugging features are available?</H4> <P>PostgreSQL has several features that report status information @@ -745,7 +733,7 @@ in the <I>pgsql/data/base/dbname</I> directory. The client profile file will be put in the client's current directory.</P> - <H4><A name="3.9">3.9</A>) Why do I get <I>"Sorry, too many + <H4><A name="3.8">3.8</A>) Why do I get <I>"Sorry, too many clients"</I> when trying to connect?</H4> <P>You need to increase <I>postmaster</I>'s limit on how many @@ -775,7 +763,7 @@ the MaxBackendId constant in <I>include/storage/sinvaladt.h</I>.</P> - <H4><A name="3.10">3.10</A>) What are the <I>pg_sorttempNNN.NN</I> + <H4><A name="3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I> files in my database directory?</H4> <P>They are temporary files generated by the query executor. For @@ -862,30 +850,32 @@ <H4><A name="4.6">4.6</A>) How much database disk space is required to store data from a typical text file?</H4> - <P>A PostgreSQL database may need six-and-a-half times the disk - space required to store the data in a flat file.</P> + <P>A PostgreSQL database may require up to five times the disk space + to store data from a text file.</P> - <P>Consider a file of 300,000 lines with two integers on each line. - The flat file is 2.4 MB. The size of the PostgreSQL database file - containing this data can be estimated at 14 MB:</P> + <P>As an example, consider a file of 100,000 lines with an integer + and text description on each line. Suppose the text string avergages + twenty characters in length. The flat file would be 2.8 MB. The size + of the PostgreSQL database file containing this data can be + estimated as 6.6 MB:</P> <PRE> 36 bytes: each row header (approximate) - + 8 bytes: two int fields @ 4 bytes each + 26 bytes: two int fields @ 4 bytes each + 4 bytes: pointer on page to tuple ---------------------------------------- - 48 bytes per row + 66 bytes per row The data page size in PostgreSQL is 8192 bytes (8 KB), so: 8192 bytes per page - ------------------- = 171 rows per database page (rounded up) - 48 bytes per row + ------------------- = 124 rows per database page (rounded down) + 66 bytes per row - 300000 data rows - -------------------- = 1755 database pages - 171 rows per page + 100000 data rows + -------------------- = 807 database pages (rounded up) + 124 rows per page -1755 database pages * 8192 bytes per page = 14,376,960 bytes (14 MB) +807 database pages * 8192 bytes per page = 6,610,944 bytes (6.6 MB) </PRE> <P>Indexes do not require as much overhead, but do contain the data |