[PERFORM] slow nested views in 9.3
I read that the query planner changes with every release. Was there a change from 8.4 to 9.3 that would account for a major (2 orders of magnitude) difference in execution time for nested views after we upgraded to 9.3? http://stackoverflow.com/questions/24067543/nested-views-much-slower-in-pg-9-3-4-than-8-4-8 Prod server running Red Hat Enterprise Linux Server release 5.11 (Tikanga) and Pg 9.3.4 on a 2 x 2.33GHZ processor, 24GB of RAM, 900 GB of RAID 5 storage on 6 drive server. Pg configuration: autovacuum,on,configuration file autovacuum_analyze_scale_factor,0.1,configuration file autovacuum_analyze_threshold,50,configuration file autovacuum_max_workers,3,configuration file autovacuum_naptime,1min,configuration file autovacuum_vacuum_cost_delay,20ms,configuration file autovacuum_vacuum_cost_limit,-1,configuration file autovacuum_vacuum_scale_factor,0.2,configuration file autovacuum_vacuum_threshold,50,configuration file checkpoint_completion_target,0.9,configuration file checkpoint_segments,16,configuration file client_encoding,UTF8,session client_min_messages,warning,configuration file DateStyle,ISO, MDY,configuration file deadlock_timeout,5s,configuration file default_text_search_config,pg_catalog.english,configuration file effective_cache_size,4GB,configuration file from_collapse_limit,8,configuration file geqo_effort,5,configuration file geqo_threshold,12,configuration file hot_standby,on,configuration file lc_messages,en_US.UTF-8,configuration file lc_monetary,en_US.UTF-8,configuration file lc_numeric,en_US.UTF-8,configuration file lc_time,en_US.UTF-8,configuration file listen_addresses,*,configuration file log_connections,on,configuration file log_destination,stderr,configuration file log_directory,/dbms/postgresql/logs/dtfprod,configuration file log_disconnections,on,configuration file log_duration,off,configuration file log_error_verbosity,terse,configuration file log_filename,postgresql-%a.log,configuration file log_hostname,on,configuration file log_line_prefix, %m %u %d %h ,configuration file log_min_error_statement,error,configuration file log_min_messages,error,configuration file log_rotation_age,1d,configuration file log_rotation_size,100MB,configuration file log_timezone,US/Pacific,configuration file log_truncate_on_rotation,on,configuration file logging_collector,on,configuration file maintenance_work_mem,256MB,configuration file max_connections,200,configuration file max_stack_depth,8MB,configuration file max_wal_senders,5,configuration file port,5432,configuration file random_page_cost,2,configuration file shared_buffers,2GB,configuration file ssl,on,configuration file stats_temp_directory,pg_stat_tmp,configuration file temp_buffers,16MB,configuration file TimeZone,US/Pacific,configuration file track_activities,on,configuration file track_activity_query_size,1024,configuration file track_counts,on,configuration file track_functions,none,configuration file track_io_timing,off,configuration file update_process_title,on,configuration file wal_keep_segments,1920,configuration file wal_level,hot_standby,configuration file wal_sender_timeout,1min,configuration file wal_sync_method,fdatasync,configuration file work_mem,5MB,configuration file thanks, PasDep
Re: [ADMIN] [PERFORM] empty string Vs NULL
Hi, Please take this to another list, this has little to do with PostgreSQL admin or performance. Florent On Tue, Feb 10, 2015 at 4:53 AM, sridhar bamandlapally sridhar@gmail.com wrote: In application code is while inserting/updating: INSERT/UPDATE into ... ( '' ) - which is empty string in PG, and in Oracle its NULL while selecting: SELECT ... WHERE column IS NULL / NOT NULL the issue is, while DML its empty string and while SELECT its comparing with NULL On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin m.ma...@intershop.de wrote: Hi 2015-02-09 12:22 GMT+01:00 sridhar bamandlapally sridhar@gmail.com: Hi All We are testing our Oracle compatible business applications on PostgreSQL database, the issue we are facing is empty string Vs NULL In Oracle '' (empty string) and NULL are treated as NULL but, in PostgreSQL '' empty string not treated as NULL I need some implicit way in PostgreSQL where ''empty string can be treated as NULL It is not possible in PostgreSQL. PostgreSQL respects ANSI SQL standard - Oracle not. Regards Pavel p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects. A clean way would be to disallow empty strings on the PG side. This is somewhat combersome depending on how dynamic your model is and add some last on your db though. hmm, you could also consider disallowing NULLs, i.e. force empty strings. this may result in a better compatibility although unwise from postgres point of view (see null storage in PG) and neither way allow a compatibility out of the box: Postgres ORACLE '' IS NULL false true NULL || 'foo' NULL 'foo' as mention in another post, you need to check/fix your application. ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) IS NULL) oops, this shold be CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...)) -- and to ensure compatibility with your app or migration: CREATE OR REPLACE FUNCTION tablename_setnull_trf() RETURNS trigger AS $BODY$ BEGIN -- for all *string* columns NEW.colname1 = NULLIF (colname1,''); NEW.colname2 = NULLIF (colname2,''); NEW.colname3 = NULLIF (colname3,''); RETURN NEW; END; $BODY$ CREATE TRIGGER tablename_setnull_tr BEFORE INSERT OR UPDATE ON tablename FOR EACH ROW EXECUTE PROCEDURE tablename_setnull_trf(); You can query the pg catalog to generate all required statements. A possible issue is the order in which triggers are fired, when more than one exist for a given table: If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name ( http://www.postgresql.org/docs/9.3/static/trigger-definition.html ) regards, Marc Mamin -- Florent Guillaume, Director of RD, Nuxeo Open Source Content Management Platform for Business Apps http://www.nuxeo.com http://community.nuxeo.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] Survey: Max TPS you've ever seen
No problem with this. If anyone want to specify more details. But I want to know how far postgres can go. No matter OS or other variables. Gavin, you got more than 12000 TPS? 2015-02-09 19:29 GMT-02:00 Gavin Flower gavinflo...@archidevsys.co.nz: On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote: Hi, A survay: with pgbench using TPS-B, what is the maximum TPS you're ever seen? For me: 12000 TPS. -- Luis Antonio Dias de Sá Junior Important to specify: 1. O/S 2. version of PostgreSQL 3. PostgreSQL configuration 4. hardware configuration 5. anything else that might affect performance I suspect that Linux will out perform Microsoft on the same hardware, and optimum configuration for both O/S's... Cheers, Gavin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Luis Antonio Dias de Sá Junior
Re: [PERFORM] Survey: Max TPS you've ever seen
I'd suggest you run it on a large ramdisk with fsync turned off on a 32 core computer, see what you get, that will be a good indication of a maximum. Keep in mind though that 'postgres' with fsync (vs. without) is such a different creature that the comparison isn't meaningful. Similarly 'postgres' on volatile backing store vs. non-volatile isn't really a meaningful comparison. There's also a question here about the 't' in TPS. If you have no fsync and volatile storage, are you really doing 'transactions'? Depending on the definition you take, a transaction may have some sense of 'reliability' or atomicity which isn't reflected well in a ramdisk/no-fsync benchmark. It's probably not ideal to fill a mailing list with numbers that have no meaning attached to them, so why not set up a little web database or Google doc to record max TPS and how it was achieved? For example, imagine I tell you that the highest I've achieved is 124 tps. How does it help you if I say that? Graeme Bell On 10 Feb 2015, at 11:48, Luis Antonio Dias de Sá Junior luisjunior...@gmail.com wrote: No problem with this. If anyone want to specify more details. But I want to know how far postgres can go. No matter OS or other variables. Gavin, you got more than 12000 TPS? 2015-02-09 19:29 GMT-02:00 Gavin Flower gavinflo...@archidevsys.co.nz: On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote: Hi, A survay: with pgbench using TPS-B, what is the maximum TPS you're ever seen? For me: 12000 TPS. -- Luis Antonio Dias de Sá Junior Important to specify: 1. O/S 2. version of PostgreSQL 3. PostgreSQL configuration 4. hardware configuration 5. anything else that might affect performance I suspect that Linux will out perform Microsoft on the same hardware, and optimum configuration for both O/S's... Cheers, Gavin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Luis Antonio Dias de Sá Junior -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] slow query
am connecting three tables in query. one table have 73000 records another two tables have 138000 records. but its take 12 sec for show 12402 rows in tables Tables Structure: Items Table CREATE TABLE C_SAM_Master.items ( itemno integer NOT NULL, itemname character varying(250) NOT NULL, itemcode character varying(250) NOT NULL, shortname character varying(20) NOT NULL, aliasname character varying(250) NOT NULL, aliasnamelanguage character varying(250) NOT NULL, masteritemno integer NOT NULL, groupno1 smallint NOT NULL, groupno2 smallint NOT NULL, groupno3 smallint NOT NULL, commodityno smallint NOT NULL, unitno smallint NOT NULL, weighttype character(1) NOT NULL, altunitno smallint NOT NULL, weight double precision NOT NULL, reqmrp character(1) NOT NULL, reqbatch character(1) NOT NULL, reqmfrdate character(1) NOT NULL, mfrdateformat character varying(20) NOT NULL, reqexpdate character(1) NOT NULL, expdateformat character varying(20) NOT NULL, expdays1 smallint NOT NULL, expdays2 character(1) NOT NULL, expinfodays smallint NOT NULL, stdsaleratemethod smallint NOT NULL, salesrateper smallint NOT NULL, stdprofit1 double precision NOT NULL, stdprofit2 character(1) NOT NULL, includestockrep character(1) NOT NULL, minstock double precision NOT NULL, minstockunit smallint NOT NULL, minsaleqtynos double precision NOT NULL, minsaleqtyunit smallint NOT NULL, minsaleqty double precision NOT NULL, description text NOT NULL, remarks character varying(250) NOT NULL, actpurchaseorder character(1) NOT NULL, actpurchase character(1) NOT NULL, actpurchasereturn character(1) NOT NULL, actsalesorder character(1) NOT NULL, actsales character(1) NOT NULL, actsalesreturn character(1) NOT NULL, actreceiptnote character(1) NOT NULL, actdeliverynote character(1) NOT NULL, actconsumption character(1) NOT NULL, actproduction character(1) NOT NULL, actestimate character(1) NOT NULL, notifypurchaseorder character varying(250) NOT NULL, notifypurchase character varying(250) NOT NULL, notifypurchasereturn character varying(250) NOT NULL, notifysalesorder character varying(250) NOT NULL, notifysales character varying(250) NOT NULL, notifysalesreturn character varying(250) NOT NULL, notifyreceiptnote character varying(250) NOT NULL, notifydeliverynote character varying(250) NOT NULL, notifyconsumption character varying(250) NOT NULL, notifyproduction character varying(250) NOT NULL, notifyestimate character varying(250) NOT NULL, act boolean NOT NULL, recordowner smallint NOT NULL, lastmodified smallint NOT NULL, crdate timestamp without time zone NOT NULL, stdmaxprofit double precision NOT NULL, commodityname character varying(100) NOT NULL, lst double precision NOT NULL, unittype character(1) NOT NULL, unit1 character varying(15) NOT NULL, unit2 character varying(15) NOT NULL, units integer NOT NULL, unitname character varying(50) NOT NULL, decimals smallint NOT NULL, groupname1 character varying(50) NOT NULL, groupname2 character varying(50) NOT NULL, groupname3 character varying(50) NOT NULL, repgroupname character varying(160) NOT NULL, masteritemname character varying(100) NOT NULL, altunit1 character varying(15) NOT NULL, altunit2 character varying(15) NOT NULL, altunits integer NOT NULL, altunitname character varying(50) NOT NULL, altunitdecimals smallint NOT NULL, CONSTRAINT items_itemno_pk PRIMARY KEY (itemno), CONSTRAINT items_altunitno_fk FOREIGN KEY (altunitno) REFERENCES C_SAM_Master.measureunits (unitno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_commodityno_fk FOREIGN KEY (commodityno) REFERENCES C_SAM_Master.commodity (commodityno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno1_fk FOREIGN KEY (groupno1) REFERENCES C_SAM_Master.itemgroup1 (groupno1) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno2_fk FOREIGN KEY (groupno2) REFERENCES C_SAM_Master.itemgroup2 (groupno2) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_groupno3_fk FOREIGN KEY (groupno3) REFERENCES C_SAM_Master.itemgroup3 (groupno3) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_lastmodified_fk FOREIGN KEY (lastmodified) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_masteritemno_fk FOREIGN KEY (masteritemno) REFERENCES C_SAM_Master.masteritems (masteritemno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_recordowner_fk FOREIGN KEY (recordowner) REFERENCES appsetup.user1 (userno) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, CONSTRAINT items_unitno_fk FOREIGN KEY (unitno) REFERENCES C_SAM_Master.measureunits (unitno) MATCH SIMPLE ON UPDATE NO ACTION ON
Re: [PERFORM] Survey: Max TPS you've ever seen
On 10/02/15 10:29, Gavin Flower wrote: On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote: Hi, A survay: with pgbench using TPS-B, what is the maximum TPS you're ever seen? For me: 12000 TPS. -- Luis Antonio Dias de Sá Junior Important to specify: 1. O/S 2. version of PostgreSQL 3. PostgreSQL configuration 4. hardware configuration 5. anything else that might affect performance I suspect that Linux will out perform Microsoft on the same hardware, and optimum configuration for both O/S's... Yes, exactly - and also the pgbench parameters: - scale - number of clients - number of threads - statement options (prepared or simple etc) - length of test We've managed to get 4 to 6 TPS on some pretty serious hardware: - 60 core, 1 TB ram - 16 SSD + 4 PCIe SSD storage - Ubuntu 14.04 - Postgres 9.4 (beta and rc) ...with Postgres parameters customized: - checkpoint_segments 1920 - checkpoint_completion_target 0.8 - wal_buffers 256MB - wal_sync_method open_datasync - shared_buffers 10GB - max_connections 600 - effective_io_concurrency 10 ..and finally pgbench parameters - scale 2000 - clients 32, 64, 128, 256 (best results at 32 and 64 generally) - threads = 1/2 client number - prepared option - 10 minute test run time Points to note, we did *not* disable fsync or prevent buffers being actually written (common dirty tricks in benchmarks). However, as others have remarked - raw numbers mean little. Pgbench is very useful for testing how tuning configurations are helping (or not) for a particular hardware and software setup, but is less useful for answering the question how many TPS can postgres do... Regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
