[GENERAL] question about memory usage
Hi, I run Windows and I started using 64 bit PostgreSQL 9.3 a month ago. I have several PostGIS databases on localhost, with these statistics: === Xact XactRolled Blocks Blocks TuplesTuples Size Committed BackReadHit Returned Fetched --- 1839 MB 52290105 7593 1962771 27426946 502140 312 MB 23 0 177 1671 2796 902 340 MB 31 0 242 2867 4138 1426 69 MB 40796 21 7996 2019122 27852048 463184 71 MB 23 0 176 1674 2101 914 663 MB 37 0 328 2825 14240 1481 6298 kB 51680 0 950 1431658 23355239 291505 === Looking in process explorer, I see unusual size for postgres server process, i.e. working set reported around 1GB: http://i.imgur.com/HmkvFLM.png (same in attachment) I also use SqlExpress server with several databases (including spatial) but that doesn't go above 100MB in Private Bytes and around 1 MB in Working Set. I wanted to ask, if this is normal, or there is some problem with my server setting? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about memory usage
Bill, thanks for your reply. "shared_buffers" is set to "128MB". Now that you mention config file, the only thing I did change there, and was suggested to me while I made some on my databases was "max_locks_per_transaction = 5" (which has default value 1). After resetting "max_locks_per_transaction" to default value and restarting the server, memory occupied in working set reduced linearly to around 200 MB. I guess this is it. On Wed, Jul 23, 2014 at 5:53 AM, Bill Moran wrote: > > I'm not an expert on the Windows version, so I could be off-base, but the > POSIX versions of Postgres allocate shared_buffers worth of memory at > startup > and lock it for exclusive use by Postgres. Do you have shared_buffers set > to > around 1G, perhaps? > >
Re: [GENERAL] question about memory usage
Tomas, thanks for the heads up! I certainly didn't know what this setting means, except the obvious name. Your links helped. I just can't find where was this setting suggested, but IIRC it was in a guide for migrating OSM to PostGIS, as other tables were just created by GDAL OGR. I had this line in my `postgresql.conf`: max_locks_per_transaction = 5# 1 that's why I thought that 1 is the default, but it may be that commented value was entered by me, and not the real default value. I've set it now to 64. Thanks again On Wed, Jul 23, 2014 at 4:10 PM, Tomas Vondra wrote: > On 23 Červenec 2014, 15:56, klo uo wrote: > > Bill, thanks for your reply. > > > > "shared_buffers" is set to "128MB". > > > > Now that you mention config file, the only thing I did change there, and > > was suggested to me while I made some on my databases was > > "max_locks_per_transaction = 5" (which has default value 1). > > > > After resetting "max_locks_per_transaction" to default value and > > restarting > > the server, memory occupied in working set reduced linearly to around 200 > > MB. > > > > I guess this is it. > > The default value for max_locks_per_transaction is 64, not 1. Values > this high are quite insane, and suggest that either you don't know what > the value means (and increased it just in case, because "more is always > better") or that the application does something wrong (eventually > requiring so many locks). > > You really need to check this (notice how the amount of shared memory > depends on max_locks_per_transaction): > > > http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS > > and this (which explains what max_locks_per_transaction does): > > > http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html > > regards > Tomas > >
[GENERAL] Calltips in pgAdmin SQL Editor
Hi, I believe SQL Editor in pgAdmin wx GUI is Scintilla. Scintilla supports calltips (as in SciTE with *.api files). If above is true, is there a way to make SQL Editor support calltips on user defined commands?
Re: [GENERAL] Calltips in pgAdmin SQL Editor
Hi Guillaume, if not obvious here are some usage examples: - novice user learning SQL or PL/pgSQL - user learning PostGIS - user documenting own functions I've also attached two images: [1] auto-completion PostGIS function [2] calltip describing function basics, and providing information about parameters and return values Apologies for posting in wrong list, would be great if Administrator could move the message thread to appropriate group. Thanks, Klo On Sun, Jul 27, 2014 at 1:43 PM, Guillaume Lelarge wrote: > Hi, > > 2014-07-27 8:55 GMT+02:00 klo uo : > > Hi, >> >> I believe SQL Editor in pgAdmin wx GUI is Scintilla. >> Scintilla supports calltips (as in SciTE with *.api files). >> >> If above is true, is there a way to make SQL Editor support calltips on >> user defined commands? >> >> > pgAdmin uses scintilla on the SQL query tool. I think I get what calltips > are, not sure what we should do with those on the query tool. Wanna explain > a bit more? > > Thanks. > > PS: this should better go to pgadmin-support mailing list. > > > -- > Guillaume. > http://blog.guillaume.lelarge.info > http://www.dalibo.com > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Calltips in pgAdmin SQL Editor
OK, replica here: http://www.postgresql.org/message-id/caa-8ld8rntybiqwoz1rh69ugj0nbp30cgj1vv2_t6kly7me...@mail.gmail.com Thanks On Sun, Jul 27, 2014 at 2:22 PM, Guillaume Lelarge wrote: > 2014-07-27 14:13 GMT+02:00 klo uo : > > >> >> Apologies for posting in wrong list, would be great if Administrator >> could move the message thread to appropriate group. >> >> > That won't happen. This isn't a web forum :) >
[GENERAL] How to restore backup database
Hi, I use PostrgrSQL 9.3 for couple of months now, on Windows. I installed new Window OS, and before installing, I made tar backups of my PostgreSQL databases. Now I want to restore these on my new OS, but I can't find such option in pgAdmin. In documentation (http://www.postgresql.org/docs/9.3/static/backup.html) I see only suggestions how to restore sql dumps with command line tools, but none how to restore single file tar backups. Please suggest the right way to restore my backup databases. Thanks
Re: [GENERAL] How to restore backup database
Hi Adrian, I used pgAdmin backup command. However in the meantime I figured how to restore. I first have to create database, and then use restore option which becomes available if I right-click on a database. On MSSQL for example, I can restore database without creating database first, and I guess I was expecting similar behavior. Cheers On Mon, Sep 8, 2014 at 6:47 PM, Adrian Klaver wrote: > On 09/08/2014 09:04 AM, klo uo wrote: > >> Hi, >> >> I use PostrgrSQL 9.3 for couple of months now, on Windows. >> I installed new Window OS, and before installing, I made tar backups of >> my PostgreSQL databases. >> > > What was the command you used? > > Now I want to restore these on my new OS, but I can't find such option >> in pgAdmin. >> >> >> In documentation (http://www.postgresql.org/docs/9.3/static/backup.html) >> I see only suggestions how to restore sql dumps with command line tools, >> but none how to restore single file tar backups. >> >> Please suggest the right way to restore my backup databases. >> > > Well that depends on the answer to the previous question. > > >> >> Thanks >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] How to restore backup database
On Mon, Sep 8, 2014 at 8:10 PM, Adrian Klaver wrote: > > You do not have to create a database in Postgres either. There are some > system databases already created for you, template0(read only best left > alone), template1 and postgres. You can connect to one of these(best > practices, use postgres) and then have the restore script create the new > database. If you do this you need to check the Include CREATE > DATABASE statement box (http://www.pgadmin.org/docs/1.18/restore.html) > > > To get a better idea of what is going on I would suggest reading the > following sections of the docs: > > http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html > > http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html > > This is what pgAdmin is using behind the scenes. > Nice. Thanks for the tip and suggestions. Cheers
