[GENERAL] Tablename.columnname%TYPE in Types On PostgreSQL 9.2
Hello Everyone, Trying to create a type using %Type seems not to work on PostgreSQL 9.2: CREATE TYPE type1 AS (tvar_1 TABLE1. COL1%TYPE , tvar_2 INTEGER); Returns XX ERROR: syntax error at or near % Where am I wrong ? Someone has an idea ? Thanks a lot ! BESSON Adrien
[GENERAL] Queries seldomly take 4s while normally take 1ms?
Hello I have a setup with one master and two slaves which are used by a closed source application. The database is asked the same query, a stored procedure, with different parameters about 4 million times per second at a peak rate of 150 times per second using 10 parallel connections. The slaves are decent Dell servers with 64GB RAM with dual hexacore CPUs and RAID10. Usually this stored procedure takes 1ms as it basically just does two selects against a GIST index for a prefix_range type. Seldomly though, about 1-3 times per day, one of these queries takes up to 4000ms! All those queries also runs in 1ms when executed manually some times later. Queries with similar parameters (who I supposed to use the same area of the index) also continues to run fast during that time. Queries with different paramers which are running parallel on different threads take 1ms, too, so it's not a general load problem. Cronjobs and other applications seem quiet during that time, there is no peak in any of our monitoring graphs. Automatic vacuum/analyze log entries on the master are not near the timestamps in question. So my problem seems not the query itself nor the way I indexed my data but what could it be? Some strange effects with streaming replication or cache invalidation? Apologies for not giving you reproducible problem but maybe you still have some ideas as I'm just curious as I've never seem such an effect during my MySQL years :-) The queries contain obvious customer data so I'm reluctant to give examples but again I doubt that an explain plan will help if only 1 out of 4E6 queries takes too long. bye, -christian- -- 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] Backup advice
On Mon, 8 Apr 2013 10:40:16 -0500 Shaun Thomas stho...@optionshouse.com wrote: Anyone else? If his db has low inserts/updates/deletes he can use diff between pg_dumps (with default -Fp) before compressing. --- --- Eduardo Morras emorr...@yahoo.es -- 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] Queries seldomly take 4s while normally take 1ms?
Christian -- original text snip because this POS editor won't let me properly edit postgres version ? type of replication ? changes from postgres config defaults ? Do they happen more at peak usage, semi regularly or sporadically ? Possibly some sporadic postgres process such as checkpoints of autovac processes kicking off. Do your logs show anything ? HTH, Greg W. -- 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] Tablename.columnname%TYPE in Types On PostgreSQL 9.2
Adrien Besson wrote: Trying to create a type using %Type seems not to work on PostgreSQL 9.2: CREATE TYPE type1 AS (tvar_1 TABLE1. COL1%TYPE , tvar_2 INTEGER); Returns XX ERROR: syntax error at or near % Where am I wrong ? Someone has an idea ? I think that the %TYPE syntax is PL/pgSQL only. Yours, Laurenz Albe -- 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] Queries seldomly take 4s while normally take 1ms?
Hello On Tue, 9 Apr 2013 03:53:13 -0700 (PDT) Greg Williamson gwilliamso...@yahoo.com wrote: Christian -- original text snip because this POS editor won't let me properly edit postgres version ? 9.2.3 type of replication ? As written, one master does streaming replication to two slaves. changes from postgres config defaults ? max_connections = 1000 # (change requires restart) shared_buffers = 20GB # min 128kB wal_level = hot_standby # minimal, archive, or hot_standby archive_mode = on # allows archiving to be done archive_command = 'test ! -f /srv/postgresql-data/archivedir/%f cp %p /srv/postgresql-data/archivedir/%f'# command to use to archive a logfile segment max_wal_senders = 3 # max number of walsender processes hot_standby = on# on allows queries during recovery max_standby_archive_delay = 1h # max delay before canceling queries max_standby_streaming_delay = 1h# max delay before canceling queries hot_standby_feedback = on # send info from standby to prevent effective_cache_size = 1024MB log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_directory = '/var/log/postgresql/' # directory where log files are written, log_filename = 'postgresql-9.2-data.log'# log file name pattern, log_file_mode = 0640# creation mode for log files, log_rotation_age = 0# Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_min_messages = notice log_min_duration_statement = 500# -1 is disabled, 0 logs all statements log_connections = on log_disconnections = on log_duration = on log_line_prefix = '%t [%p] %u@%d ' # special values: log_statement = 'all' # none, ddl, mod, all log_timezone = 'Europe/Berlin' track_activities = on track_counts = on track_io_timing = on datestyle = 'iso, dmy' timezone = 'Europe/Berlin' lc_messages = 'en_GB.UTF-8' # locale for system error message default_text_search_config = 'pg_catalog.english' shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1000 pg_stat_statements.track = all effective_cache_size is way too low, I noticed, can that be the cause? Do they happen more at peak usage, semi regularly or sporadically ? Sporadically. Not at a specific hour or minute-of-hour. Not only at peak times either. Sometimes at 05:xx in the morning, sometings at 10:xxh. Possibly some sporadic postgres process such as checkpoints of autovac processes kicking off. Do your logs show anything ? As far as I understood, Auto-Vacuum and Auto-Cleaning only happen on the master and their results get to the slaves via streaming replication. I therefore checked the master logs and, as written, all occurences of Auto-* are at least half an hour before or after. HTH, Greg W. bye, -christian- -- 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] pg_stat_get_last_vacuum_time(): why non-FULL?
On 2013-04-09 00:09, Kevin Grittner wrote: I'm not sure that what we're doing now is correct, but updating things as if a normal vacuum had been done would *not* be the thing to do. For starters, VACUUM FULL blows away the free space map and visibility map for a table. Among other things, that means that index-only scans will cease to work until the table has a normal vacuum. Ah, now it makes sense. Thank you, that's what I was looking for. And I agree with Jeff that this could be documented in more detail. Thanks, crl -- 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] postgresql command line exploit found in the wild
Re: Daniel Verite 2013-04-08 cd81d201-e9fa-4567-ac49-e3e762935747@mm Merlin Moncure wrote: if you have an internet facing database, patch it immediately! By the way: People running 9.1 on debian stable (squeeze) typically use this package: http://packages.debian.org/squeeze-backports/postgresql-9.1 Currently, it looks like the fix is only available in pre-compiled form for the amd64 architecture (see the bottom of the page). All other archs including the popular i386 are stuck at version: 9.1.7-1~bpo60+1 This is just packages.debian.org lagging behind. The packages were available on Thursday. (Excluding i386/armel.) Look at the timestamps on http://backports.debian.org/debian-backports/pool/main/p/postgresql-9.1/ . I find it problematic. One can always switch to the new apt.postgresql.org repository that has the latest versions, but how many people are going to not even notice the problem, trusting their normal upgrade path? I'm poking the backports people to throw more resources on building packages there. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] Queries seldomly take 4s while normally take 1ms?
Christian Hammers c...@lathspell.de wrote: 9.2.3 You really need to think about 9.2.4 Real Soon Now; there's a security fix that you probably should not wait on. max_connections = 1000 # (change requires restart) shared_buffers = 20GB # min 128kB Those are both potential causes. For max_connections, see this: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Maybe you happened to have enough users hit the enter key at the same moment to cause a process holding a lock to be starved of cycles or something similar. One problem with a large shared_buffers setting is that PostgreSQL can accumulate a very large number of dirty pages and flush them to the OS all at once. This can overwhelm the storage system and cause exactly the kind of symptoms you're seeing. effective_cache_size is way too low, I noticed, can that be the cause? Probably not. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] optimizer's cost formulas
The tool to tweak the query planner parameters mentioned in the article sounds very useful. Can we download it somewhere, either as binary or source code ? Sébastien On Mon, Apr 8, 2013 at 2:44 AM, Daniel Bausch bau...@dvs.tu-darmstadt.dewrote: Hi, AFAIK there is no such thing in the code or documentation. Nevertheless, for a paper I've published last year, I tried to create mathematical transcriptions at least for the I/O costs. http://dl.acm.org/citation.cfm?id=2236584.2236588coll=DLdl=ACMCFID=309621631CFTOKEN=77712718 Have a look at the appendix. Regards, Daniel Bausch Am 06.04.2013 12:51, schrieb dafNi: Hello everybody! I'm trying to understand the the query planner's cost estimator. I was not able to find anywhere the formulas that estimate the cost of each operation that the optimizer produces. I only found this for the sequential scan: SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pages * seq_page_cost ) where : cpu_tuple_cost=0.01 seq_page_cost=1.0 and rows and number of pages are given in the query plan (via EXPLAIN ANALYZE) I am wondering where could I find the rest formulas for the rest operations (e.g. HashAggregate, Nested Loop, Hash Join, Index Scan, Sort, etc) I also looked at costsize.c but could not find a formula like the above or maybe I couldn't make sense of it Thank you in advance! -- Daniel Bausch Wissenschaftlicher Mitarbeiter Technische Universität Darmstadt Fachbereich Informatik Fachgebiet Datenbanken und Verteilte Systeme Hochschulstraße 10 64289 Darmstadt Germany Tel.: +49 6151 16 6706 Fax: +49 6151 16 6229 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disallow SET command in a postgresql server
Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all. Is it possible?
Re: [GENERAL] Disallow SET command in a postgresql server
Fabio Rueda Carrascosa avances...@gmail.com writes: Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all. If you're allowing untrustworthy users to execute arbitrary SQL, preventing them from using SET would not make very much difference in how much trouble they can cause. You're wasting your time worrying about this. regards, tom lane -- 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] Disallow SET command in a postgresql server
My grant/revoke architecture is fine, you mean about costly cpu/ram queries? 2013/4/9 Tom Lane t...@sss.pgh.pa.us Fabio Rueda Carrascosa avances...@gmail.com writes: Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all. If you're allowing untrustworthy users to execute arbitrary SQL, preventing them from using SET would not make very much difference in how much trouble they can cause. You're wasting your time worrying about this. regards, tom lane
Re: [GENERAL] Disallow SET command in a postgresql server
Fabio Rueda Carrascosa escribió: My grant/revoke architecture is fine, you mean about costly cpu/ram queries? Sure. The SQL dialect supported by Postgres is Turing-complete, so people can write statements that consume arbitrary amounts of RAM and diskspace, and run for arbitrary amounts of time -- regardless of work_mem and other settings. (Actually, this was true even before the dialect got to be Turing-complete). Please don't top-post. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Disallow SET command in a postgresql server
On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa avances...@gmail.com wrote: My grant/revoke architecture is fine, you mean about costly cpu/ram queries? it has nothing to do with grant/revoke. There are multiple trivial things a user can do to DOS you server. You can prevent a lot of them, but it's definitely whack-a-mole. If you don't believe me, try logging into schemaverse in the next few moments. I just took it down. It will come up shortly. The only way I will advise opening up database to untrusted user is through pgbouncer (modified to allow only v3 parameterized queries that match a whitelist). merlin -- 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] Disallow SET command in a postgresql server
On Tue, Apr 9, 2013 at 11:13 AM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Apr 9, 2013 at 10:57 AM, Fabio Rueda Carrascosa avances...@gmail.com wrote: My grant/revoke architecture is fine, you mean about costly cpu/ram queries? it has nothing to do with grant/revoke. There are multiple trivial things a user can do to DOS you server. You can prevent a lot of them, but it's definitely whack-a-mole. If you don't believe me, try logging into schemaverse in the next few moments. I just took it down. It will come up shortly. schemaverse (one of the neatest things on the internet) is now functioning! merlin -- 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] Disallow SET command in a postgresql server
On 04/09/2013 09:06 AM, Alvaro Herrera wrote: Fabio Rueda Carrascosa escribió: My grant/revoke architecture is fine, you mean about costly cpu/ram queries? Sure. The SQL dialect supported by Postgres is Turing-complete, so people can write statements that consume arbitrary amounts of RAM and diskspace, and run for arbitrary amounts of time -- regardless of work_mem and other settings. (Actually, this was true even before the dialect got to be Turing-complete). A simple example that can crush your machine if you aren't careful: select generate_series(1,1); Now run it on 4 connections. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Create a DBLink from PostrgeSQL 9.2 to Oracle 11g
Hello, I am looking for an example on creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. I tried the below link and for some reason the ODBC_Link installation is failing. FYI., http://vibhork.blogspot.com/2011/05/postgresql-database-link-to-oracle.html I have the Heterogeneous DB Connection Between Oracle And PostgreSQL. I want the other way, reason being, I have 100's of oracle database and I need to pull data from these oracle databases and store into PostgreSQL. I tried many thing and not successful on the creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. Any help, highly appreciated. Waiting for your response. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Create-a-DBLink-from-PostrgeSQL-9-2-to-Oracle-11g-tp5751446.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Create a DBLink from PostrgeSQL 9.2 to Oracle 11g
On Tue, Apr 9, 2013 at 11:07 AM, kiran kiran.peddire...@cmegroup.com wrote: Hello, I am looking for an example on creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. I tried the below link and for some reason the ODBC_Link installation is failing. FYI., http://vibhork.blogspot.com/2011/05/postgresql-database-link-to-oracle.html I have the Heterogeneous DB Connection Between Oracle And PostgreSQL. I want the other way, reason being, I have 100's of oracle database and I need to pull data from these oracle databases and store into PostgreSQL. I tried many thing and not successful on the creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. Any help, highly appreciated. Waiting for your response. if you have any experience with java (and maybe even if you don't), you might want to check out jdbc-fdw. https://github.com/atris/JDBC_FDW merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is pg backend using memory for?
Hi, So, I checked a backend on Linux, and found such thing: 2ba63c797000-2ba63fa68000 rw-p 2ba63c797000 00:00 0 Size: 52036 kB Rss: 51336 kB Shared_Clean: 0 kB Shared_Dirty: 0 kB Private_Clean:0 kB Private_Dirty:51336 kB Swap: 0 kB Pss: 51336 kB (this is part of /proc/pid/smaps). This is not shared memory, so it's local. It's not related to any files (in such case first line would have path to file). What's more - this backend, during getting smaps copy was idle, and it's not stats manager, or anything like this. How can this be diagnosed, to find out why there is so much private memory? In case it matters: it's pg 9.1.6 on linux 2.6.18-164.2.1.el5 Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Inconsistent query performance
On Apr 8, 2013, at 7:09 PM, Kevin Grittner wrote: Ramsey Gurley rgur...@smarthealth.com wrote: I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. expression took 20147 ms: SELECT t0.appointment_id, t0.customer_id, t0.event_date, t0.patient_id, t0.transaction_id FROM customer.customer_transactions_detail t0 WHERE (t0.patient_id = 7441 AND t0.customer_id = 2965) Index Scan using customer_id_patient_id_idx on customer_transactions_detail t0 (cost=0.00..10.22 rows=1 width=24) (actual time=35.952..99.487 rows=14 loops=1) Index Cond: ((customer_id = 2965) AND (patient_id = 7441)) Total runtime: 99.537 ms So it took 20 seconds at 12:18pm today, but now it takes ~100ms. Well, often when you get a faster run time when running a query again it is due to caching, but there are other things which can come into play. There could be blocking. There could be a glut of disk writes at checkpoint time which holds up all other disk I/O. Is there a way to make checkpoints happen more frequently so that large ones don't cause two minute delays? You could have had a badly bloated index when the query was run the first time, and a VACUUM command or autovacuum cleaned things up before your explain analyze. I did run a vacuum on the entire database the day before. I don't know if I have auto-vacuuming set up. I'm currently using postgres 8.3.x That was much more vulnerable to the write glut problem than versions which are still in support. I'm in the process of upgrading to 9.2.x. I'm also moving the database to a dedicated machine with more RAM available to the database. So maybe a less crufty setup in another week or two. It's hard to even suggest what steps to take next without knowing the OS, your hardware, or your configuration. Please read these two pages: http://www.postgresql.org/support/versioning/ http://wiki.postgresql.org/wiki/SlowQueryQuestions Thanks for the suggestions Kevin :) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Inconsistent query performance
On Apr 8, 2013, at 8:46 PM, Jeff Janes wrote: On Monday, April 8, 2013, Ramsey Gurley wrote: Hi all, I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. Here is an example: sqlLogging _valuesForSQL ran 116509ms with sql string: I think that your IO subsystem is getting congested on occasion, and that you have a selection bias. You are seeing in your log the occasions on which it is running slow, but not seeing the occasions it is running fast. When you run it manually, you see a result similar to the (invisible, but frequent) times that it did not run slow. I think you're right about this. It seems to run fast most of the time. It's those times it takes 2 minutes to respond to a customer which I'd like to minimize or eliminate. But I think caching has a bit to do with it too. I can go back to the logs from last week and run a slow query and it will frequently be equally slow again. Not always, but enough that I think something else is wrong with my setup. You should monitor with sar, or iostat, or vmstat, or similar. I'll look into that. Thanks Jeff :) Cheers, Jeff
Re: [GENERAL] Queries seldomly take 4s while normally take 1ms?
On Tue, 9 Apr 2013 07:25:16 -0700 (PDT) Kevin Grittner kgri...@ymail.com wrote: Christian Hammers c...@lathspell.de wrote: 9.2.3 You really need to think about 9.2.4 Real Soon Now; there's a security fix that you probably should not wait on. Is scheduled (no access from outside to that network segment at least) max_connections = 1000 # (change requires restart) shared_buffers = 20GB # min 128kB Those are both potential causes. For max_connections, see this: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Maybe you happened to have enough users hit the enter key at the same moment to cause a process holding a lock to be starved of cycles or something similar. The application connects permanently with a fixed number of only 20 connections. One problem with a large shared_buffers setting is that PostgreSQL can accumulate a very large number of dirty pages and flush them to the OS all at once. This can overwhelm the storage system and cause exactly the kind of symptoms you're seeing. I have pretty big changes during early night hours on the master and then almost only read-only accesses during the day. As checkpoint_timeout is at 5min, there should not be any significant amount of dirty pages during daytime, right? Where would I verity this, with pg_stats_bgwriter.buffers_checkpoint and the Linux I/O graphs? bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Character set display
Hi, I got a data like: AHrühn And I need the output like: AHrühn The DB is running with UTF8 on Postgresql 9.2. Any help will be appreciated. Thanks.
Re: [GENERAL] Queries seldomly take 4s while normally take 1ms?
One of the most common causes I've seen for this is linux's vm.*dirty* settings to get in the way. Like so many linux kernel optimizations this one looks good on paper but gives at best middling improvements with occasional io storms that block everything else. On big mem machines doing a lot of writing IO I just set these to 0. Also tend to turn off swap as well as it's known to get in the way as well. settings for /etc/sysctl.conf vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 On Tue, Apr 9, 2013 at 3:41 AM, Christian Hammers c...@lathspell.de wrote: Hello I have a setup with one master and two slaves which are used by a closed source application. The database is asked the same query, a stored procedure, with different parameters about 4 million times per second at a peak rate of 150 times per second using 10 parallel connections. The slaves are decent Dell servers with 64GB RAM with dual hexacore CPUs and RAID10. Usually this stored procedure takes 1ms as it basically just does two selects against a GIST index for a prefix_range type. Seldomly though, about 1-3 times per day, one of these queries takes up to 4000ms! All those queries also runs in 1ms when executed manually some times later. Queries with similar parameters (who I supposed to use the same area of the index) also continues to run fast during that time. Queries with different paramers which are running parallel on different threads take 1ms, too, so it's not a general load problem. Cronjobs and other applications seem quiet during that time, there is no peak in any of our monitoring graphs. Automatic vacuum/analyze log entries on the master are not near the timestamps in question. So my problem seems not the query itself nor the way I indexed my data but what could it be? Some strange effects with streaming replication or cache invalidation? Apologies for not giving you reproducible problem but maybe you still have some ideas as I'm just curious as I've never seem such an effect during my MySQL years :-) The queries contain obvious customer data so I'm reluctant to give examples but again I doubt that an explain plan will help if only 1 out of 4E6 queries takes too long. bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion.
Re: [GENERAL] Character set display
On 4/9/2013 10:37 AM, AI Rumman wrote: Hi, I got a data like: AHrühn And I need the output like: AHrühn The DB is running with UTF8 on Postgresql 9.2. Any help will be appreciated. Thanks. when/where are you getting this? in a terminal session? from a web app? is your terminal session (or web app) configured to use UTF8 ? what exactly do you mean by 'I have a data like ' ? what OS is this on? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Queries seldomly take 4s while normally take 1ms?
On Apr 9, 2013, at 11:25 AM, Scott Marlowe scott.marl...@gmail.com wrote: One of the most common causes I've seen for this is linux's vm.*dirty* settings to get in the way. Like so many linux kernel optimizations this one looks good on paper but gives at best middling improvements with occasional io storms that block everything else. On big mem machines doing a lot of writing IO I just set these to 0. Also tend to turn off swap as well as it's known to get in the way as well. settings for /etc/sysctl.conf vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 I'll +1 on the you have to tune your Linux install advice. I found the PostgreSQL 9.0 High Performance book to be worth its weight in gold. A few days spent with the book and research on mailing lists improved our PostgreSQL performance multiple times over, and responsiveness under load by orders of magnitude. http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X On Tue, Apr 9, 2013 at 3:41 AM, Christian Hammers c...@lathspell.de wrote: Hello I have a setup with one master and two slaves which are used by a closed source application. The database is asked the same query, a stored procedure, with different parameters about 4 million times per second at a peak rate of 150 times per second using 10 parallel connections. The slaves are decent Dell servers with 64GB RAM with dual hexacore CPUs and RAID10. Usually this stored procedure takes 1ms as it basically just does two selects against a GIST index for a prefix_range type. Seldomly though, about 1-3 times per day, one of these queries takes up to 4000ms! All those queries also runs in 1ms when executed manually some times later. Queries with similar parameters (who I supposed to use the same area of the index) also continues to run fast during that time. Queries with different paramers which are running parallel on different threads take 1ms, too, so it's not a general load problem. Cronjobs and other applications seem quiet during that time, there is no peak in any of our monitoring graphs. Automatic vacuum/analyze log entries on the master are not near the timestamps in question. So my problem seems not the query itself nor the way I indexed my data but what could it be? Some strange effects with streaming replication or cache invalidation? Apologies for not giving you reproducible problem but maybe you still have some ideas as I'm just curious as I've never seem such an effect during my MySQL years :-) The queries contain obvious customer data so I'm reluctant to give examples but again I doubt that an explain plan will help if only 1 out of 4E6 queries takes too long. bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion. -- 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] Queries seldomly take 4s while normally take 1ms?
On Tue, Apr 9, 2013 at 12:37 PM, Steven Schlansker ste...@likeness.comwrote: On Apr 9, 2013, at 11:25 AM, Scott Marlowe scott.marl...@gmail.com wrote: One of the most common causes I've seen for this is linux's vm.*dirty* settings to get in the way. Like so many linux kernel optimizations this one looks good on paper but gives at best middling improvements with occasional io storms that block everything else. On big mem machines doing a lot of writing IO I just set these to 0. Also tend to turn off swap as well as it's known to get in the way as well. settings for /etc/sysctl.conf vm.dirty_background_ratio = 0 vm.dirty_ratio = 0 I'll +1 on the you have to tune your Linux install advice. I found the PostgreSQL 9.0 High Performance book to be worth its weight in gold. A few days spent with the book and research on mailing lists improved our PostgreSQL performance multiple times over, and responsiveness under load by orders of magnitude. http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X Yep. That's probably the single most useful performance tuning book anyone working with dbs can own. Even if you don't run postgresql, the hardware tuning and testing section is fantastic.
[GENERAL] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries
Hi, I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu server 12.04.02 LTS. I have dumped one of our databases from our windows server and restored it on the postgres server running on ubuntu in order to test for incompatibilities. But the thing I am noticing playing with pgAdmin is that queries are being performed much more slowly on the linux server compared to the old windows 2k8 server, even with linux running on a ssd with more ram, faster cpu etc. I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin on the linux database but performing queries consistently take 10x the amount of time that they take on the windows server so I'm obviously missing something here. The dump is a simple pg_dump -F c -f data.backup, then using pg_restore to restore in an empty database created with template0. What could be causing the new database to perform so abysmal?
Re: [GENERAL] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries
On 04/09/2013 02:29 PM, Giovanni Martina wrote: Hi, I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu server 12.04.02 LTS. I have dumped one of our databases from our windows server and restored it on the postgres server running on ubuntu in order to test for incompatibilities. But the thing I am noticing playing with pgAdmin is that queries are being performed much more slowly on the linux server compared to the old windows 2k8 server, even with linux running on a ssd with more ram, faster cpu etc. I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin on the linux database but performing queries consistently take 10x the amount of time that they take on the windows server so I'm obviously missing something here. The dump is a simple pg_dump -F c -f data.backup, then using pg_restore to restore in an empty database created with template0. What could be causing the new database to perform so abysmal? connect with psql to your new database and run reindex database your db name; -- 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] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries
On 04/09/2013 02:29 PM, Giovanni Martina wrote: Hi, I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu server 12.04.02 LTS. I have dumped one of our databases from our windows server and restored it on the postgres server running on ubuntu in order to test for incompatibilities. But the thing I am noticing playing with pgAdmin is that queries are being performed much more slowly on the linux server compared to the old windows 2k8 server, even with linux running on a ssd with more ram, faster cpu etc. I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin on the linux database but performing queries consistently take 10x the amount of time that they take on the windows server so I'm obviously missing something here. The dump is a simple pg_dump -F c -f data.backup, then using pg_restore to restore in an empty database created with template0. What could be causing the new database to perform so abysmal? I suspect a spurious network configuration -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Facing difficulty in PITR
Hello, We are facing difficulty in PITR. *Case :* Production server backups are happening every day at 12:00AM mid night and we are refreshing Test server with the backup and applying wal archives up to 10:00AM. *Issue:* * * While transferring wal archives from Production to Test server, unfortunately missing few wal archives,Due to this while doing PITR database is opening before reaching/satisfying *recovery_target_time* mentioned in recovery conf file. *Question*: Is there any way we can stop/Pause PITR and get message if any wal archives got missed before reaching recovery_target_time mentioned in recovery.conf file?. Thanks in advance.Any suggestions would be appreciated. Regards, Chiru
Re: [GENERAL] Facing difficulty in PITR
chiru r chir...@gmail.com writes: Hello, We are facing?difficulty?in PITR. Case : Production server backups are happening every day at 12:00AM mid night and we are refreshing Test server with the backup and applying wal?archives up to?10:00AM. Issue: While?transferring?wal archives from Production to Test server,?unfortunately?missing few wal archives,Due to this while doing ?PITR database is opening before reaching/satisfying?recovery_target_time?mentioned in recovery conf file. Question: Is there any way we can stop/Pause PITR and get message ?if any wal archives got missed before reaching ? recovery_target_time mentioned in recovery.conf file?. Consider using pg_standby which will wait for a missing WAL rather than using plain cp. Your instance will come up if time target is reached else sit there spinning while you figure out where the missing WALs are and move them into place. HTH Thanks in advance.Any?suggestions?would be?appreciated. Regards, Chiru -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Facing difficulty in PITR
Thanks Jerry,it worked for me and saved my time. Regards, Chiru On Wed, Apr 10, 2013 at 4:57 AM, Jerry Sievers gsiever...@comcast.netwrote: chiru r chir...@gmail.com writes: Hello, We are facing?difficulty?in PITR. Case : Production server backups are happening every day at 12:00AM mid night and we are refreshing Test server with the backup and applying wal?archives up to?10:00AM. Issue: While?transferring?wal archives from Production to Test server,?unfortunately?missing few wal archives,Due to this while doing ?PITR database is opening before reaching/satisfying?recovery_target_time?mentioned in recovery conf file. Question: Is there any way we can stop/Pause PITR and get message ?if any wal archives got missed before reaching ? recovery_target_time mentioned in recovery.conf file?. Consider using pg_standby which will wait for a missing WAL rather than using plain cp. Your instance will come up if time target is reached else sit there spinning while you figure out where the missing WALs are and move them into place. HTH Thanks in advance.Any?suggestions?would be?appreciated. Regards, Chiru -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: [GENERAL] Create a DBLink from PostrgeSQL 9.2 to Oracle 11g
You can try http://oracle-fdw.projects.postgresql.org/ On Wed, Apr 10, 2013 at 12:07 AM, kiran kiran.peddire...@cmegroup.comwrote: Hello, I am looking for an example on creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. I tried the below link and for some reason the ODBC_Link installation is failing. FYI., http://vibhork.blogspot.com/2011/05/postgresql-database-link-to-oracle.html I have the Heterogeneous DB Connection Between Oracle And PostgreSQL. I want the other way, reason being, I have 100's of oracle database and I need to pull data from these oracle databases and store into PostgreSQL. I tried many thing and not successful on the creating a DBLink from PostrgeSQL 9.2 to Oracle 11g. Any help, highly appreciated. Waiting for your response. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Create-a-DBLink-from-PostrgeSQL-9-2-to-Oracle-11g-tp5751446.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
