18.1 Tuning PostgreSQL
| PostgreSQL is designed to meet the needs of a variety of different applications and requests. Depending on what you are using the database for and on which hardware you want to run, the best settings for PostgreSQL might vary significantly. PostgreSQL offers a great deal of flexibility in the way it can be configured. You can set and modify many runtime parameters to define the behavior of the database. In addition, tuning increases the speed of your system. In this chapter you will be guided through the fundamentals of tuning PostgreSQL. You will see what you can do to speed up your database and how PostgreSQL works internally. When tuning databases, some important things have to be taken into consideration. In this chapter, a brief overview of all relevant topics will be provided. 18.1.1 Permanent Versus Temporary SettingsIn general there are two ways of setting PostgreSQL's runtime parameters. One way is to set various variables temporarily so that only the current session or an individual query is affected. Another way is to set parameters globally so that every connection established to the database can access the same settings. Changing the global settings of PostgreSQL will help you to influence the overall speed of your database. However, in individual, complex queries that take a long time to be executed, it can be helpful to use some temporary settings as well. In most applications, a comparatively small set of queries are responsible for most of the power needed on the machine. To get rid of or to reduce the problem, you should try to figure out the most time-consuming queries and optimize these with the help of temporary settings. Depending on what you want to optimize, the best mixture of settings might vary. 18.1.2 The OptimizerTo tune a database and an application efficiently, it is necessary to understand the basics of how PostgreSQL processes a query. We won't go too far into detail because this would be beyond the scope of this book. 18.1.2.1 FundamentalsThe first thing to do when executing a query is to parse the statement you want PostgreSQL to process. Parsing means that the query is split up into tokens and analyzed. The syntax is checked and in the case of success, a so-called parse tree is built. This tree is used to extract the various components of the query. Now that the query has been prepared, PostgreSQL tries to find the best way through the query. Finding the best way through a query means that PostgreSQL has to decide how to execute the query best, which kind of join to use best, and the database has to decide if it is useful to use an index or not. All decisions made by PostgreSQL are based on so-called system tables, which contain statistical information about the content of a table. With the help of this data, PostgreSQL tries to compute the minimum costs to execute the query. The most important thing is that if the data in pg_statistic (the system table containing the statistical information) is not up-to-date, PostgreSQL won't be able to find the best way through a query. Therefore it is recommended to run VACUUM ANALYZE from time to time. This will make sure that the statistics are updated. If the number of tables that have to be joined increases, the number of possible ways through the query will increase exponentially. This can be a problem because the time needed to evaluate all ways through the query will also increase, so it is fairly impossible to perform a join with dozens of tables involved. Because PostgreSQL is a highly developed database system, there is a way to get around the problem. 18.1.2.2 GEQOGEQO is the Genetic Query Optimizer. In complex queries, GEQO is used to reduce time needed for finding the best way through a query. This is necessary because the time needed for finding the best way grows exponentially. PostgreSQL's genetic algorithm (major parts of GEQO are based on D. Whitley's Genitor algorithm) is an heuristic optimization method, which means that a good solution is found by determined, randomized searching. The list of possible solutions for the optimization problem is considered as a population of individuals. The degree of adaptation of an individual to its environment is specified by its fitness. The idea of a genetic algorithm is to check whether a mutation of an individual achieves a higher level of fitness. This way it is possible to get better solutions than just random ones. Keep in mind that the result of a genetic algorithm is based on stochastic methods ("stochastic" means "built on a probability calculus"). Therefore the way through the query computed might not be the best one, but it is still better than a random one. Don't be confused by the fact that it is a random algorithm; the result of a query is always the same. Keep in mind that this is an essential feature of PostgreSQL because otherwise it would be absolutely impossible to compute a really complex query. For mathematical reasons there is no significantly better algorithm available. GEQO can be configured to your needs by editing the settings related to GEQO in postgresql.conf, but we strongly recommend sticking to the default settings unless you know extremely well what PostgreSQL is doing inside. In this book tuning GEQO won't be discussed because it takes a great deal of background information to achieve a reasonable result and in many cases it is simply not useful to change GEQO's settings. In queries that are not too complex, PostgreSQL checks possible ways through the query to find the best result and GEQO is not used. 18.1.3 Helping the OptimizerWhen executing a query, it can be useful to help the optimizer in order to achieve better solutions fast. You can help the optimizer by restricting the possible steps that can be performed by the optimizer. Because this can be done temporarily, it's easy to tune PostgreSQL efficiently. In this section you will see how things can be done. You can write a simple Perl script for generating some data. You can also use PHP to write this application, but in case of slow machines or a large amount of data, it is better to use Perl for that purpose. 18.1.3.1 Generating Sample DataThe next piece of code will generate data for three tables: #!/usr/bin/perl open(ONE, "| psql phpbook") or die "cannot open pipe\n"; open(TWO, "| psql phpbook") or die "cannot open pipe\n"; open(THREE, "| psql phpbook") or die "cannot open pipe\n"; print ONE "CREATE TABLE one (id int4, gerade bool, modvalue int4);\n"; print TWO "CREATE TABLE two (id int4, laenge numeric(9,4));\n"; print THREE "CREATE TABLE three (id int4, modvalue int4, randval numeric(20,10));\n"; print ONE "COPY one FROM stdin;\n"; print TWO "COPY two FROM stdin;\n"; print THREE "COPY three FROM stdin;\n"; for ($i = 1; $i < 100000000; $i++) { $even = ($i + 1) % 2; $modval = $i % 23; print ONE "$i $even $modval\n"; if ($i % 50000 eq 0) { print "processing record: $i\n"; $len = length($i); print TWO "$i $len.0\n"; } $modval = $i % 123; if ($modval eq 0) { $randval = rand($i); printf THREE ("%s %s %6.3f\n", $i, $modval, $randval); } } print ONE '\.'; print TWO '\.'; print THREE '\.'; close(ONE); close(TWO); close(THREE); At the beginning of the script, three pipes to psql are opened. Every connection will be used to receive data for one table. In the next step, three tables are created and a COPY command is started for every table. Then a loop is executed 100 million times. Inside the loop $even is assigned to the result of $i%2. Every 50,000th record is added to table two. This table contains the id of the record as well as the length of the number in the first column. For the table called three, every 123rd record is added to it. The table contains an id, $even, and a random value. After sending the data to the server, COPY is finished and the pipes are closed. After executing the script, the database contains many records we will use for showing some basics related to tuning. 18.1.3.2 Defining IndicesAfter you have executed the script, the database contains three tables containing many records. To find out how many records can be found in table one, you can run a query: [hs@duron data]$ time psql -c "SELECT COUNT(*) FROM one" phpbook count ---------- 99999999 (1 row) real 13m54.460s user 0m0.010s sys 0m0.000s The query has been executed using an AMD 750 CPU with 384MB of RAM and a ST320423A (20GB) hard disk. The query takes almost 14 minutes to be executed because the entire table has to be read. For the two other tables, the time needed to execute the query is significantly less: [hs@duron data]$ time psql -c "SELECT COUNT(*) FROM two" phpbook count ------- 1999 (1 row) real 0m0.645s user 0m0.030s sys 0m0.000s [hs@duron data]$ time psql -c "SELECT COUNT(*) FROM three" phpbook count -------- 813008 (1 row) real 0m35.755s user 0m0.000s sys 0m0.020s The reason that the queries are much faster is that the amount of data that has to be read is much smaller. In aggregation functions such as COUNT, MAX, MIN, and AVG, PostgreSQL always has to perform sequential scans because no indices can be used by the database so far. Let's see what happens if a special value is retrieved: [hs@duron data]$ time psql -c "SELECT * FROM one WHERE id=30" phpbook id | gerade | modvalue ----+--------+---------- 30 | t | 7 (1 row) real 4m48.040s user 0m0.030s sys 0m0.000s The query takes more than four minutes, which is far too much. Imagine a production environment where dozens of concurrent users want to access the database it would be a serious problem if every query took more than four minutes. Therefore it is necessary to find the bottlenecks of the database. The EXPLAIN command is essential for that purpose: phpbook=# \h EXPLAIN Command: EXPLAIN Description: Shows statement execution plan Syntax: EXPLAIN [ VERBOSE ] query EXPLAIN can be used to display the execution plan of a query. The next listing shows the execution plan of the SQL statement we have just executed: [hs@duron data]$ time psql -c "EXPLAIN SELECT * FROM one WHERE id=30" phpbook NOTICE: QUERY PLAN: Seq Scan on one (cost=0.00..22.50 rows=10 width=9) EXPLAIN real 0m0.620s user 0m0.020s sys 0m0.000s As you can see, a so-called sequential scan is performed, which means that the entire table has to be read to compute the result of the query. This is far too slow, so you should consider using an index as shown in the next listing: [hs@duron data]$ time psql -c "CREATE INDEX idx_one_id ON one(id)" phpbook CREATE real 32m17.762s user 0m0.010s sys 0m0.000s Creating the index takes about half an hour. This is fast because building up an insert forces the database to sort the data in the table, which is a lot of work. Internally an index is a tree based on a sorted list pointing to the data in the table. Sorting about 100 million records takes a long time. Internally PostgreSQL uses the memory defined as sort memory for building up the index. In other words, in PostgreSQL's default setting 512 kilobytes of sort memory per sort process are used for sorting. If more memory is needed, the database creates temporary files on disk to perform the sort. Writing data to disk is much slower than sorting records in memory, so we recommend redefining the size of the sort buffer. Before doing this, you can take a look at how the size of the default sort buffer can be retrieved: phpbook=# SHOW sort_mem; NOTICE: sort_mem is 512 SHOW VARIABLE As you can see, 512 kilobytes of memory are used as sort buffer. If you are creating a huge index like the one you saw before, you can redefine the size of the memory temporarily as shown in the next example (don't forget to drop the old index first): [hs@duron data]$ time psql -c "CREATE INDEX idx_one_id ON one(id)" phpbook CREATE real 26m37.654s user 0m0.010s sys 0m0.000s As you can see, modifying the sort buffer can help you to speed up the generation of the index. Now that the index has been created, you can take a look at the execution plan of the query so that you can see if the index is used: [hs@duron data]$ psql -c "EXPLAIN SELECT * FROM one WHERE id=30" phpbook NOTICE: QUERY PLAN: Seq Scan on one (cost=0.00..1838235.99 rows=1000000 width=9) EXPLAIN As you can see, no index is used. To understand the problem, it is necessary to take a closer look at how the optimizer works. 18.1.3.3 Optimizing Queries Using VacuumThe optimizer's results are based on statistical information that is stored in a system table called pg_statistic. If the statistical data in pg_statistic is bad, the execution plans generated by PostgreSQL are bad as well. This will lead to bad performance and long execution times. To solve these problems, PostgreSQL provides a command called VACUUM. The next listing shows the syntax overview of VACUUM: phpbook=# \h VACUUM Command: VACUUM Description: garbage-collect and optionally analyze a database Syntax: VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] With the introduction of PostgreSQL 7.2, the parameters accepted by VACUUM have grown. In particular, VACUUM FULL is an important command that tries to shrink PostgreSQL's internal consumption of storage as much as possible. With the help of VACUUM ANALYZE, PostgreSQL not only shrinks the files on disk, but it also computes the statistical information the optimizer is relying on. This leads to better execution plans and more efficient storage. If you don't want to run VACUUM interactively, you can run vacuumdb from the command line. Here is an overview of the syntax: [hs@athlon hs]$ vacuumdb --help vacuumdb cleans and analyzes a PostgreSQL database. Usage: vacuumdb [options] [dbname] Options: -h, --host=HOSTNAME Database server host -p, --port=PORT Database server port -U, --username=USERNAME Username to connect as -W, --password Prompt for password -d, --dbname=DBNAME Database to vacuum -a, --all Vacuum all databases -t, --table='TABLE[(columns)]' Vacuum specific table only -f, --full Do full vacuuming -v, --verbose Write a lot of output -z, --analyze Update optimizer hints -e, --echo Show the command being sent to the backend -q, --quiet Don't write any output Read the description of the SQL command VACUUM for details. Report bugs to <pgsql-bugs@postgresql.org>. As you can see, many parameters can be defined. The most important thing is that it is possible to vacuum remote databases. Especially when working with many server machines, this can be useful because all machines can be maintained using just one machine. Since PostgreSQL 7.2, a command called ANALYZE is provided. It can be used to gather statistical information for PostgreSQL's optimizer. Let's take a look at the syntax overview: phpbook=# \h ANALYZE Command: ANALYZE Description: collect statistics about a database Syntax: ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ] Just define the table you want to analyze and click the button. PostgreSQL will build up the system table for you. Now run vacuumdb to rebuild pg_statistic: [hs@duron data]$ time vacuumdb -a -z Vacuuming template1 VACUUM Vacuuming phpbook VACUUM real 29m32.559s user 0m0.040s sys 0m0.030s After executing the command, you can run a SELECT statement again to see how fast the query can be executed now: [hs@duron data]$ time psql -c "SELECT * FROM one WHERE id=5000000" phpbook id | gerade | modvalue ---------+--------+---------- 5000000 | t | 7 (1 row) real 0m0.104s user 0m0.010s sys 0m0.010s It takes about 0.1 seconds to retrieve one value out of 100 million records, which is fast. This impressive example shows how much performance can be gained by such an operation. Execute the query a second time: [hs@duron data]$ time psql -c "SELECT * FROM one WHERE id=5000000" phpbook id | gerade | modvalue ---------+--------+---------- 5000000 | t | 7 (1 row) real 0m0.036s user 0m0.020s sys 0m0.010s As you can see, the query has been executed even faster because of caching effects. 18.1.3.4 Optimizing Queries Using CachingCaching means that PostgreSQL keeps some data in memory and therefore it is not necessary to read every piece of data from the hard disk every time a query is executed. To retrieve the current size of the cache, you can use the SHOW command as shown in the next listing: phpbook=# SHOW effective_cache_size; NOTICE: effective_cache_size is 1000 SHOW VARIABLE The default cache size is 1000 blocks, which is 8 megabytes. Let's see what effects caching can have on the performance of your system. You can write a simple shell script that measures the data needed to perform various queries: #!/bin/sh echo "1: 8mb cache" time psql -c "SELECT COUNT(*) FROM one WHERE id<200000; SELECT COUNT(*) FROM one WHERE id<200000; " phpbook > /dev/null echo "1: 160mb cache" time psql -c "SET effective_cache_size TO 20000; SELECT COUNT(*) FROM one WHERE id<200000; SELECT COUNT(*) FROM one WHERE id<200000; " phpbook > /dev/null echo "2: 160mb cache" time psql -c "SELECT COUNT(*) FROM one WHERE id<100000000; SELECT COUNT(*) FROM one WHERE id<100000000; " phpbook > /dev/null echo "2: 160mb cache" time psql -c "SET effective_cache_size TO 20000; SELECT COUNT(*) FROM one WHERE id<100000000; SELECT COUNT(*) FROM one WHERE id<100000000; " phpbook > /dev/null Keep in mind that these results have been computed on my machine. The absolute figures are not that important, but it is necessary to see the relationship between the various results. This will help you to understand PostgreSQL's way of caching. Let's take a look at the results: 1: 8mb cache real 0m3.464s user 0m0.020s sys 0m0.000s 1: 160mb cache real 0m1.674s user 0m0.020s sys 0m0.010s 2: 160mb cache real 14m13.022s user 0m0.010s sys 0m0.000s 2: 160mb cache real 14m40.221s user 0m0.010s sys 0m0.000s As you can see, there is a significant difference between the first two results. The first example, which uses the standard settings, is significantly slower due to I/O. In the second example, the data needed for the executing the first query is kept in memory, so the second query can be executed much faster. This behavior seems obvious. In the third example, huge amounts of data are processed using the default settings. As you can see, the full table scan takes around 14 minutes. In the fourth example, PostgreSQL uses a lot of cache, but the queries are even slower. The reason for that is simple: Caching causes overhead. Internally PostgreSQL keeps the most recently used blocks in memory. If the cache is full and data is added to it, PostgreSQL will remove the oldest block stored in the cache, and this is exactly what happens in the case of complex queries. Caching won't help you to speed up your queries, but it can cause overhead for managing the cache. Example number four is a worst-case scenario, but it is necessary to understand what happens internally. 18.1.3.5 Joins and Helping the OptimizerAfter defining an index and having a closer look at caching, it is time to see how joins are performed. The next example shows how the number of rows two tables have in common can be computed: [hs@duron data]$ time psql -c "SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook count ------- 1999 (1 row) real 0m44.202s user 0m0.020s sys 0m0.000s As you can see, the query takes around 44 seconds to complete. But 44 seconds are quite a bit of time, so it is necessary to take a look at the execution plan of the query: [hs@duron data]$ time psql -c "EXPLAIN SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook NOTICE: QUERY PLAN: Aggregate (cost=10078.16..10078.16 rows=1 width=8) -> Nested Loop (cost=0.00..10073.17 rows=1999 width=8) -> Seq Scan on two (cost=0.00..32.99 rows=1999 width=4) -> Index Scan using idx_one_id on one (cost=0.00..5.01 rows=1 width=4) EXPLAIN real 0m0.091s user 0m0.010s sys 0m0.010s First an index scan and a sequential scan are performed. This is no problem because the second table is comparatively small. After that, a nested loop has to be done. This means that for every record in the first table, a counterpart in the second table has to be found. This takes a long time because if the number of records in the two tables doubles, it will take up to four times longer to execute the query. In the case of 10 times more data, it will take up to 100 times longer to compute the result. Therefore huge amounts of data cannot be processed this way. To join two tables, an index should be defined: [hs@duron data]$ psql -c "CREATE INDEX idx_two_id ON two(id)" phpbook CREATE After defining the index, you can take a look at the execution plan of the query again: [hs@duron data]$ psql -c "EXPLAIN SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook NOTICE: QUERY PLAN: Aggregate (cost=10078.16..10078.16 rows=1 width=8) -> Nested Loop (cost=0.00..10073.17 rows=1999 width=8) -> Seq Scan on two (cost=0.00..32.99 rows=1999 width=4) -> Index Scan using idx_one_id on one (cost=0.00..5.01 rows=1 width=4) EXPLAIN Nothing has changed. The database still performs a sequential scan. To get rid of the problem, you can either run VACUUM or tell the optimizer to use an index scan instead of a sequential scan. This can be done by using the SET command: [hs@duron data]$ time psql -c "SET enable_seqscan TO off; SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook count ------- 1999 (1 row) real 0m0.441s user 0m0.010s sys 0m0.000s This time the result is computed much faster. The reason for that can easily be found in the execution plan of the query: [hs@duron data]$ psql -c "SET enable_seqscan TO off; EXPLAIN SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook NOTICE: QUERY PLAN: Aggregate (cost=10140.18..10140.18 rows=1 width=8) -> Nested Loop (cost=0.00..10135.18 rows=1999 width=8) -> Index Scan using idx_two_id on two (cost=0.00..95.00 rows=1999 width=4) -> Index Scan using idx_one_id on one (cost=0.00..5.01 rows=1 width=4) EXPLAIN This time two sequential scans are performed. After that a nested loop is executed to find the appropriate matches. Finally the COUNT operation is executed. To build up the statistics the optimizer is using, you can run VACUUM for a specific table in a specific database. This can be done by using the -t flag in combination with -d: [hs@duron data]$ vacuumdb -z -t two -d phpbook VACUUM After that it is not necessary any more to turn sequential scans off manually. In many cases, setting parameters temporarily can be useful for tuning a query. However, in some cases it can be fatal to influence the optimizer. Let's take a look at the execution time of the next example: [hs@duron data]$ time psql -c "SET enable_nestloop TO off; SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook count ------- 1999 (1 row) real 10m38.210s user 0m0.020s sys 0m0.000s The query takes more than 10 minutes to complete. This is far too long, so it is worth taking a look at the execution plan of the query: [hs@duron data]$ time psql -c "SET enable_nestloop TO off; EXPLAIN SELECT COUNT(one.id) FROM one, two WHERE one.id=two.id" phpbook NOTICE: QUERY PLAN: Aggregate (cost=5588303.92..5588303.92 rows=1 width=8) -> Hash Join (cost=37.99..5588298.92 rows=1999 width=8) -> Seq Scan on one (cost=0.00..1588235.99 rows=99999999 width=4) -> Hash (cost=32.99..32.99 rows=1999 width=4) -> Seq Scan on two (cost=0.00..32.99 rows=1999 width=4) EXPLAIN real 0m0.039s user 0m0.020s sys 0m0.000s Two sequential scans are performed. In addition, hashes are built. This way of executing the query is far slower. 18.1.4 Performance MonitoringTo tune a database, it is necessary to know what the database is doing and how things work. One important parameter when executing a SQL statement is the time it needs to be executed by the database. The target of every tuning process is to find out which queries take too long. This can easily be done with the help of a simple function. Let's take a look at an example: <?php $dbh = pg_connect("user=postgres dbname=phpbook"); if (!$dbh) { echo "Cannot connect<br>\n"; } $stat = myexec($dbh, "SELECT * FROM pg_class ORDER BY relname"); $stat = myexec($dbh, "SELECT 1+1"); $data = pg_fetch_row($stat, 0); echo "1+1 makes ".$data[0]."<br>\n"; function myexec($dbh, $code) { $start = split(" ", microtime()); $stime = $start[0] + $start[1]; $stat = pg_exec($dbh, $code); $endtime = split(" ", microtime()); $etime = $endtime[0] + $endtime[1]; echo $etime - $stime. " seconds: $code<br>\n"; return $stat; } ?> The most important part of the example is the myexec function, which can be found at the end of the script. It measures the time needed for executing a query and prints it onscreen. The information can also be sent to a logfile, which is used solely for tuning purposes. To keep the example slim, we have decided to display the information onscreen. Let's execute the script and see what comes out: 0.019946932792664 seconds: SELECT * FROM pg_class ORDER BY relname 0.0053499937057495 seconds: SELECT 1+1 1+1 makes 2 As you can see, three lines have been returned. The first two lines contain debugging information, and the third line contains the result of the second SQL statement. As you can see, both queries are extremely fast. Another thing that can be important is to find out how many backend processes are running on the local machine. You can write a shell script to find that out. Let's take a look: [hs@athlon data]$ ps ax | egrep -e ''postgres:'' 25032 ? S 0:00 postgres: stats buffer process 25034 ? S 0:00 postgres: stats collector process 4289 ? S 0:00 postgres: postgres phpbook [local] idle 4295 ? S 0:00 postgres: postgres phpbook [local] idle 4311 ? S 0:00 postgres: postgres phpbook [local] idle 4371 ? S 0:00 postgres: postgres phpbook [local] idle 4372 ? S 0:00 postgres: postgres phpbook [local] idle 15489 pts/3 S 0:00 postgres: stats buffer process 15490 pts/3 S 0:00 postgres: stats collector process 15493 pts/3 S 0:00 postgres: hs phpbook 127.0.0.1 idle 15497 pts/4 S 0:00 egrep -e postgres: ps ax returns a list of all processes on your Linux box. With the help of egrep, you can extract all processes that run on the local machine. All connections are returned. If you want to retrieve all connections that have been established via Unix sockets, you can use a different regular expression: [hs@athlon data]$ ps ax | egrep -e ''postgres:.*local'' 4289 ? S 0:00 postgres: postgres phpbook [local] idle 4295 ? S 0:00 postgres: postgres phpbook [local] idle 4311 ? S 0:00 postgres: postgres phpbook [local] idle 4371 ? S 0:00 postgres: postgres phpbook [local] idle 4372 ? S 0:00 postgres: postgres phpbook [local] idle 15445 pts/4 S 0:00 egrep -e postgres:.*local As you can see, a list of processes has been returned. In addition to backend processes, you can see that egrep itself is also listed. The target of the next example is to write a SQL function that returns the number of backend processes running on your local machine and interacting with PostgreSQL via Unix sockets. You can write a simple PHP application that makes a system call and returns the data. This is not that comfortable but can be done easily. A more elegant way is to use PL/Sh by Peter Eisentraut. PL/Sh provides access to a Unix shell from inside PostgreSQL. If you are working in a multiuser environment, you should not use it in all databases for security reasons. However, if you need it just for monitoring, you can create a separate database and PL/Sh support to it. Check out the following Web site to download the source code: http://www.ca.postgresql.org/~petere/download/ To install the sources, use tar xvfz and the name of the package. Then run configure to build all makefiles. Just define the location where configure can find your PostgreSQL source code. Then use --prefix to tell the script where your current PostgreSQL binaries can be found. Take a look at how to do this: CPPFLAGS=-I/usr/src/postgres/postgresql-7.2/src/include/./configure --prefix=/usr/local/postgresql/ If no errors have occurred, you can run make and make install to compile and install the package. In the next step you can add support for PL/Sh to your database. In this example it is done for the database called phpbook (assume that PostgreSQL has been installed in /usr/local/postgresql): [hs@athlon hs]$ psql -d phpbook -f /usr/local/postgresql/share/pgplsh/createlang_pgplsh.sql CREATE CREATE If no error occurred, PL/Sh has been added to the database successfully. In the next step you can write two functions. The first one returns the result of the ps command you saw before. The second one returns the number of connections being established to the local machine via Unix sockets (not via TCP/IP): CREATE OR REPLACE FUNCTION "backends_list"() RETURNS "text" AS ' #!/bin/sh ps ax | egrep -e ''postgres:.*local'' ' LANGUAGE 'plsh'; CREATE OR REPLACE FUNCTION "backends_number"() RETURNS "text" AS ' #!/bin/sh expr $(ps ax | egrep -e ''postgres:.*local'' | wc -l) - 1 ' LANGUAGE 'plsh'; In the first function, egrep is used to find all processes related to PostgreSQL. In the second function, the number of processes returned is used and reduced by one. This is done to subtract the back end performing the calculation. Let's execute the function and see what is happening: phpbook=# SELECT backends_list(); backends_list ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------ 4289 ? S 0:00 postgres: postgres phpbook [local] idle 4295 ? S 0:00 postgres: postgres phpbook [local] idle 4311 ? S 0:00 postgres: postgres phpbook [local] idle 4371 ? S 0:00 postgres: postgres phpbook [local] idle 4372 ? S 0:00 postgres: postgres phpbook [local] idle 15408 pts/3 S 0:00 postgres: hs phpbook [local] SELECT 15419 pts/3 S 0:00 egrep -e postgres:.*local (1 row) A list of processes is returned. In this listing the egrep process is returned as well but it can easily be removed by writing a slightly more complex shell script. To compute the number of back ends, you can call backends_number: phpbook=# SELECT backends_number(); backends_number ----------------- 6 (1 row) In this example, six back end processes are running. An additional command that can be important for you is called uptime and can be used to find out how long the system has been running and how much load your CPU has to face. Let's take a look at an example: [hs@duron hs]$ uptime 10:55pm up 124 days, 12:10, 5 users, load average: 1.52, 1.56, 1.54 In this example the machine has been up and running for 124 days since the last reboot. Currently five users have logged into the machine. The most important information for database tuners is the average load of the CPU: The first column contains the load during the last minute, the second column tells you the average load during the last 5 minutes, and the last column contains information about the last 15 minutes. Keep in mind that the CPU load says nothing about the real load on your system. In addition, not all operating systems provide a command such as uptime. 18.1.5 Optimizations at Compile TimeA variety of settings can be changed at compile time. Depending on the compiler you are using, some basic optimizations can be done by adding or changing the default compiler flags. In this section you will get a brief insight into what you can do with the help of GCC. One flag that can be used is the -mcpu flag. The following parameters are accepted by GCC 2.96: i386, i486, i586, i686, pentium, pentiumpro, k6, and athlon. Although picking a specific CPU type will schedule things appropriately for that particular chip, the compiler will not generate any code that does not run on the i386 without the -march= option being used. i586 is equivalent to pentium and i686 is equivalent to pentiumpro. -march will help you to define the architecture. The choices for CPU type are the same as for the -mcpu flag. Note that specifying -march implies -mcpu. The following flags are accepted: -m386, -m486, -mpentium, -mpentiumpro. In addition to telling GCC which CPU the binaries should be optimized for, it is possible to use various other optimization flags as shown in Table 18.1.
In this section you will take a closer look at the -0 flags. When you use -0 flags, compiling the binaries will take slightly more time than when not using the optimizer. In contrast, the binaries the compiler generates will be more efficient and can be executed faster. Depending on which level of optimization you use, the compiler produces more or less efficient code. By default -02 is used. If you want to use an even higher level of optimization, you can use the -03 flag, which does everything -02 does along with turning -finline-functions on. This means that the compiler is allowed to reduce the number of function calls by inlining various components of the code. The problem with this algorithm is that binaries created by using -03 cannot easily be debugged because small functions might be omitted by inlining them. However, if you want to get the maximum performance out of your compiler, you can try to use -03. In Chapter 6 you can see how to set compiler settings. |
EAN: N/A
Pages: 201