[PERFORM] slow pg_connect()
Hi,
I'm uning postgres 8.1 at P4 2.8GHz with 2GB RAM.
(web server + database on the same server)
Please, how long takes your connectiong to postgres?
$starttimer=time()+microtime();
$dbconn = pg_connect("host=localhost port=5432 dbname=xxx user=xxx
password=xxx")
or die("Couldn't Connect".pg_last_error());
$stoptimer = time()+microtime();
echo "Generated in ".round($stoptimer-$starttimer,4)." s";
It takes more then 0.05s :(
Only this function reduce server speed max to 20request per second.
Than you for any Help!
Best regards.
-
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Turn correlated in subquery into join
Look like the mysql people found a subquery that postgresql doesn't handle as good as possible: http://s.petrunia.net/blog/ Is there some deeper issue here that I fail to see or is it simply that it hasn't been implemented but is fairly straigt forward? In the link above they do state that it isn't a very common case anyway. /Dennis - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
[EMAIL PROTECTED] wrote:
It takes more then 0.05s :(
Only this function reduce server speed max to 20request per second.
If you need that sort of frequent database access, you might want to
look into:
- Doing more work in each connection and reducing the number of
connections required;
- Using multiple connections in parallel;
- Pooling connections so you don't need to create a new one for every job;
- Using a more efficient database connector and/or language;
- Dispatching requests to a persistent database access provider that's
always connected
However, your connections are indeed taking a long time. I wrote a
trivial test using psycopg for Python and found that the following script:
#!/usr/bin/env python
import psycopg
conn = pyscopg.connect("dbname=testdb")
generally took 0.035 seconds (350ms) to run on my workstation -
including OS process creation, Python interpreter startup, database
interface loading, connection, disconnection, and process termination.
A quick timing test shows that the connection/disconnection can be
performed 100 times in 1.2 seconds:
import psycopg
import timeit
print timeit.Timer('conn = psycopg.connect("dbname=craig")', 'import
psycopg').timeit(number=100);
... and this is still with an interpreted language. I wouldn't be too
surprised if much better again could be achieved with the C/C++ APIs,
though I don't currently feel the desire to write a test for that.
--
Craig Ringer
-
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
Craig Ringer wrote: [EMAIL PROTECTED] wrote: It takes more then 0.05s :( Only this function reduce server speed max to 20request per second. If you need that sort of frequent database access, you might want to look into: - Doing more work in each connection and reducing the number of connections required; - Using multiple connections in parallel; - Pooling connections so you don't need to create a new one for every job; - Using a more efficient database connector and/or language; - Dispatching requests to a persistent database access provider that's always connected Oh, I missed: Use a UNIX domain socket rather than a TCP/IP local socket. Database interfaces that support UNIX sockets (like psycopg) will normally do this if you omit the host argument entirely. -- Craig Ringer - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
> > It takes more then 0.05s :( > > Only this function reduce server speed max to 20request per second. First, benchmarking using only PHP is not very accurate, you're probably also measuring some work that PHP needs to do just to get started in the first place. Second, this 20r/s is not requests/sec but connections per second per PHP script. One pageview in PHP needs one connection, so it will delay the pageview by 0.05 seconds. If you need raw speed, you can use pg_pconnect(), but be VERY carefull because that will keep one databaseconnection open for every database for every webserverprocess. If you have 10 databasedriven websites running on the same webserver and that server is configured to run 100 processes at the same time, you will get 10x100=1000 open connections, which eats more RAM than you have. - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] increasing shared buffer slow downs query performance.
Hi friends, I am using postgresql 8.1, I have shared_buffers = 5, now i execute the query, it takes 18 seconds to do sequential scan, when i reduced to 5000, it takes one 10 seconds, Why. Can anyone explain what is the reason, ( any other configuration is needed in postgresql.conf) -- With Best Regards, Petchimuthulingam S
Re: [PERFORM] increasing shared buffer slow downs query performance.
petchimuthu lingam <[EMAIL PROTECTED]> schrieb: > Hi friends, > > I am using postgresql 8.1, I have shared_buffers = 5, now i execute the > query, it takes 18 seconds to do sequential scan, when i reduced to 5000, it > takes one 10 seconds, Why. Wild guess: the second time the data are in the filesystem cache. > > Can anyone explain what is the reason, ( any other configuration is needed in > postgresql.conf) Show us the EXPLAIN ANALYSE - Output. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
[EMAIL PROTECTED] wrote: Hi, I'm uning postgres 8.1 at P4 2.8GHz with 2GB RAM. (web server + database on the same server) Please, how long takes your connectiong to postgres? It takes more then 0.05s :( Only this function reduce server speed max to 20request per second. I tried running the script a few times, and got substantially lower start up times than you are getting. I'm using 8.1.11 on Debian on a 2x Xeon CPU 2.40GHz with 3GB memory, so I don't think that would account for the difference. Generated in 0.0046 s Generated in 0.0036 s Generated in 0.0038 s Generated in 0.0037 s Generated in 0.0038 s Generated in 0.0037 s Generated in 0.0047 s Generated in 0.0052 s Generated in 0.005 s -- Tommy Gildseth - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
Hi,
[EMAIL PROTECTED] schrieb:
Please, how long takes your connectiong to postgres?
$starttimer=time()+microtime();
$dbconn = pg_connect("host=localhost port=5432 dbname=xxx user=xxx password=xxx")
or die("Couldn't Connect".pg_last_error());
$stoptimer = time()+microtime();
echo "Generated in ".round($stoptimer-$starttimer,4)." s";
It takes more then 0.05s :(
Only this function reduce server speed max to 20request per second.
Two hints:
* Read about configuring and using persistent database connections
(http://www.php.net/manual/en/function.pg-pconnect.php) with PHP
* Use a connection pooler such as pgpool-II
(http://pgpool.projects.postgresql.org/)
Using both techniques together should boost your performance.
Ciao,
Thomas
-
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] waiting for harddisk
i am using postgresql 8.1.8, Following configurations: shared_buffers = 5000 work_mem = 65536 maintenance_work_mem = 65536 effective_cache_size = 16000 random_page_cost = 0.1 The cpu is waiting percentage goes upto 50%, and query result comes later, i am using normal select query ( select * from table_name ). table has more then 6 million records. -- With Best Regards, Petchimuthulingam S
Re: [PERFORM] waiting for harddisk
i am using postgresql 8.1.8, Following configurations: shared_buffers = 5000 work_mem = 65536 maintenance_work_mem = 65536 effective_cache_size = 16000 random_page_cost = 0.1 The cpu is waiting percentage goes upto 50%, and query result comes later, i am using normal select query ( select * from table_name ). table has more then 6 million records. When you mean SELECT *, are you selecting the WHOLE 6 million records ? Without WHERE ? Or just a few rows ? Please post EXPLAIN ANALYZE of your query. - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Turn correlated in subquery into join
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > Look like the mysql people found a subquery that postgresql doesn't > handle as good as possible: >http://s.petrunia.net/blog/ > Is there some deeper issue here that I fail to see or is it simply that > it hasn't been implemented but is fairly straigt forward? I don't think it's straightforward: you'd need to do some careful analysis to prove under what conditions such a transformation would be safe. If the answer is "always, it doesn't matter what the correlation condition is" then the actual implementation would probably not be tremendously difficult. If there are restrictions then checking whether the restrictions hold could be interesting ... regards, tom lane - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Planning hot/live backups?
The owners of the animal hospital where I work at want to consider live/hot backups through out the day so we're less likely to lose a whole day of transaction. We use Postgresql 8.0.15. We do 3AM backups, using pg_dumpall, to a file when there is very little activity. The hospital enjoys the overall performance of the veterinary application running on Postgresql. I know doing a mid-day backup when up to 60 computers (consistently 35-40) are access client/patient information, it will cause some frustration. I understand there needs to be balance of performance and backup of current records. While I know that not all situations are the same, I am hoping there is a performance latency that others have experienced when doing backups during the day and/or planning for cluster (or other types of redundancy). My animal hospital operates 24x7 and is in the south part of the San Francisco Bay area. Outside of sharing your experiences/input with me, I would not mind if you/your company do this type of consulting offline. Thank you. Steve - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
I back up around 10 Gig of data every half hour using pg_dump. I don't backup the entire database at once. Instead I backup at the schema namespace level. But I do all of them every half hour. It takes four minutes. That includes the time to copy the files to the backup server. I do each schema namespace backup consecutively. I also run vacuum full analyze once a day. My system is up 24/7 as well. I don't backup in the middle of the night. There is so little back. But I could. I am able to have more backups by not doing it when there are only a handful of transactions. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Poe Sent: Monday, March 24, 2008 3:23 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Planning hot/live backups? The owners of the animal hospital where I work at want to consider live/hot backups through out the day so we're less likely to lose a whole day of transaction. We use Postgresql 8.0.15. We do 3AM backups, using pg_dumpall, to a file when there is very little activity. The hospital enjoys the overall performance of the veterinary application running on Postgresql. I know doing a mid-day backup when up to 60 computers (consistently 35-40) are access client/patient information, it will cause some frustration. I understand there needs to be balance of performance and backup of current records. While I know that not all situations are the same, I am hoping there is a performance latency that others have experienced when doing backups during the day and/or planning for cluster (or other types of redundancy). My animal hospital operates 24x7 and is in the south part of the San Francisco Bay area. Outside of sharing your experiences/input with me, I would not mind if you/your company do this type of consulting offline. Thank you. Steve - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Steve Poe wrote: The owners of the animal hospital where I work at want to consider live/hot backups through out the day so we're less likely to lose a whole day of transaction. We use Postgresql 8.0.15. We do 3AM backups, using pg_dumpall, to a file when there is very little activity. You probably want to look into PITR, you can have a constant ongoing backup of your data and never lose more than a few minutes of data. The overhead isn't all the big especially if you are shipping the log files to a separate server. - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Matthew T. O'Connor wrote: Steve Poe wrote: The owners of the animal hospital where I work at want to consider live/hot backups through out the day so we're less likely to lose a whole day of transaction. We use Postgresql 8.0.15. We do 3AM backups, using pg_dumpall, to a file when there is very little activity. You probably want to look into PITR, you can have a constant ongoing backup of your data and never lose more than a few minutes of data. The overhead isn't all the big especially if you are shipping the log files to a separate server. I'll second that. PITR is IMHO the way to go, and I believe you'll be pleasantly surprised how easy it is to do. As always, test your backup strategy by restoring. Even better, make a point of periodically testing a restore of production backups to a non-production system. Paul - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > Steve Poe wrote: >> The owners of the animal hospital where I work at want to consider live/hot >> backups through out the day so we're less likely to lose a whole >> day of transaction. We use Postgresql 8.0.15. We do 3AM >> backups, using pg_dumpall, to a file when there is very little activity. > You probably want to look into PITR, you can have a constant ongoing > backup of your data and never lose more than a few minutes of data. The > overhead isn't all the big especially if you are shipping the log files > to a separate server. But note that you really need to update to a newer major release before depending on PITR. While 8.0 nominally has support for it, it's taken us several releases to really get the operational gotchas sorted out. regards, tom lane - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Steve Poe wrote: At this point, I am just moving the pg_dumpall file to another server. Pardon my question: how would you 'ship the log files'? [ You should cc the mailing list so that everyone can benefit from the conversation. ] RTM: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Tom, So, are you saying we need to get to at least 8.1.x before considering PITR for a production environment? Unfortunately, the vendor/supplier of our veterinary application does not support higher versions. We would be proceeding "at our own risk". Is there anything else we can do we 8.0.15 version? Steve On Mon, Mar 24, 2008 at 3:23 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > > Steve Poe wrote: > >> The owners of the animal hospital where I work at want to consider > live/hot > >> backups through out the day so we're less likely to lose a whole > >> day of transaction. We use Postgresql 8.0.15. We do 3AM > >> backups, using pg_dumpall, to a file when there is very little activity. > > > You probably want to look into PITR, you can have a constant ongoing > > backup of your data and never lose more than a few minutes of data. The > > overhead isn't all the big especially if you are shipping the log files > > to a separate server. > > But note that you really need to update to a newer major release before > depending on PITR. While 8.0 nominally has support for it, it's taken > us several releases to really get the operational gotchas sorted out. > > regards, tom lane > - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Hi! Going a bit off topic, but one quick question: to avoid storing GB of WAL files that will probably take a lot of time to reload, how can the backup be "reset"? I suspect that it's something like stopping the WAL archiving, doing a new base backup, and restart archiving, but I've never done it (have been using SQL dumps), so... Yours Miguel Arroz On 2008/03/24, at 22:28, Matthew T. O'Connor wrote: Steve Poe wrote: At this point, I am just moving the pg_dumpall file to another server. Pardon my question: how would you 'ship the log files'? [ You should cc the mailing list so that everyone can benefit from the conversation. ] RTM: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance Miguel Arroz http://www.terminalapp.net http://www.ipragma.com smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] waiting for harddisk
On Mon, Mar 24, 2008 at 7:05 AM, petchimuthu lingam <[EMAIL PROTECTED]> wrote: > i am using postgresql 8.1.8, > > Following configurations: >shared_buffers = 5000 > work_mem = 65536 > maintenance_work_mem = 65536 > effective_cache_size = 16000 > random_page_cost = 0.1 That number, 0.1 is not logical. anything below 1.0 is generally a bad idea, and means that you've got some other setting wrong. > The cpu is waiting percentage goes upto 50%, and query result comes later, > > i am using normal select query ( select * from table_name ). > > table has more then 6 million records. You need faster disks if you want sequential scans to go faster. Look into a decent RAID controller (Areca, Escalade (forgot what they're called now) or LSI) with battery backed cache. Run RAID-10 on it with as many drives as you can afford to throw at the problem. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow pg_connect()
* Read about configuring and using persistent database connections (http://www.php.net/manual/en/function.pg-pconnect.php) with PHP Though make sure you understand the ramifications of using persistent connections. You can quickly exhaust your connections by using this and also cause other issues for your server. If you do this you'll probably have to adjust postgres to allow more connections, which usually means lowering the amount of shared memory each connection can use which can also cause performance issues. I'd probably use pgpool-II and have it handle the connection stuff for you rather than doing it through php. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning hot/live backups?
Miguel Arroz wrote: Going a bit off topic, but one quick question: to avoid storing GB of WAL files that will probably take a lot of time to reload, how can the backup be "reset"? I suspect that it's something like stopping the WAL archiving, doing a new base backup, and restart archiving, but I've never done it (have been using SQL dumps), so... Basically, you only need WAL logs from the last time you made a back-up of your data directory. We do nightly re-rsync of our data directory and then purge old WAL files that are no longer needed. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
