Re: [GENERAL] PostgreSQL vs Mongo
Hi, On 17 October 2013 02:30, CS DBA cs_...@consistentstate.com wrote: Anyone have any thoughts on why we would / would not use Mongo for a reporting environment. hm.. I wouldn't use anything which doesn't support rich SQL as a backed for reporting system. In mongo, simple selects are fine but anything complex requires map-reduce style query. Hence you need real developers to maintain it. what are the use cases where mongo is a good fit? what are the drawbacks long term? is mongo a persistent db or simply a big memory cache? does mongo have advantages over Postgres hstore? This is very good summary: http://aphyr.com/posts/284-call-me-maybe-mongodb (entire Call me maybe series is good: http://aphyr.com/tags/jepsen, even Postgres is there) The problem with all NoSQL solutions is that they are selected based on the features and APIs not based on consistency vs. availability tradeoffs. Moreover, distributed system should have partition tolerance and many NoSQL dbs simply drop data in this case. -- Ondrej -- 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] Restore 1 Table from pg_dumpall?
Hi, On 23 January 2013 04:57, Rich Shepard rshep...@appl-ecosys.com wrote: Is there a way I can extract a single table's schema and data from the full backup? If so, I can then drop the fubar'd table and do it correctly this time. You should grep for: - CREATE TABLE - COPY statements and then note line numbers (fgrep -n). Finally, used `sed` to get the right part(s) of a file (schema + data): sed -n 'startline,endlinep' dump.sql out.sql (ie. sed -n '10,1000p') -- Ondrej -- 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 contrib 9.2.x
Hi, On 14 December 2012 17:56, a...@hsk.hk a...@hsk.hk wrote: I could see that it would install older PostgreSQL 9.1 and postgresql-contrib-9.1. As I already have 9.2.1 and do not want to have older version 9.1 in parallel, I aborted the apt install. How can I get pure postgresql-contrib for Postgresql 9.2.x? You need PostreSQL PPA: sudo apt-get update sudo apt-get install python-software-properties sudo add-apt-repository ppa:pitti/postgresql sudo apt-get install postgresql-contrib-9.2 -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] Problem with aborting entire transactions on error
Hi, On 11 December 2012 06:25, Виктор Егоров vyego...@gmail.com wrote: On the other hand, it is possible to write whenever sqlerror continue; and this will make ORACLE to process all the statements inide the script, ignoring all errors. This is a general feature, available not only for sqlplus scripts — as mentioned, a series of 100 INSERTs can have 5 failing ones and commit statement will result in 95 new records.. For example, nCluster (and Greenplum) has similar feature for COPY: page 13 Error Logging in COPY http://www.pgcon.org/2009/schedule/attachments/135_PGCon%202009%20-%20Aster%20v6.pdf. Around the same time AsterData tried to push some COPY enhancements (error logging and partitioning): http://archives.postgresql.org/pgsql-hackers/2009-10/msg00247.php -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] large database
Hi, On 11 December 2012 07:26, Mihai Popa mi...@lattica.com wrote: First, the project has been started using MySQL. Is it worth switching to Postgres and if so, which version should I use? You should to consider several things: - do you have in-depth MySQL knowledge in you team? - do you need any sql_mode features? (http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html) - do you need flexible, easy to setup and monitor replication? - do you need multi-master? - do you need REPLACE / INSERT ... ON DUPLICATE KEY UPDATE / INSERT IGNORE syntax? - do you need many connections to your database w/o deploying / using load balancer? - do you need something which is MySQL only? (http://dev.mysql.com/doc/refman/5.0/en/compatibility.html) If you have 4 or more 'yes' then I would stick with MySQL... -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] Ubutu 12.04 and PostgreSQL9.2.1
Hi, On 7 December 2012 14:17, a...@hsk.hk a...@hsk.hk wrote: I have questions about Linux Write cache sizing: 1) /proc/sys/vm/dirty_ratio : current value (default) 20 2) /proc/sys/vm/dirty_background_ratio: current value (default) 10 I am using Ubuntu 12.04 (8GB RAM) and PostgreSQL 9.2.1, what values of these kernel ratios should be set for better PostgreSQL database performance? Read this thread: http://archives.postgresql.org/pgsql-performance/2011-10/msg00076.php Mainly, - http://archives.postgresql.org/pgsql-performance/2011-10/msg00078.php - and http://archives.postgresql.org/pgsql-performance/2011-10/msg00080.php To sum up: - with 8G RAM you should be fine with the defaults - keep keep dirty_ratio lower than the size of your disk controller cache - you can use dirty_bytes and dirty_background_bytes in recent kernels -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] About aggregates...
Hi, On 30 November 2012 08:06, Michael Giannakopoulos miccagi...@gmail.com wrote: However an aggregate function feeds me one a tuple for each call, but I would like to have access to a batch of tuples per function call. Is there any possible way to perform something like this? Yes, this might be good for you:: WINDOW WINDOW indicates that the function is a window function rather than a plain function. This is currently only useful for functions written in C. The WINDOW attribute cannot be changed when replacing an existing function definition. http://www.postgresql.org/docs/9.1/static/sql-createfunction.html Apart from C you can use this in Pl/R: http://www.joeconway.com/plr/doc/plr-window-funcs.html -- Ondrej -- 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] How to list all schema names inside a PostgreSQL database through SQL
Hi, On 15 November 2012 23:31, Xiaobo Gu guxiaobo1...@gmail.com wrote: How can I list all schema names inside a PostgreSQL database through SQL, especially thoese without any objects created inside it. Use -E psql's option: -E, --echo-hiddendisplay queries that internal commands generate then you get SQL query for each internal command. The second option is to use information_schema.schemata view (this is works across databases) -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] alter view foo set () -- fixed in 9.2 stable, but when will it be released?
Hi, On 5 November 2012 08:39, Chris Angelico ros...@gmail.com wrote: On Sat, Nov 3, 2012 at 9:15 AM, Joe Van Dyk j...@tanga.com wrote: Point of random curiosity: The commit mentioned adds the following line: if (rinfo-reloptions strlen(rinfo-reloptions) 0) Is there a reason this isn't done as: if (rinfo-reloptions *rinfo-reloptions) ? It seems like overkill to ascertain the string length just to find out if the first character is the null terminator. My guess is to be multibyte encoding safe: UTF-16 or similar. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Strategies/Best Practises Handling Large Tables
Hi, On 13 October 2012 01:44, Chitra Creta chitracr...@gmail.com wrote: I currently have a table that is growing very quickly - i.e 7 million records in 5 days. This table acts as a placeholder for statistics, and hence the records are merely inserted and never updated or deleted. Many queries are run on this table to obtain trend analysis. However, these queries are now starting to take a very long time (hours) to execute due to the size of the table. Have you changed autovacuum settings to make it more agressive? Another options is to run analyse after loading. I have put indexes on this table, to no significant benefit. Some of the other strategies I have thought of: 1. Purge old data 3. Partition Those two go together. Partitioning is useful if you can constrain queries to specific ranges ie. this query needs last two days. You shouldn't go over 200 - 300 partitions per table. Partition granularity should be the same as the amount of data in average query. if you run weekly queries then use weekly partitions (bi-weekly or daily partitions might work but I do not have good experience). It is easy to purge old data because you need to drop unwanted partitions (no table/index bloat). Loading is little bit tricky becuase you have load data into right partition. 4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required I think this way to the hell. You start with few tables and then you add more tables until you realise that it takes longer to update them then run your queries :) You might benefit from query parallelisation, for example, pgpool-II, Stado, Postgres XC or do not use Postgres at all. For example, any column oriented NoSQL database might be good choice. -- Ondrej Ivanic (ondrej.iva...@gmail.com) (http://www.linkedin.com/in/ondrejivanic) -- 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] Index only scan
Hi, On 10 October 2012 23:03, Lars Helge Øverland larshe...@gmail.com wrote: We are now in the process of designing a new component for analytics and this feature got me thinking we could utilize postgres over other alternatives like column-oriented databases. Basically we will have a wide, denormalized table with 20+ columns with relatively low cardinality. Typically we will have queries which sums a fact column based on where/group by clauses on several dimension columns (standard data warehouse stuff). An example would be select a, b, c, sum(d) from analytics where a=1 and b=2 group by a,b,c; Finally, is there anyone else who are using postgres for this purpose and have some good tips to share in order to achieve good performance, including index strategies, beyond the standard config best practices? yes, we had fact table which has around 250 columns and 250mil rows. The question is if you can partition your data set. For example, monthly partition. This keeps indexes small but all queries must be constrained by the same column as is used for partitioning (ie. monthly partitions - every query should have datetime between ... and ...) From my experience postgres is not good with large group by queries. For example, your query: select a, b, c, sum(d) from analytics where a=1 and b=2 group by a,b,c could be executed over multiple connections: insert into t select select a, b, c, sum(d) as d from analytics where c = val1 and c val2 and a=1 and b=2 group by a,b,c insert into t select select a, b, c, sum(d) as d from analytics where c = val2 and c val3 and a=1 and b=2 group by a,b,c ... insert into t select select a, b, c, sum(d) as d from analytics where c = valN-1 and c valN and a=1 and b=2 group by a,b,c and then get the final result: select a, b, c, sum(d) from t group by a,b,c You can use pgpool-II parallel query feature instead of manual slicing. -- Ondrej Ivanic (ondrej.iva...@gmail.com) (http://www.linkedin.com/in/ondrejivanic) -- 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] moving from MySQL to pgsql
Hi, On 10 October 2012 19:47, Vineet Deodhar vineet.deod...@gmail.com wrote: 3) Can I simulate MySQL's TINYINT data-type (using maybe the custom data type or something else) What do you exactly mean? Do you care about storage requirements or constraints? The smallest numeric type in postgres is smallint: range is +/- 32K and you need two bytes. You can use check constraint to restrict the range (postgres doesn't have signed / unsigned types): create table T ( tint_signed smallint check ( tint_signed = -128 and tint_signed = 127 ), tint_unsigned smallint check ( tint_unsigned = 0 and tint_unsigned = 255 ) ) if you care about storage then char (yes, with quotes) might be the right type for you. -- Ondrej Ivanic (ondrej.iva...@gmail.com) (http://www.linkedin.com/in/ondrejivanic) -- 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] Can I force a query plan to materialise part?
Hi, On 2 October 2012 12:33, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: I have a query that joins two views, and takes 28 seconds to run. However if I create temporary tables that contain the contents of each view, and then join them, the total time is 1.3 seconds. try offset 0 (or you can tweak statistics collector to get better estimates): select ... from (select * from view offset 0) as v http://blog.endpoint.com/2009/04/offset-0-ftw.html -- Ondrej Ivanic (ondrej.iva...@gmail.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] Can I force a query plan to materialise part?
Hi, On 2 October 2012 13:28, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: I have a query that joins two views, and takes 28 seconds to run. However if I create temporary tables that contain the contents of each view, and then join them, the total time is 1.3 seconds. try offset 0 (or you can tweak statistics collector to get better estimates): select ... from (select * from view offset 0) as v I wish I could work out what's wrong with the statistics that cause the query plan to go awry.. the tables aren't actually very large and I've played with the statistics setup previously and it seemed right.. Try this (in single session): explain analyze your query set default_statistics_target = 1000 (or 500 or 250; 1000 might take ages) analyze table_1; analyze table_2; ..., analyze table_N; (all involved tables in your query) explain analyze your query and compare explains outputs. If estimates are very different (magnitude or two) then you should tweak autovacuum frequency and set per column statistics (ie. keep default_statistics_target = 100 (default), and change it on per column basis) but this could be tedious: Although per-column tweaking of ANALYZE frequency might not be very productive, you might find it worthwhile to do per-column adjustment of the level of detail of the statistics collected by ANALYZE. Columns that are heavily used in WHERE clauses and have highly irregular data distributions might require a finer-grain data histogram than other columns. See ALTER TABLE SET STATISTICS, or change the database-wide default using the default_statistics_target configuration parameter. Also, by default there is limited information available about the selectivity of functions. However, if you create an expression index that uses a function call, useful statistics will be gathered about the function, which can greatly improve query plans that use the expression index. http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-STATISTICS -- Ondrej Ivanic (ondrej.iva...@gmail.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] transitive pruning optimization on the right side of a join for partition tables
Hi, On 30 September 2012 16:36, Waldo, Ethan ewa...@healthetechs.com wrote: Once again I reiterate that I don't have control over the query construction and I am currently running postgresql 9.1.5. My question is, does postgresql support transitive pruning optimization on the right side of a join for partition tables? If so, how do I get that to work? If not, are there plans for this and when should a release with this feature be expected? No, postgres is not smart enough. You need to use same condition (dates.recorded_on_id = ...) for myfact table (myfact.recorded_on_id = ) but you do not have control over the query construction... -- Ondrej Ivanic (ondrej.iva...@gmail.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] transitive pruning optimization on the right side of a join for partition tables
Hi, On 30 September 2012 16:36, Waldo, Ethan ewa...@healthetechs.com wrote: My question is, does postgresql support transitive pruning optimization on the right side of a join for partition tables? If so, how do I get that to work? If not, are there plans for this and when should a release with this feature be expected? Few guys implemented this using Postgres (8.3) and published their results: Join Optimization Techniques for Partitioned Tables ABSTRACT Table partitioning splits a table into smaller parts that can be accessed, stored, and maintained independent of one an- other. The main use of partitioning used to be in reduc- ing the time to access large base tables in parallel systems. Partitioning has evolved into a powerful mechanism to im- prove the overall manageability of both centralized and par- allel database systems. Partitioning simplifies administra- tive tasks like data loading, removal, backup, statistics main- tenance, and storage provisioning. More importantly, SQL extensions and MapReduce frameworks now enable applica- tions and user queries to specify how derived tables should be partitioned. However, query optimization techniques have not kept pace with the rapid advances in usage and user con- trol of table partitioning. We address this gap by developing new techniques to generate efficient plans for SQL queries involving multiway joins over partitioned tables. Our tech- niques are designed for easy incorporation into bottom-up query optimizers in centralized and parallel database sys- tems. We have prototyped these techniques in PostgreSQL and in a parallel database system composed of PostgreSQL nodes managed by Hadoop. An extensive evaluation shows that our partition-aware optimization techniques, with low overhead, generate plans that are often an order of magni- tude better than plans produced by current optimizers. 8. CONCLUSION Query optimization technology has not kept pace with the growing usage and user control over table partitioning. We addressed this gap by developing new partition-aware optimization techniques to generate efficient plans for SQL queries. We made the following contributions: • Our new techniques are designed for easy incorporation into bottom-up query optimizers for both centralized and parallel systems. • We have prototyped these techniques in PostgreSQL and in a parallel shared-nothing database system composed of PostgreSQL nodes managed by Hadoop. • An extensive evaluation showed that our optimizer, with low optimization-time overhead, generates plans that are often an order of magnitude better than plans produced by current optimizers. www.cs.duke.edu/~shivnath/tmp/paqo_draft.pdf -- Ondrej Ivanic (ondrej.iva...@gmail.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] transitive pruning optimization on the right side of a join for partition tables
Hi, On 30 September 2012 21:00, Waldo, Ethan ewa...@healthetechs.com wrote: Yeah, I actually saw that paper but couldn't find a date on it. Currently their techniques are well outside of the scope of my current problem particularly in consideration that I could switch to MySQL which does support the right side join pruning. I figured if MySQL can do it, there might be a good chance Postgres can too or will soon. Google says 2010 (Google Scholar). I was in the same position 2 years ago but I was able to change / optimise our BI solution. Good to know that MySQL can do this. -- Ondrej Ivanic (ondrej.iva...@gmail.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] transitive pruning optimization on the right side of a join for partition tables
Hi,
On 1 October 2012 01:14, Tom Lane t...@sss.pgh.pa.us wrote:
Waldo, Ethan ewa...@healthetechs.com writes:
This query does a sequence scan and append across all the partition tables:
select dates.date_description FROM myfact as myfact, dates as
dates where myfact.recorded_on_id = dates.recorded_on_id and
dates.recorded_on_id IN ('4617', '4618', '4619', '4620', '4621', '4622',
'4623', '4624', '4625', '4626', '4627', '4628', '4629', '4630', '4631',
'4632', '4633', '4634', '4635', '4636', '4637', '4638', '4639', '4640',
'4641', '4642', '4643', '4644', '4645', '4646', '4647');
When I try that in 9.1, I get a plan with inner indexscans for each
child table; which, while not exactly what you're asking for, should
perform well enough when the fact table is large enough that
partitioning is actually a useful activity.
I do not have 9.1 handy but this is from 9.0.4:
db=# EXPLAIN select dates.datetime, myfact.recorded_on_id from myfact,
dates where myfact.recorded_on_id = dates.recorded_on_id and
dates.recorded_on_id in (1813, 1814);
QUERY PLAN
Hash Join (cost=33.46..86124.15 rows=2858831 width=16)
Hash Cond: (public.myfact.recorded_on_id = public.dates.recorded_on_id)
- Append (cost=0.00..46245.73 rows=3001773 width=8)
- Seq Scan on myfact (cost=0.00..27.70 rows=1770 width=8)
- Seq Scan on myfact_y2004w51 myfact (cost=0.00..15406.01
rows=101 width=8)
- Seq Scan on myfact_y2004w52 myfact (cost=0.00..15406.01
rows=101 width=8)
- Seq Scan on myfact_y2004w53 myfact (cost=0.00..15406.01
rows=101 width=8)
- Hash (cost=33.21..33.21 rows=20 width=16)
- Append (cost=0.00..33.21 rows=20 width=16)
- Seq Scan on dates (cost=0.00..32.12 rows=18 width=16)
Filter: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
- Seq Scan on dates_y2004w51 dates (cost=0.00..1.09
rows=2 width=16)
Filter: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
(13 rows)
Postgres can't infer that myfact.recorded_on_id is in (1813, 1814)
from the join condition (myfact.recorded_on_id = dates.recorded_on_id)
hence all partitons are included (myfact_y2004w51, myfact_y2004w53,
myfact_y2004w53). Adding myfact.recorded_on_id in (1813, 1814)
creates much better plan:
db=# EXPLAIN select dates.datetime, myfact.recorded_on_id from myfact,
dates where myfact.recorded_on_id = dates.recorded_on_id and
dates.recorded_on_id in (1813, 1814) and myfact.recorded_on_id in
(1813, 1814);
QUERY PLAN
-
Hash Join (cost=33.46..18878.72 rows=296673 width=16)
Hash Cond: (public.myfact.recorded_on_id = public.dates.recorded_on_id)
- Append (cost=0.00..14710.38 rows=311507 width=8)
- Seq Scan on myfact (cost=0.00..32.12 rows=18 width=8)
Filter: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
- Bitmap Heap Scan on myfact_y2004w51 myfact
(cost=5378.64..14678.25 rows=311489 width=8)
Recheck Cond: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
- Bitmap Index Scan on myfact_y2004w51_recorded_on_id
(cost=0.00..5300.77 rows=311489 width=0)
Index Cond: (recorded_on_id = ANY
('{1813,1814}'::bigint[]))
- Hash (cost=33.21..33.21 rows=20 width=16)
- Append (cost=0.00..33.21 rows=20 width=16)
- Seq Scan on dates (cost=0.00..32.12 rows=18 width=16)
Filter: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
- Seq Scan on dates_y2004w51 dates (cost=0.00..1.09
rows=2 width=16)
Filter: (recorded_on_id = ANY ('{1813,1814}'::bigint[]))
(15 rows)
--
create table myfact (
id bigint not null,
recorded_on_id bigint not null
);
create table myfact_y2004w51 (
check (recorded_on_id = 1812 and recorded_on_id = 1818),
primary key (id)
) inherits (myfact);
create table myfact_y2004w52 (
check (recorded_on_id = 1819 and recorded_on_id = 1825),
primary key (id)
) inherits (myfact);
create table myfact_y2004w53 (
check (recorded_on_id = 1826 and recorded_on_id = 1832),
primary key (id)
) inherits (myfact);
create table dates (
datetime timestamp without time zone not null,
recorded_on_id bigint not null
);
create table dates_y2004w51 (
check (recorded_on_id = 1812 and recorded_on_id = 1818),
primary key (datetime)
) inherits (dates);
create table dates_y2004w52 (
check (recorded_on_id = 1819 and recorded_on_id = 1825),
primary key (datetime)
) inherits (dates);
create table dates_y2004w53 (
check (recorded_on_id = 1826
Re: [GENERAL] PostgreSQL, OLAP, and Large Clusters
Hi, On 28 September 2012 04:34, Ryan Kelly rpkell...@gmail.com wrote: On Thu, Sep 27, 2012 at 08:51:31AM +1000, Ondrej Ivanič wrote: - aggregation job ran every 15 minutes and completed under 2 minutes: 5mil rows - aggregation - 56 tables 5mil overall, or matching your aggregation query? And is that the 2TB mentioned above? We have more than 100 times that many rows, but less data. Let me explain. ETL process imports several thousands row every 5 minutes or so. Aggregation job runs every 15 minutes and it grabs the everything new since last run which could be up to 5 mil rows. Next step is to compute aggregates -- 56 queries like insert into mat_table1 select attr1, attr2, count(*) from tmp_table; 2TB was the size of the live dataset - 6 months, 30-40mil rows per month. - all queries can be executed over date range up to several months (monthly partitioned tables, 6 months history) Yeah we have to be able to query over various date ranges. Partitioning works nicely in this case. - AsterData: nice SQL-MR feature and analytics (decision trees, frequent items, clustering, ...); No libpq support and you have to use JDBC or selected ODBC manager I don't think no libpq support is a deal-breaker, but other missing features could be problematic. It was for us -- we ended up with one option which was ODBC and unixODBC segfaulted on simple queries like 'select 1::numeric'. Aster removed many PG features (i think arrays and composite types are not supported) but they added several cool things. - Greenplum (winer): performance comparable to FusionIO (10 to 50 times); we were able to remove aggregation job (because of columnar store model); easy to port from postgres but could be complicated if you are heavy pgpsql user Not using any pl/pgpsql, but a number of other features: arrays and hstore, which I doubt (hopefully wrongly) that Greenplum supports. Anything which you can compile against 8.2 might work... At this time I would try: - Postgres-XC From what I understand, more of a write-scaleable-oriented solution. We mostly will need read scalability. I also don't think it really handles redundancy. read scalability is there as well: it can use multiple nodes for select quires and push-down (execute it on node) certain operations. Check this talk: http://www.pgcon.org/2012/schedule/events/424.en.html redundancy is up to you -- you can deploy as many coordinator nodes as you need. Data distribution is quite flexible, see DISTRIBUTE BY and TO GROUP / NODE clauses (http://postgres-xc.sourceforge.net/docs/1_0/sql-createtable.html). - Stado Looks promising, sounded very promising, but it doesn't seem to be particularly active or well-documented. It also doesn't support window functions (which I could probably get by without) or CTEs (which will be trickier, but doable. I'm also not sure of how easy it is to handle node failure or adding more nodes, as it appears the number of nodes is essentially fixed. yup, documentations is not the best. You might have a look at pgpool-II parallel query mode (docs is skimpy, not sure about window functions and CTEs support) http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html PS. For any commercial solution is better to buy their appliance (if you can afford it...). Thanks for the advice. Is it just better supported, or more performant, or...? Usually both. You get support, monitoring, performance. Some of appliances do dial-home calls hence you get support call back with concrete advice / solution. Hardware is fine-tuned and proven: good RAIDs controller, disks, 10GbE interconnects, redundant network / storage paths. You can build something like that by your self but you are not going to save in the long run. -- Ondrej Ivanic (ondrej.iva...@gmail.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] PostgreSQL, OLAP, and Large Clusters
Hi, On 26 September 2012 21:50, Ryan Kelly rpkell...@gmail.com wrote: The size of our database is growing rather rapidly. We're concerned about how well Postgres will scale for OLAP-style queries over terabytes of data. Googling around doesn't yield great results for vanilla Postgres in this application, but generally links to other software like Greenplum, Netezza, and Aster Data (some of which are based off of Postgres). Too, there are solutions like Stado. But I'm concerned about the amount of effort to use such solutions and what we would have to give up feature-wise. We were able to have 2TB of data (16 Cores, 96GB RAM, FC connected SAN). Performance was good: - up to 5sec for simple select with multiple where conditions (2 - 25) order by any column (of 400 columns) and return top 10k - up to 15sec for executing 5 queries (simultaneously) which return top 20 combination of any two columns - up to 25sec for executing 56 queries (using materialised aggregate tables) - aggregation job ran every 15 minutes and completed under 2 minutes: 5mil rows - aggregation - 56 tables - all queries can be executed over date range up to several months (monthly partitioned tables, 6 months history) but it was very hard to scale this solution. We have tried: - FusionIO cards: 10 to 100 times better performance, but very hard to expand storage capacity; Cooling/power issues - AsterData: nice SQL-MR feature and analytics (decision trees, frequent items, clustering, ...); No libpq support and you have to use JDBC or selected ODBC manager - Greenplum (winer): performance comparable to FusionIO (10 to 50 times); we were able to remove aggregation job (because of columnar store model); easy to port from postgres but could be complicated if you are heavy pgpsql user At this time I would try: - Postgres-XC - Stado - Cassandra + Solr + Hadoop (for example DataStax Enterprise) - Birst (http://www.birst.com/) PS. For any commercial solution is better to buy their appliance (if you can afford it...). -- Ondrej Ivanic (ondrej.iva...@gmail.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] should I increase default_statistics_target
Hi, On 20 September 2012 20:49, AI Rumman rumman...@gmail.com wrote: Using explain analyze of a large query I found that in every step there are a lot difference between the number of rows between actual and estimated. I am using default_statistics_target 200. Should I increase it? I would keep it at default level but I would increase it per column: ALTER TABLE table ALTER column SET STATISTICS number and you can do the same for index: ALTER TABLE index_name ALTER COLUMN column SET STATISTICS number (for function indexes you need to use \d in order to see real column name) Finally, you need to run analyse on that table / column -- Ondrej Ivanic (ondrej.iva...@gmail.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] CentOS initd Script
Hi,
On 12 September 2012 16:41, Kenaniah Cerny kenan...@gmail.com wrote:
In the service script that gets installed to /etc/rc.d/init.d/, there is a
hard-coded value for PGPORT. Would it be possible to have this variable and
the corresponding -p flag set when calling postgres removed?
My init.d script has the following:
# Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/data
PGLOG=/var/lib/pgsql/pgstartup.log
# Override defaults from /etc/sysconfig/pgsql if file is present
[ -f /etc/sysconfig/pgsql/${NAME} ] . /etc/sysconfig/pgsql/${NAME}
In this case you can create as many configs as you need, for example:
/etc/sysconfig/pgsql/postgresql:
PGDATA=/var/lib/pgsql/data
PGPORT=5432
PGLOG=/var/lib/pgsql/pgstartup.log
PGOPTS='--config_file=/etc/postgresql.conf'
/etc/sysconfig/pgsql/postgresql2:
PGDATA=/var/lib/pgsql2/data
PGPORT=54320
PGLOG=/var/lib/pgsql2/pgstartup.log
PGOPTS='--config_file=/etc/postgresql.conf'
and create /etc/init.d/postgresql2 as symlink to /etc/init.d/postgresql
--
Ondrej Ivanic
(ondrej.iva...@gmail.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] Too far out of the mainstream
Hi, On 5 September 2012 12:14, Chris Travers chris.trav...@gmail.com wrote: So people are using PostgreSQL in roles that aren't very visible anyway, DBA's are usually coming to PostgreSQL from other RDBMS's, and few applications are really distributed for PostgreSQL. snip Not only this but there was significant interest in moving more db's to PostgreSQL, but the big limitation is that everyone who knows PostgreSQL already has a job. Some shops are going opposite way -- from PostgreSQL to MySQL like databases because of missing replication features. The 9.1 caught up but there is no multi-master replication like in Percona's XtraDB cluster: http://www.percona.com/software/percona-xtradb-cluster/ Postgres-XC can solve this missing multi-master replication issue but nobody knows that this project exists. Another project is Galera Cluster for PostgreSQL (Galera is used in XtraDB) but this looks like vaporware... -- Ondrej Ivanic (ondrej.iva...@gmail.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] Statistical aggregates with intervals
Hi, On 24 August 2012 07:39, Christopher Swingley cswin...@gmail.com wrote: I don't know why, but you could convert 'interval' into something else where all the functions work: CREATE OR REPLACE FUNCTION interval_to_seconds(interval) RETURNS double precision AS $$ SELECT (extract(days from $1) * 86400) + (extract(hours from $1) * 3600) + (extract(minutes from $1) * 60) + extract(seconds from $1); $$ LANGUAGE SQL; Looks complicated. You can extract 'epoch': db=# select now() - (now() - interval '1 day'); ?column? -- 1 day (1 row) db=# select extract(epoch from (now() - (now() - interval '1 day'))); date_part --- 86400 (1 row) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Some thoughts on table inheritance (which is uniquely awesome on PostgreSQL)
Hi, On 24 August 2012 11:44, Chris Travers chris.trav...@gmail.com wrote: One thing I have found looking through Oracle and DB2 docs is that their table inheritance seems to have all the same problems as ours and their solutions to these problems seem rather broken from a pure relational perspective. I can second that. Additionally, some vendors tried to fix partitioning (which uses table inheritance) issues by creating all sort of extension like CREATE TABLE ... PARTITION BY, and ALTER TABLE ... ALTER PARTITION ... which create all sorts of issues which are not documented at all but you get response like yes, we know about this bug; fix not yet available. Many people asked for SQL wrappers for table partitioning but that's not easy to do. I would be happy to have out of the box auto-routing for insert/update/copy statements -- Ondrej Ivanic (ondrej.iva...@gmail.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] At what point does a big table start becoming too big?
Hi, On 23 August 2012 23:37, Bill Moran wmo...@potentialtech.com wrote: And the advice I have along those lines is to establish now what constitutes unacceptable performance, and put some sort of monitoring and tracking in place to know what your performance degradation looks like and predict when you'll have to react. For example, a MRTG graph that runs an experimental query once a day during off hours and graphs the time it takes vs. the # of rows in the table will prove a valuable tool that can sometimes predict exactly when you'll have to change things before it becomes a problem. Other tricks work as well, such as having the application send an email any time a process takes more than 50% of the allowable maximum time. I like to use APDEX (http://apdex.org/specs.html). You can change your database to all time for all statements and then calculate APDEX score based on last N log entries ( 10). APDEX score is weighted score based on number of datapoints within three zones: 0...T Satisfied Zone T..F (=4*T) Tolerating Zone 4T... Frustrated Zone you can choose T (or F; then T = F/4) i.e. under normal circumstances all queries should finish under 20ms (T = 20ms, F = 4T = 80ms). Apdex score is: score = (Satisfied count + Tolerating count / 2) / Total samples You can get this number, for example, every minute and plot it using Ganglia / MRTG / ... -- Ondrej Ivanic (ondrej.iva...@gmail.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] redundant fields in table for performance optimizations
Hi, On 22 August 2012 07:07, Menelaos PerdikeasSemantix mperdikeas.seman...@gmail.com wrote: Let's say you have a father-child (or master-detail if you wish) hierarchy of tables of not just 2 levels, but, say, 5 levels. E.g. tables A, B, C, D and E organized in successive 1-to-N relationships: A 1-to-N- B B 1-to-N- C C 1-to-N- D D 1-to-N- E with appropriate foreign keys: * from E to D * from D to C * from C to B * from B to A This is normalized so far. Now assume that it is the case than in some queries on table E you also need to report a field that only exists on table A. This will mean a JOIN between five tables: E, D, C, B and A. Some questions follow: [1] assuming tables having a number of rows in the order of 100,000, after how many levels of depth would you feel justified to depart from the normalized schema and introduce some redundancy to speed up the queries? [3] do you feel this is a legitimate concern in a modern PostgreSQL database running on high end (200,000 USD) hardware and serving no more than 1000 concurrent users with table sizes at the lowest (more detailed) level of the hierarchy in the order of a few tens of millions of rows at the most and dropping by a factor of 20 for each level up ? I would ask different question(s): how static that tree structure is and what kind of queries do you want to run: - father-child: easy to understand; add new node; change leaf node; hard to run some count(*) queries; and get hierarchy (CTEs are help full) - nested sets: pailful to move nodes around (even add new node); easy to get tree subsets; ... Anyway, I've found this summary: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database when I was googling for Joe Celko's Trees and Hierarchies book. -- Ondrej Ivanic (ondrej.iva...@gmail.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] How hard would a path operator be to implement in PostgreSQL
Hi, On 20 August 2012 11:28, Chris Travers chris.trav...@gmail.com wrote: I have been reading up on object-relational features of Oracle and DB2 and found that one of the big things they have that we don't is a path operator. The idea is that you can use the path operator to follow some subset of foreign keys called refs. snip Any thoughts? If it can be done in plain SQL and inlined that would be ideal but in the prototyping state, that isn't so important and I expect that it is not. You should look at ltree: This module implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided. http://www.postgresql.org/docs/9.1/static/ltree.html -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] count number of concurrent requests
Hi, I have the following table: dwh= \d events Table public.events Column |Type | Modifiers --+-+--- datetime | timestamp without time zone | request_duration | integer | dwh= select count(*) from events; count -- 82912116 (1 row) Each row represents one event which started at 'datetime' and finished at 'datetime + request_duration'. I would like to know how many other events started between 'datetime' and ''datetime + request_duration' (ie. concurrency). I've started with this query (CTE + join): with e as ( select datetime as date_s, datetime + (request_duration::text || ' msec')::interval as date_e, request_duration from events where datetime '2012-08-01 00:01:00' ) select e1.date_s, e1.date_e, count(*) as count, count(case when e1.request_duration 1000 then true else null end) as over 1000 from e as e1 left join e as e2 on (e2.date_s between e1.date_s and e1.date_e) group by e1.date_s, e1.date_e having count(case when e1.request_duration 1000 then true else null end) 0 which is incredibly slow (as expected) and I can not analyse more than several minutes of real traffic. I need to run this query over few days at least. Second try was this one: select date_s, date_e, counts[1] as count, counts[2] as over 1000 from ( select datetime as date_s, datetime + (request_duration::text || ' msec')::interval as date_e, ( select array[ count(*), count(case when ee.request_duration 1000 then true else null end) ] from events ee where ee.datetime '2012-08-01 00:01:00' and ee.datetime = e.datetime and ee.datetime = e.datetime + (e.request_duration::text || ' msec')::interval ) as counts from events as e where datetime '2012-08-01 00:01:00' ) as x where counts[2] 0 Which is much better (like few minutes per day) but I can not use any of those queries because: - events table is partitioned so the first one is not going to work at all and second could be fixed by adding date ranges to select ... from events ee query - and I would like to have one query across two database platform but other one doesn't support correlated subquery Hence I've written simple PHP script which looks like Window Function. I tried to rewrite query using window function(s) but I can't get my head around it. Any ideas? I'm looking for something which is relatively fast and doesn't use correlated subquery... Thanks! -- Ondrej Ivanic (ondrej.iva...@gmail.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] success with postgresql on beaglebone
Hi, On 17 August 2012 07:14, Tomas Hlavaty t...@logand.com wrote: thanks for your reply. I should have mentioned that I was using the Ångström Distribution where postgresql is not provided via package manager. I wonder how did the Ubuntu guys managed to overcome the insufficient memory limitation? You don't have to do all that stuff on beaglebone -- you need to setup toolchain and compiler for target architecture. This is usually distribution specific. (check this for your distribution: http://www.angstrom-distribution.org/toolchains/ ) -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] new material for Postgres for MySQL users presentation
Hi, Several years ago I gave this presentation to a bunch of PHP developers in order to show then that something else is out there :). Presentation was based on MySQL 4.1 and Postgres 8.2/8.3 (early 2008). I would like to do it again and I'm looking for sources which can I re-use (and credit back of course). Presentation has the following structure: - Installation (database server and required PHP modules) - First look around (PGAdmin, psql) - Postgres nomenclature and MySQL equivalents (database cluster, schema, MVCC, fulltext) - Let's create first database and users (psql, pg_hba, createdb, createuser, ...) - Basic performance tuning - Cool things in Postgres (transactional DDL, PostGIS, custom types, ...) - Annoying things (Slow count(*), (auto)vacuum/(auto)analyze) - Replication and HA I need to remove obsolete stuff and add new things like extensions and hstore -- anything else worth mentioning? I would like to talk more about replication and HA because Postgres implemented same set of features like MySQL (but MySQL steps forward with master-master setup (should I count Postgres-XC?)). If you know about some good resources (presentations, blogs, ...) which can I use please let me know! Thanks! -- Ondrej Ivanic (ondrej.iva...@gmail.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] postgres maintenance db
Hi, On 27 July 2012 08:07, hartrc rha...@mt.gov wrote: What is the purpose of the postgres database? I try and drop it and get maintenance database can't be dropped error. 'postgres' database is something like 'mysql' database in MySQL. You should be able to see additional database like 'template0' and 'template1' Should I create a separate database that has all my application schemas in it and let the postgres database be stand-alone, or should I put my application schemas inside the postgres database? You should create your own database (as many as you need) and create all schemas/tables/... there. See http://www.postgresql.org/docs/9.1/static/manage-ag-createdb.html -- you can use CREATE DATABASE or createdb command. I didn't really want my database to be called postgres, can it be renamed? That's the system database let it be. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remove some rows from resultset
Hi, I have the following table: org_id | contract_name | org_specific_rule | count --+--+---+--- smpj28p2 | Group 123| f | 3 smpj28p2 | Group 2 | f | 3 smpj28p2 | Group 2 | t | 9 smpj28p2 | Group 1 | f | 1 w37mtn4r | Group 123| f |26 w37mtn4r | Group 2 | f |56 w37mtn4r | Group 1 | f |55 Based on org_specific_rule and (org_id, contract_name) I need to transform this: smpj28p2 | Group 2 | f | 3 smpj28p2 | Group 2 | t | 9 to smpj28p2 | Group 2 | 9 in other words: - if org_specific_rule = t then update count value in row where org_specific_rule = f to value from this row (3 was updated to 9) - remove org_specific_rule column I have query which does everything but I have mixed feelings about it: select b1.org_id, b1.contract_name, coalesce(b2.count, b1.count) as count from (select * from billing where org_specific_rule = false) as b1 left join billing b2 on b1.org_id = b2.org_id and b1.contract_name = b2.contract_name and b2.org_specific_rule = true order by 1,2; org_id | contract_name | count --+--+-- smpj28p2 | Group 123|3 smpj28p2 | Group 2 |9 smpj28p2 | Group 1 |1 w37mtn4r | Group 123| 26 w37mtn4r | Group 2 | 56 w37mtn4r | Group 1 | 55 Any ideas? -- Ondrej Ivanic (ondrej.iva...@gmail.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] Searchable chess positions in a Postgress DB
Hi,
On 11 April 2012 17:15, Sidney Cadot sid...@jigsaw.nl wrote:
I have written code to extract these positions, and now I want to put
them into a Postgres database. Specifically, I want to do this in a
way that allows *fast* lookups of positions, e.g. give me all
positions that have a White King on c4 and either a Black Bishop or
White Knight on f7.
I would try to use single table with 16 columns like:
white_pawn char(2)[] -- like {'c1', 'd3', ... }, max 8 elements
white_rook char(2)[] -- max 2 elements
white_bishop char(2)[] -- max 2 elements
white_knight char(2)[] -- max 2 elements
white_queen char(2)
white_king char(2)
black_pawn_1 char(2)[]
...
black_king char(2)
and each column; char(2) and char(2)[] should have btree and GiST
index respectively. The query should looks like this:
select * from positions where white_king = 'c4' and (white_bishop
ARRAY['f7'] or white_knight ARRAY['f7'])
Another alternative might be to use hstore (and GiST index):
http://www.postgresql.org/docs/9.1/static/hstore.html
--
Ondrej Ivanic
(ondrej.iva...@gmail.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] Versioned, chunked documents
Hi, On 2 April 2012 08:38, Ivan Voras ivo...@freebsd.org wrote: db= set enable_seqscan to off; snip This huge cost of 100 which appeared out of nowhere in the EXPLAIN output and the seq scan worry me - where did that come from? It is not possible to disable seq scan completely. The enable_seqscan to off just sets cost of this operation extremely high (10 000 000 000) thus planner is forced look for better plan. In your case planner wasn't able to find anything better hence cost=109.55..109.56. -- Ondrej Ivanic (ondrej.iva...@gmail.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] PostgreSQL 64 Bit XIDs - Transaction IDs
Hi, On 23 March 2012 19:14, Frank Lanitz fr...@frank.uvena.de wrote: Am 23.03.2012 06:45, schrieb Gerhard Wiesinger: With a database admin of a commercial database system I've discussed that they have to provide and they also achieve 2^31 transactions per SECOND! Just corious: What is causing this many transactions? I would be interested to know hardware configuration and name of that mysterious commercial database system! -- Ondrej Ivanic (ondrej.iva...@gmail.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] COPY and indices?
Hi, On 13 March 2012 15:11, François Beausoleil franc...@teksol.info wrote: When using COPY FROM STDIN to stream thousands of rows (20k and more hourly), what happens with indices? Are they updated only once after the operation, or are they updated once per row? Note that I'm not replacing the table's data: I'm appending to what's already there. I suspect batching writes will be faster than writing each individual row using an INSERT statement. Yes, it will be faster to use COPY than plain INSERTs. We have similar situation -- up to 10k rows every 3 minutes and around 15 indexes on the table. Table is portioned and we do not update data. Check bloat query reports some bloat but it growing very slowly and there is new partition every month. Currently, I can sustain 30-40 writes per second on a Rackspace VPS. I know it's not the ideal solution, but that's what I'm working with. Following vmstat, the server is spending 30 to 40% of it's time in iowait. I don't have measurements as to what files are touched, and I'd welcome suggestions to measure the time PostgreSQL actually spends writing indices vs data. Drop all indexes, measure time to insert and collect iostat output. Create indexes, repeat the process and compare the results -- Ondrej Ivanic (ondrej.iva...@gmail.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] Synchronous replication + Fusion-io = waste of money OR significant performance boost? (compared to normal SATA-based SSD-disks)?
Hi, On 9 March 2012 02:23, dennis jenkins dennis.jenkins...@gmail.com wrote: I've also looked at the Fusion-IO products. They are not standard flash drives. They don't appear as SATA devices. They contains an FPGA that maps the flash directly to the PCI bus. The kernel-mode drivers blits data to/from them via DMA, not a SATA or SAS drive (that would limit transfer rates to 6Gb/s). But, I don't have any in-hand to test with yet... :( But the kool-aide looks tasty :) I think they are good investment but we wasn't able to use them because: - iodrive was small (1.2TB only) and not very scalable in long term -- not enough PCIE slots - iodrive duo/octal needs more power and cooling than we had You can workaround both by upgrading server (two of them, HA) but you just delay the situation when you can't insert new card (no slots or power available). Performance wise, we were able to reduce query time - from few seconds to instant (500ms and better) - any query exceeding 300sec (apache timeout) finished under minute -- Ondrej Ivanic (ondrej.iva...@gmail.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] how to return the last inserted identity column value
Hi,
On 9 March 2012 05:20, Scott Marlowe scott.marl...@gmail.com wrote:
On Thu, Mar 8, 2012 at 11:16 AM, mgo...@isstrucksoftware.net wrote:
In some languges you can use set l_localid = @@identity which returns
the value of the identity column defined in the table. How can I do
this in Postgres 9.1
Assuming you created a table like so:
smarlowe=# create table test (id serial,info text);
NOTICE: CREATE TABLE will create implicit sequence test_id_seq for
serial column test.id
CREATE TABLE
Then use returning:
smarlowe=# insert into test (info) values ('this is a test') returning id;
You can use lastval() or currval() functions:
http://www.postgresql.org/docs/9.1/static/functions-sequence.html
--
Ondrej Ivanic
(ondrej.iva...@gmail.com)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] replication between US - EU
Hi, I would like to get some ideas about subject. I do not have any preferred solution (hot-standby, Slony or pgpoll) so anything which can deliver/satisfy the following will good: - one side completely down: Client should use switch to other side transparently (Failover / High Availability) - database writes: transparent for application ie. connection pooler/... should redirect writes to master (it would be nice to have writes on both sides and everything in sync) and use local database for reads (database holds metadata/configuration; just few writes) - replication lag: Clients shouldn't use stalled data. Switch to master or stop and wait are preferred actions. - automatic failover w/o DBA intervention (or minimal intervention) The other things to consider are: - average latency could be up to 400ms. There is no problem to buy connectivity from different provider if we can use simpler/more robust setup. But I don't know which provider can deliver low latency link suitable for this purpose. - one second lag between master and slave would be tolerable but if we can go lower that would be nice (low latency link, configuration, ...) but we need robustness - kernel or tcp/ip tweaks? We use CentOS 5.7 - database is small; around 1.5GB. It doubles in size every 6..9 months. - NetScaler like appliance could be used Thanks! -- Ondrej Ivanic (ondrej.iva...@gmail.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] replication between US - EU
Hi, On 7 March 2012 10:36, John R Pierce pie...@hogranch.com wrote: On 03/06/12 3:31 PM, Ondrej Ivanič wrote: - one side completely down: Client should use switch to other side transparently (Failover / High Availability) what happens if the link between the sites is down and both sides decide they are master? then how do you put the pieces back together ? Good catch! From application point of it is quite easy to sync data between sites. Let's assume that writes should be disabled in this scenario. -- Ondrej Ivanic (ondrej.iva...@gmail.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] canceling autovacuum time
Hi, On 28 February 2012 11:53, Jameison Martin jameis...@yahoo.com wrote: I'm seeing GMTERROR: canceling autovacuum task lines in my logs. That's *should* be fine. autovacuum daemon is smart enough to cancel it self when other query needs access to the table. The affected table will be vacuumed/analysed later. You should monitor pg_stat_user_tables view to see how often is the table vacuumed/analysed. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Rules of Thumb for Autovaccum
Hi, On 16 February 2012 01:14, Robert James srobertja...@gmail.com wrote: What rules of thumb exist for: * How often a table needs to be vacuumed? * How often a table needs to be analyzed? * How to tune Autovacuum? I prefer to use autovacuum daemon and sets thresholds on per table basis i.e. sets reasonable defaults and then add few exceptions. I keep *_threshold as is and change *_scale_factor and turn off cost based vacuum/analyse (see other note about this). My lowest scale_factor is 0.002 ie. 0.2% of table has to change (which corresponds to ~8mil rows) to trigger analyse/vacuum. autovacuum/analyse can produce significant I/O so you have two options: - tune cost based settings in order to limit I/O used by this porocess - turn off autovacuum daemon and schedule manual acuum/analyse in quiet period -- Ondrej Ivanic (ondrej.iva...@gmail.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] Puzzling full database lock
Hi, On 2 February 2012 11:38, Christopher Opena counterv...@gmail.com wrote: We've been running into some very strange issues of late with our PostgreSQL database(s). We have an issue where a couple of queries push high CPU on a few of our processors and the entire database locks (reads, writes, console cannot be achieved unless the high CPU query procs are killed). Further investigation shows ~59% total cpu usage (we have 16 total cores), low io, and mid-to-low memory usage (we have 74GB of memory, shared_buffers=16GB). I think 16GB is too much. We started with 9GB (16 cores, 80GB RAM, SAN) and then experimented with lower value (6GB) but never used in the production because we switched to different database / storage technology. Anyway, Overal CPU utilisation was lower using 6GB. If CPU util is high because of io waits then it might be worth to play with dirty_background_ratio and dirty_ratio. The problem is that the value is percentage and you have 74GB. CentOS has 10% and 40% as default value for dirty_background_ratio and dirty_ratio respectively. 10% of 74GB is 7.4GB and there is no storage controller with 7.4GB of cache so you get IO waits (and high load). So writes will backup until you hit 40% hard limit (vm.dirty_ratio) which is even worse (~30GB to flush). I think you should try lower both. For example, try 1 and 10 for vm.dirty_background_ratio and vm.dirty_ratio respectively. One thing that we aren't sure of is whether or not we are running into a general connection pooling issue. Our typical number of postgresql processes fluctuates between 1,400 and 1,600 - most of which are idle - as we have a number of application servers all connecting to a central read/write master (the master replicates out to a secondary via streaming replication). We have max_processes set to 3,000 after tweaking some kernel memory parameters so at least we know we aren't exceeding that, but is there a practical real world limit or issue with setting this too high? I would use connection pooler like PG-Pool II. It can add transparent failover and you don't need max_processes set so high (plus parallel query feature could be useful). -- Ondrej Ivanic (ondrej.iva...@gmail.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] Interval ordering
Hi, On 30 January 2012 09:19, Adam Rich ada...@sbcglobal.net wrote: desired time, I want to show them the 5 times from the table that are closest to their input. I expected to do this using abs() like such: select mytime from mytable order by abs(usertime-mytime) asc limit 5; However, the difference between times is an interval, and there appears to be no absolute value operator for those. My next thought was to convert the interval try this: select mytime from mytable order by abs(extract(epoch from (usertime-mytime))) asc limit 5; -- Ondrej Ivanic (ondrej.iva...@gmail.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] self referencing table.
Hi, On 18 January 2012 11:31, David Salisbury salisb...@globe.gov wrote: I've got a table: Taxa Column |Type +- id | integer | parent_id | integer | taxonomic_rank | character varying(32) | latin_name | character varying(32) It's basically a self referential table, with values in the taxonomic_rank like You should check Joe Celko's book: Trees and hierarchies in SQL for smarties It has many good ideas about storing and accessing tree-like structures in relational databases. (just google for chapter names :)). I have this link in my bookmarks but it doesn't work anymore: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html -- quite good article about nested sets For example in nested sets model finding the path is simple query like this: SELECT taxonomic_rank FROM Taxa WHERE lft $left AND rgt $right ORDER BY lft ASC; where $left, $right are lft and rgt values from required taxa.id -- Ondrej Ivanic (ondrej.iva...@gmail.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] Side effects of moving an index to a new tablespace
Hi, On 10 January 2012 09:16, Jason Buberel ja...@altosresearch.com wrote: We have lots of them, they are much smaller than the tables, and that will allow us to do the migrations more incrementally. In your case I would keep data and indexes on different table spaces (and lower random_page_cost). One area where the documentation is not very detailed - What are the side effects and/or constraints put in place while an index is being moved? I assume that the index will not be available to the query planner/engine during that time period. Are there other ways in which the table (and other indices) are affected or locked? yes, you are right there is not too much about alter index locking in the docs. When I did this last time (PG 8.4) 'alter index' acquired 'ACCESS EXCLUSIVE' lock. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Time to move table to new tablespace
Hi, On 10 January 2012 06:10, Jason Buberel ja...@altosresearch.com wrote: Select median price for every zip code as of 2012-01-06 (customer exports) Select median price for 94086 from 2005-01-01 through 2012-01-06 (charting apps) So by partitioning in one dimension we impact queries in the other. I do not see any issue here; if all your queries contain date range then you should partition by date. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Time to move table to new tablespace
Hi, On 8 January 2012 01:52, Jason Buberel ja...@altosresearch.com wrote: psql create tablespace 'newstorage' location '/some/new/path'; psql alter table city_summary set tablespace = 'newstorage'; Be aware that you are not going to move indexes (see ALTER INDEX name SET TABLESPACE tablespace_name). Maybe you don't have to move data and I would be worth to move indexes only. So you ended up with data and index tablespace and reduce random_page_cost when you query your table. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Single Table Select With Aggregate Function
Hi, On 4 January 2012 10:26, Rich Shepard rshep...@appl-ecosys.com wrote: select max(quant), site, sampdate from chemistry where stream = 'SheepCrk' and param = 'TDS' group by site, sampdate; but this gives me the value of each site and date, not the maximum for all dates at a specific site. Postgres tells me that both site and sampdate must be in the 'group by' clause. Obviously the syntax is more complex than I thought it would be and I would like a pointer to the correct way to write these queries. window functions might be helpful: http://www.postgresql.org/docs/9.1/static/tutorial-window.html p4= select * from public.chemistry order by sampdate, site, quant desc; quant | site | sampdate ---+--+ 100 | 1| 2012-01-03 80 | 1| 2012-01-03 30 | 1| 2012-01-03 400 | 2| 2012-01-03 100 | 2| 2012-01-03 30 | 2| 2012-01-03 100 | 3| 2012-01-03 80 | 3| 2012-01-03 30 | 3| 2012-01-03 80 | 4| 2012-01-03 (10 rows) p4= select distinct first_value(quant) over (partition by sampdate, site order by quant desc), sampdate, site from public.chemistry order by site; first_value | sampdate | site -++-- 100 | 2012-01-03 | 1 400 | 2012-01-03 | 2 100 | 2012-01-03 | 3 80 | 2012-01-03 | 4 (4 rows) -- Ondrej -- 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] Verifying a timestamp is null or in the past
Hi, On 2 January 2012 03:26, Raymond O'Donnell r...@iol.ie wrote: And also - does PERFORM works with FOUND? Not sure what you mean - can you elaborate? No, perform (and execute) doesn't populate 'found' variable: http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS You have to use something like this: get diagnostics rr = row_count; -- Ondrej Ivanic (ondrej.iva...@gmail.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] Verifying a timestamp is null or in the past
Hi 2012/1/3 David Johnston pol...@yahoo.com: On Jan 2, 2012, at 16:46, Ondrej Ivanič ondrej.iva...@gmail.com wrote: Yes, PERFORM does populate FOUND. From the documentation you just linked to A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced. Bummer! Thanks for the correction! I shouldn't (blindly) rely on my own comments in the code :) Pgpsql code uses execute which is the reason for 'get diagnostics'... -- Ondrej Ivanic (ondrej.iva...@gmail.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] checking for table bloat
Hi As I'm sure many people know, check_postgres.pl has a wonderful (if rather arcane) query to check table bloat, which has been copied all over the intarwebz. When I try to use this query one one of my databases I'm told my table (which has had no deletes) is wasting a whole lot of bytes, but no amount of vacuuming or even clustering will make it less bloated. You can use CTAS and re-create that table. The main issue with check bloat query is that you can't relay on absolute numbers. You should look the difference over the time (ie. run it once a day) and compare that number to database activity (pg_stat_all_tables - n_tup_* columns)) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Hope for a new PostgreSQL era?
Hi, On 12 December 2011 15:39, Jayadevan M jayadevan.maym...@ibsplc.com wrote: At the db level, Oracle provides Database replay feature. that lets you replay the production server events in the development/test environment. http://docs.oracle.com/cd/B28359_01/server.111/e12253/dbr_intro.htm Won't something like this be useful in PostgreSQL? It will let us mimic the production environment load and analyze it better. There is project called pgreplay (http://pgreplay.projects.postgresql.org/): pgreplay reads a PostgreSQL log file (not a WAL file), extracts the SQL statements and executes them in the same order and relative time against a PostgreSQL database cluster. The idea is to replay a real-world database workload as exactly as possible. -- Ondrej Ivanic (ondrej.iva...@gmail.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] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time
Hi, On 23 November 2011 13:20, Lonni J Friedman netll...@gmail.com wrote: I investigated, and found that for the past ~18 hours, there's one autovacuum process that has been running, and not making any obvious progress: snip... I'm using the defaults for all the *vacuum* options in postgresql.conf, except for: log_autovacuum_min_duration = 2500 Defaults are: autovacuum_vacuum_cost_delay = 20 msec autovacuum_vacuum_cost_limit = -1 (ie vacuum_cost_limit is used) vacuum_cost_limit = 200 If table is busy -- many updates and deletes then auto vacuum exhausts cost limit almost immediately. You can try to set autovacuum_vacuum_cost_delay to -1 (which disables cost based auto vacuum) but you don't want to saturate your disks. Other option is to increase vacuum_cost_limit/autovacuum_vacuum_cost_limit -- Ondrej Ivanic (ondrej.iva...@gmail.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] Exporting 1 text column from all rows from a table to a file per row?
Hi, On 22 November 2011 06:10, Joost Kraaijeveld j.kraaijev...@askesis.nl wrote: Is it possible, and if so how, to export a single column of a table into a separate file per row? I have a table with ~21000 rows that have a column body1 containing ASCII text and I want to have 21000 separate ASCII files, each containing that column body1. The name of the file does not matter, although it would be nice if they had the extension txt. Something like this could do the job: for i in `psql -a -t -c 'select id from T'`; do psql -a -t -c select body1 from T where id = $i -o $i.txt; done; where 'T' is your table and 'id' is primary column in that table. I've omitted connection options (-h -U ...) from psql commands. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Table Design question for gurus (without going to NoSQL)...
Hi, On 21 November 2011 00:33, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi. Want to start another thread, loosely related to the performance problems thread I have going. Need some DB design guidance from the gurus here. My big table now has about 70 million rows, with the following columns: You can put different tables/indexes on different disks using tablespaces. For example, one tablespace for all tables and another one for all indexes (and change random_page_cost and seq_page_cost appropriately ie. lower random_page_cost). It is a good idea to put pg_xlog on the separate drive too. Sometimes Postgres just can't utilise all available resources properly and you can get results faster by running query over multiple connections. It could be worth to investigate pg-poolII's parallel query mode. You don't need multiple servers - just setup multiple PG instances on the same physical machine (up to one PG instance per core, with lower shared_mem, ...). Alternative could be parallel DB like Postgres-XC (http://wiki.postgresql.org/wiki/Postgres-XC), ... -- Ondrej Ivanic (ondrej.iva...@gmail.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] Restore db
Hi, On 14 November 2011 11:09, Alexander Burbello burbe...@yahoo.com.br wrote: What can I do to tune this database to speed up this restore?? My current db parameters are: shared_buffers = 256MB maintenance_work_mem = 32MB You should increase maintenance_work_mem as much as you can. full_page_writes, archive_mode and auto_vacuum should be disable during restore. Increase checkpoint_segments (for example to 64) and set wal_buffers to 16MB. 8.4 introduced parallel restore (pg_restore option -j num jobs). Maybe you can't do anything mentioned above because it is not possible to restart server (you can change maintenance_work_mem via PGOPTIONS) or there is a single table to import (-j is not aplicable) -- try to drop indexes and recreate them after import. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Learning to rephrase equivalent queries?
Hi, On 11 November 2011 00:04, Jay Levitt jay.lev...@gmail.com wrote: Sometimes the planner can't find the most efficient way to execute your query. Thanks to relational algebra, there may be other, logically equivalent queries that it DOES know how to optimize. But I don't know relational algebra. yet. (Date/Codd is a sleeping pill.) I need more experience first. Are there blogs, guides, rules of thumb, common refactoring patterns out there somewhere? I'm looking for a list of basic equalities, the SQL equivalent of: Have a look here: http://en.wikipedia.org/wiki/Relational_algebra plus External links section -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, mostly heavy read workloads but OLTP performance is required (like run query over 100m+ dataset in 15 sec) that isn't OLTP, its OLAP. Online Analytic Processing rather than Online Transaction Processing large complex reporting queries that have to aggregate many rows is classic OLAP. I didn't say OLTP. Our workload is aggregations/drill downs/roll ups/... (= OLAP) but we need OLTP like performance i.e. our users are not keen to wait more than several seconds for the result. Greenplum or Postgres + Fusion IO can deliver this performance for us. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, 2011/11/8 Craig Ringer ring...@ringerc.id.au: Spreads reads too much ? Are you saying there's too much random I/O? Is it possible it'd benefit from a column store? When you're using Greenplum are you using Polymorphic Data Storage column storage WITH (orientation=column) ? yes, exactly. Column store and compression speed up queries even more (sometimes beyond 100x times) comparing to postgres. Or is the performance different just in better utilisation of the hardware under Greenplum? Yes, looks like that they can better utilise available hardware. Is there a way to get PG backed IO stats using stock CentOS (5.7) kernel and tools? (I can't change my env easily) Dunno; check postgresql high performance (book), the manual, etc. Useful tools are the pg_stat_ tables, vmstat, iostat, iotop, etc. Yeah, I know about those.. I like iotop but enterprise distributions do not ship fresh kernels... I need something which can I safely (slightly worse performance is acceptable but machine must survie) run in production for several hours and then cross reference it with postgres and other system logs That said, Pg's codebase isn't exactly trivial :S and trying to get involved in major re-engineering like parallelisation isn't going to be practical when you're just getting started. That's what I meant ;) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, 2011/11/8 Tomas Vondra t...@fuzzy.cz: Sure you did - you've stated that mostly heavy read workloads but OLTP performance is required (like run query over 100m+ dataset in 15 sec). That clearly mentions OLTP ... Whatever :) Let's make it clear: I need to run aggregates/roll ups/drill downs on large dataset (100m+) but the query should return result (less than 1000 rows, mostly around 100) under 30 sec My point is that MPP (same box used) solution can deliver required performance in most cases (75%). -- Ondrej Ivanic (ondrej.iva...@gmail.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] IP range in pg_hba.conf?
Hi, On 8 November 2011 22:33, Chrishelring christianhelr...@gmail.com wrote: I want to exclude access to our postgresql db using a configuration in the pg_hba.conf file. I have a range of IP adress that should have access, but how do I do that? The range is 10.17.64.1 - 10.17.79.254 (eg. 255.255.240.0 as subnet). The range above should be written like this: 10.17.64.0/20 -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, On 9 November 2011 04:53, John R Pierce pie...@hogranch.com wrote: On 11/08/11 1:49 AM, Ondrej Ivanič wrote: Greenplum or Postgres + Fusion IO can deliver this performance for us. then, thats your answer! it ain't free, oh well. FusionIO is little bit problematic: smaller card (2.4TB) has serious scalability issues (not card but PG and card); bigger one can't fit our server due to thermal restrictions. Anyway, both cards just do scale. Geenplum DCA is on site already. What I see is that single node Greenplum can utilise available resources more efficiently... -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, it's a lot of work and right now the only people who've done that work aren't giving it away for free - or not in any form that can be integrated into PostgreSQL without removing other capabilities other users need. One MPP vendor implemented columnar store in roughly six months -- lot's of work is involved there!. Anyway, all implementation what I came across took several shortcuts like no updates(append only) or no foreign keys, ... but it works! That's not to say Pg can't improve. It can, and not just by adding column store or index-structured table support. Improved parallelism capabilities are needed in Pg I see most benefits coming from parallelism: 12hr query can finish in 2hr if sliced properly -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres vs other Postgres based MPP implementations
Hi, I have simple question (I think which is not easy to answer): why Postgres is so slow comparing to other Postgres based MPP products (even on the same box in single node configuration)? I'm mot talking about multi node setup; all benchmarks were done on single box (CentOS 5.5, 16 cores, 80GB ram, fast SAS local disks) and Postgres is slower by order of magnitude (10 or sometimes 100 times slower). Secondly, I've run simple selects and aggregations on vertically partitioned star schema and I haven't used features like columnar tables or replicated dimension tables. I believe that my Postgres configuration is close to optimal one. Another interesting experiment was to parallelise query by hand: select sum(count) from T where org_id = ... and date_in_tz = '2011-08-01' and date_in_tz '2011-11-01' The query above was revritten as series of queries like this: create table t00 as select sum(count) from T where (... previous where ...) and date_in_tz = hist[1] date_in_tz = hist[2] create table t01 as select sum(count) from T where (... previous where ...) and date_in_tz = hist[2] date_in_tz = hist[3]... create table t08 as select sum(count) from T where (... previous where ...) and date_in_tz = hist[8] date_in_tz = hist[9] create table t09 as select sum(count) from T where (... previous where ...) and date_in_tz = hist[9] date_in_tz = hist[10] hist is similar to pg_stat.histogram_bounds (A list of values that divide the column's values into groups of approximately equal population). The idea is to slice T table by primary key (date_in_tz) into 10 groups (or N groups) with similar row count and execute those CTAS queries thru multiple connections. Final query just sums: select sum(count) from ( select count from t00 union all ... union all select count from t09 ) as x All above were faster than single query at the begging. Disk activity was lower and spanned over longer timespan for original query comparing to sliced query scenario. Maybe there is no need for quite complex query rewrite and queries can be speed up by async parallel reads... -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations
Hi, On 8 November 2011 16:58, Craig Ringer ring...@ringerc.id.au wrote: Which one(s) are you referring to? In what kind of workloads? Are you talking about Greenplum or similar? Yes, mainly Geenplum and nCluster (AsterData). I haven't played with gridSQL and pgpool-II's parallel query mode too much. Queries are simple aggregations/drill downs/roll ups/... -- mostly heavy read workloads but OLTP performance is required (like run query over 100m+ dataset in 15 sec) Pg isn't very good at parallelism within a single query. It handles lots of small queries concurrently fairly well, but isn't as good at using all the resources of a box on one big query because it can only use one CPU per query and has only a very limited ability to do concurrent I/O on a single query too. Usually CPU is not bottleneck but I it was when I put Pustgres on FusionIO. The problem is that PG spreads reads too much . iostat reports very low drive utilisation and very low queue size. That said, you should be tuning effective_io_concurrency to match your storage; if you're not, then you aren't getting the benefit of the concurrent I/O that PostgreSQL *is* capable of. You'll also need to have tweaked your shared_buffers, work_mem etc appropriately for your query workload. I've played with effective_io_concurrency (went thru entire range: 1, 2, 5, 10, 20, 50, 100, 200, 500, 1000) but nothing improved. Is there a way to get PG backed IO stats using stock CentOS (5.7) kernel and tools? (I can't change my env easily) queries it won't perform all that well without something to try to parallelise the queries outside Pg. yeah, I have one moster query which needs half a day to finish but it finishes in less than two hours on the same hw if is executed in parallel... I'm not at all surprised by that. PostgreSQL couldn't use the full resources of your system when it was expressed as just one query. This is very interesting area to work in but my lack of C/C++ and PG internals puts me out of the game :) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Regarding the shared disk fail over cluster configuration
Hi, On 2 November 2011 02:00, Debasis Mishra debasis1...@gmail.com wrote: Thanks a lot for your replay. I just wanna know whether it is required for me to run initdb or setting the PGDATA environment variable is enough? Master needs to be properly initialised configured - install postgres - run initdb - install your application schema,... - point $PGDATA to SAN volume Secondary: - install Postgres - point $PGDATA to SAN volume (volume is mounted on primary!) During failover clustering software has to: - shutdown primary (usually: STONITH (Shoot The Other Node In The Head) aka fencing) - mount SAN volume on secondary - startup postgres on secondary -- Ondrej Ivanic (ondrej.iva...@gmail.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] Regarding the shared disk fail over cluster configuration
Hi, On 31 October 2011 23:33, Debasis Mishra debasis1...@gmail.com wrote: RHEL HA clustering is configured to have zero downtime. So if primary server is down then HeartBeat will bring secondary server online. By RHEL HA clustering do you mean RedHat cluster suite? RHCS uses SIGTERM and then kill -9 after 30 sec(?) so it could be tricky to have zero downtime 1. Do i need to install Postgres server in both Primary and Secondary Server? We have Postgres installation on both and SAN volume is mounted to primary only (RHCS takes care about it). (I would use hot standby and two data volumes now instead of shared disk) 2. According to the shared disk fail over concept the dbdata directory has to be shared. So is it like DB will be installed in both the server and dbdata directory will be in storage and both the database will be referring to the same data in storage? yes, but you should mount $PGDATA (or RHCS) to active instance only. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Drill-downs and OLAP type data
Hi, On 12 October 2011 14:50, Anthony Presley anth...@resolution.com wrote: After a few weeks of searching around, we're running into dead-ends on the front-end, and the back-end. PG doesn't support OLAP / MDX and the GUI tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, MS SQL Analytics, etc...). What's the PG route here? Are there some secrets / tips / tricks / contrib modules for handling this? Our db has very simple star schema and several materialised tables for aggregates. We need OLAP style queries with OLTP performance :). After several upgrades we ended up with 16 cores, 80GB of RAM and fast SAN but performance wasn't good. Meanwhile we evaluated several NoSQL solutions and several comercial MPP solutions -- and the winner is Greenplum! (you can get CE version for free -- max two processors (but unlimited cores) and almost no HA options). AsterData's nCluster has very nice MapReduce integration but looks quite immature. I would say that roll-ups/drill-downs/cubes sound obsolete to me :) I suggest the following tools (list is not sorted by anything) - RapidMiner (http://rapid-i.com/content/view/181/196/) - Tableau (http://www.tableausoftware.com/public/gallery) - AlpineMiner (http://www.alpinedatalabs.com/, http://alpineminer.org/) - Jasper (http://www.jaspersoft.com/) - Pentaho (http://www.pentaho.com/) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Drill-downs and OLAP type data
Hi, The *problem* with Greenplum is that it's ultra-expensive once you leave the CE version - and you're not supposed to be using the CE version for commercial usage last I read the license. Has that changed? Not sure. I haven't seen something like that in the license. After POC we bought HW and license from EMC The last pricing I saw was around $16k per CPU (it may have been per core?). At that point, you're now in the realm of SQL Server Analysis and DB2 SPSS. Yeah, it is not cheap but it is drop in replacement for Postgres and we can connect to it from PHP without any issues. (PHP PDO + ODBC doesn't work very well) Our problem is pairing up a web based GUI to a database we love using. Doesn't seem possible, because the user-friendly OLAP / data analysis / dashboard tools are all expecting functionality that PG doesn't have. Could you please name few of them? I'm looking for something like Tableau but web based (without .Net) and hackable (our DB is partially encrypted) It sounds like, we're either choosing a different DB to work with the pretty GUI tools, or writing a GUI tool to work with PG. I think you are right here. -- Ondrej Ivanic (ondrej.iva...@gmail.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] how to save primary key constraints
Hi, On 12 October 2011 08:16, J.V. jvsr...@gmail.com wrote: I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. psql -E is your friend here. Then use \d table and you get several internal queries like this: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(queue)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; oid | nspname | relname ---+-+- 26732 | public | queue SELECT conname, conrelid::pg_catalog.regclass, pg_catalog.pg_get_constraintdef(c.oid, true) as condef FROM pg_catalog.pg_constraint c WHERE c.confrelid = '26732' AND c.contype = 'f' ORDER BY 1; conname |conrelid| condef ---++-- T_fkey | T | FOREIGN KEY (queue) REFERENCES queue(id) ... -- Ondrej Ivanic (ondrej.iva...@gmail.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] plpgsql syntax error
Hi, On 10 October 2011 21:35, József Kurucz jozsef.kur...@invitel.hu wrote: ERROR: syntax error at or near $1 LINE 1: create table $1 ( ) ^ QUERY: create table $1 ( ) CONTEXT: SQL statement in PL/PgSQL function check_table near line 22 I think you have to use execute: execute 'create table ' || tblname || ' ()'; http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Ondrej Ivanic (ondrej.iva...@gmail.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] Rules going away
Hi, folks, don't use RULES! use triggers -- and as much as possible, keep triggers simple, short, and to the point (simple validation, custom RI, auditing/logging, etc). I like them :). 'DO INSTEAD' rules are great for partitioning so you can insert (or update) to parent table and 'DO INSTEAD' rule takes care about the rest. -- Ondrej Ivanic (ondrej.iva...@gmail.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] limitby without orderby
Hi, On 22 September 2011 21:32, Rohan Malhotra yourbuddyro...@gmail.com wrote: Hi Gurus, What is difference between select * from items order by random() limit 5; and select * items limit 5; my basic requirement is to get random rows from a table, my where clause This one says: give me first five rows which you have around. Usually rows are from cache and you can get the same result after each execution. On the other hand the first query returns different result set every time. If you know approximate number of rows in items table then you can use this (and avoid sort): select * from items where random() 5.0 / total_rows limit 5 You can replace total_rows by this query: select reltuples from pg_class where relname = 'items' (ie select * from items where random 5.0 / (select reltuples from pg_class where relname = 'items') limit 5) -- Ondrej Ivanic (ondrej.iva...@gmail.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] COLUMNAR postgreSQL ?
Hi, On 20 September 2011 18:16, Simon Riggs si...@2ndquadrant.com wrote: It would be useful to get some balanced viewpoints on this. I see you have Alterian experience, so if you are using both it could be valuable info. I've never heard anyone describe the downsides of columnar datastores, presumably there are some? Inserts are slower. I haven't done proper benchmark because there is no need for thousands inserts per sec in our database. My understanding is that columnar will work well for queries like this SELECT count(*) FROM table WHERE col1 AND col2 AND col3 but less well when we include the columns in the SELECT clause. Columnar store is good if: - you are selecting less than 60% of the total row size (our table has 400 cols and usual query needs 5 - 10 cols) - aggregates: count(*), avg(), ... In some cases columnar store is able to beat Postgres + High IOPS (250k+) SSD card Would you be able to give some viewpoints and measurements on that? Check this: http://www.greenplum.com/community/forums/showthread.php?499-enable_mergejoin-and-random_page_costp=1553#post1553 -- Ondrej Ivanic (ondrej.iva...@gmail.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] COLUMNAR postgreSQL ?
Hi, 2011/9/21 Tomas Vondra t...@fuzzy.cz: Columnar store is good if: - you are selecting less than 60% of the total row size (our table has 400 cols and usual query needs 5 - 10 cols) - aggregates: count(*), avg(), ... Where did those numbers come from? What columnar database are you using? What options were used (e.g. compression)? Aster's nCluster and Greenplum with no and maximum compression (there was no difference between compression level 5 and 9 but hoge difference between compression level 0 and 9) and partitioned. In some cases columnar store is able to beat Postgres + High IOPS (250k+) SSD card What do you mean by in some cases? If that means a DWH/DSS workloads, then it's apples to oranges I guess. SSDs are great for OLTP workloads (with a lot of random I/O). With DWH/DSS workloads, the performance gain is much smaller (not worth the money in some cases). Yes, our DB is hybrid: we need OLAP solution with OLTP performance. Schema si very simple star schema and is multitenant. So random io to seq io is 80% : 20% but most of the queries are simple aggregates and select queries (drill downs, dicing, slicing, summaries, and queries generated by machine learning algos). Users are anxious if they have to wait for more than 30 sec. With this kind of workload the IOPS is not that important, the sequential reads is. And SSDs are not significantly faster in case of sequential I/O - you can usually achieve the same sequential performance with spinners for less money). yes, you are right: seq IO: FusionIO is 3-5 times faster than our Hitachi SAN. SAN is 5-10 times faster than local SAS 15k drive. Random IO is completely different story. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Dynamic constraint names in ALTER TABLE
Hi, On 20 September 2011 13:09, patrick keshishian pkesh...@gmail.com wrote: e.g., ALTER TABLE sales DROP CONSTRAINT (SELECT conname FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 1 AND contype='f') ; You have to build query in different way: psql ... -A -t -c SELECT 'ALTER TABLE sales DROP CONSTRAINT ' || conname || ';' FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 1 AND contype='f' Finally, you can save it in to file or pipe it to another psql: psql ... -A -t -c '' | psql ... -- Ondrej Ivanic (ondrej.iva...@gmail.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] different unnest function [SOLVED]
Hi, to get the output OP wants, you need to expand and rewrap: create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$ select array(select unnest($1[i:i])) from generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ language 'sql'; Yup, this is what I need. Thanks -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] different unnest function
Hi,
I need function which unnest array in a different way. Input table has
ineger[][] column:
col1
--
{{1,2,3,4}, {5,6,7,8}, {9, 10, 11, 12}}
{{11,12,13,14}, {15,16,17,18}, {19, 110, 111, 112}}
...
and output should be:
select unnest2(col1) from T
unnest2
-
{1,2,3,4}
{5,6,7,8}
{9, 10, 11, 12}
{11,12,13,14}
{15,16,17,18}
{19, 110, 111, 112}
My function is:
create or replace function unnest2(anyarray) returns setof anyarray AS
$BODY$
select $1[i:i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$
language 'sql';
and the result is:
{{1,2,3,4}}
{{5,6,7,8}}
{{9, 10, 11, 12}}
{{11,12,13,14}}
{{15,16,17,18}}
{{19, 110, 111, 112}}
which is almost what I need...(or I'm at the beginning :)) Any ideas?
Thanks,
--
Ondrej Ivanic
(ondrej.iva...@gmail.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] share nothing but reads cluster
Hi, On 15 September 2011 23:40, Marc Mamin m.ma...@intershop.de wrote: Are there other way we should evaluate ? Should we better wait foir POstgres 9.2+ ? You can try pgpool-II (Parallel Query mode) or MPP database like Greenplum (Community Edition). Another option is high IOPS (500k+) SSD card but they are not cheap (but cheaper than comercial MPP solution) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Query performance help with 'shadow table' approach.
Hi, On 14 September 2011 07:44, Brian Fehrle bri...@consistentstate.com wrote: 2. I have appropriate indexes where they need to be. The issue is in the query planner not using them due to it (i assume) just being faster to scan the whole table when the data set it needs is as large as it is. Try to reduce random_page cost to 2, which biased planner towards index scans, (set random_page = 2 before the query; assuming that default seq_page_cost and random_page_cost are 1 and 4 respectively) and run explain analyze. Sometimes is worth to disable nested loops join (set enable_nestloop = off). Finally you can increase default_statistics_target (or ALTER TABLE SET STATISTICS) to 100 (8.4 has this as a default) on selected columns or table (and run analyze on that table). -- Ondrej Ivanic (ondrej.iva...@gmail.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] pg_dump with select command
Hi, On 12 September 2011 15:03, Adarsh Sharma adarsh.sha...@orkash.com wrote: Today I need some part ( subset ) of some tables to another database to a remote server. I need to take backup of tables after satisfying a select query. Is there any option to specify query in pg_dump command.I researched in the manual but not able to find that. Please let me know if it is possible as we can can specify in mysqldump command. No, pg_dump can dump full tables only. You can use psql: psql -h host1 ... -c 'copy (select ... from tablename where ...) to stdout' | psql -h host2 ... -c 'copy tablename from stdin' (where '...' are other psql's options like user, db, ...) -- Ondrej Ivanic (ondrej.iva...@gmail.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] How can I merge two tables?
Hi, On 2 September 2011 03:09, Jerry LeVan jerry.le...@gmail.com wrote: I keep registration numbers for software and login/passwords for various organizations, etc… As time goes by the tables on the various computers get out of sync. Is there an elegant way I can get all of the differences (uniquely) I would copy data from the other machines to the master one: on the master, under postgres user (data only dump; -a options): pg_dump -a -h host1 -t registrations -U user database -F c | pg_restore -d database pg_dump -a -h host2 -t registrations -U user database -F c | pg_restore -d database ... pg_dump -a -h hostN -t registrations -U user database -F c | pg_restore -d database and then do the following: begin; insert into tmp select distinct * from registrations; truncate registrations; insert into registrations select * from tmp; commit; -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sort Method: external merge
Hi, I have several queries in *single* transaction and I want to figure out reasonable work_mem value. Here is the excerpt from explain plan -- each query has two sorts: 1) Sort Method: quicksort Memory: 6 324kB Sort Method: quicksort Memory: 1 932 134kB 2) Sort Method: quicksort Memory: 28 806kB Sort Method: quicksort Memory: 977 183kB 3) Sort Method: quicksort Memory: 103 397kB Sort Method: external merge Disk: 3 105 728kB 4) Sort Method: quicksort Memory: 12 760kB Sort Method: quicksort Memory: 3 704 460kB 5) Sort Method: quicksort Memory: 84 862kB Sort Method: external merge Disk: 3 593 120kB 6) Sort Method: quicksort Memory: 4 828kB Sort Method: quicksort Memory: 112 472kB 7) Sort Method: quicksort Memory: 1 490kB Sort Method: quicksort Memory: 81 066kB 8) Sort Method: quicksort Memory: 78174kB Sort Method: quicksort Memory: 2 579 739kB 9) Sort Method: quicksort Memory: 101 717kB Sort Method: quicksort Memory: 2 913 709kB work_mem is set to 4 000 000 kb and I do not understand why few queries (3 and 5) used disk and the rest fit were able to data into memory. Why disk was used and subsequent query was able to sort bigger data set in the memory (see 3 and 4)? The box has 90GB RAM and no other queries run during that time. PG version is: PostgreSQL 8.4.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.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] Sort Method: external merge
Hi, On 26 August 2011 00:14, Tom Lane t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Ondrej_Ivani=C4=8D?= ondrej.iva...@gmail.com writes: work_mem is set to 4 000 000 kb and I do not understand why few queries (3 and 5) used disk and the rest fit were able to data into memory. The on-disk representation of sort data is quite a bit more compact than the in-memory representation. So where it says that 3.5GB of disk were needed, it's not a surprise that the memory requirement would have exceeded 4GB to do the sort in-memory. I managed to do in memory shorting by setting work_mem to 9GB. Memory usage peeked around 6.5GB. The idea behind this exercise was to see if query could perform better: - original query: around 8 hours - parallelised query over 4 connections: default work_mem (=256M): 110 minutes work_mem = 4G: 99 minutes work_mem = 9G: 97 minutes parallelised query: original query is group by style query and one of the group by attributes has around 40 unique values. I replaced original query by 40 queries (with different value for that attribute) and execute them over 4 connections. If you want to know what the conversion factor is for your particular dataset, try doing the query with small and large work_mem so you can see what is reported as the amount of space needed each way. Looks like that disk representation is half of memory usage in my case. Anyway, the test showed that work_mem is irrelevant in my case (disk is fast SSD PCIE card) Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.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] Indexes on inheriting tables
Hi, On 25 August 2011 11:17, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Yes, it is little bit annoying but I like it. You don't need any index on parent table but you have to create them manually. I wrote simple python script which creates partitions and required indexes in advance (tables are partitioned by date). I like the flexibility because you can have different indexex on different partitions. For example, I discovered that adding index will improve several queries. In the production I can't afford exclusive lock (build index concurrently takes ages) so I updated and re-run the script which re-created future partitions. -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres on SSD
Hi, On 12 August 2011 14:57, Greg Smith g...@2ndquadrant.com wrote: I'm about to evaluate this SSD card: FusionIO ioDrive Duo [1]. The main reason for this experiment is to see if SSD can significantly improve query performance The result is that FusionIO will help to our queries which was expected. Most of the long running queries return data between 5 and 30 sec range which is very good. The rest of the queries is super fast but aggregates queries need sometimes several minutes. Anyway, the overal performance is satisfactory and the rest could be fixed by redesigning aggregates (keep/rollover top N counts not everything) Database size is around ~1.4TB. Main tables occupied around 1/3 (450GB, ~220mil rows) and aggregated data occupied 2/3 (900GB). All indexes are on separate table space (~550GB) The redesign mentioned above can reduce aggregated data size to size between 1/3 and 1/4 of the current size (and speed up queries). I've tried to change several settings (work_mem, shared_buffers, random/seq/... costs) but I wasn't able to get better benchmark results. Our schema is very simple and query execution plan is reasonable. checkpoint_segments | 48 maintenance_work_mem | 256MB shared_buffers | 9GB wal_buffers | 50MB work_mem | 256MB checkpoint_segments should be higher, at least 64 and probably 128 to 256. shared_buffers should be lower (at most 8GB, maybe even less). maintenance_work_mem should be 1 to 2GB on a server with 80GB of RAM. There's no proven benefit to increasing wal_buffers over 16MB. I think this is the part which I have to look at... Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.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] Where to start, graphs and routing.
Hi, On 14 August 2011 20:25, k_b k_b0...@yahoo.se wrote: Hi. For learning purpose i would like to make a small database with a small graph of locations, roads and public transport information. Then calculate the fastest or cheapest way between two points. If we think of a minimal network, as below. A ---5-- B ---10 C \ / \-5-/ Welcome in the club! I've been there and I can say that is very interesting exercise. My schema was simple: - bus stop table: just list of all bus stop and related meta data (like this bus stop is part of transit centre X, ...) - schedule table: contains all possible combination how to travel between two adjacent stops: (stopA, stopB, timeA, timeB, route_n). Table had several million rows which was necessary because of the following anomalies: * A - B could be 5 min but B - A could be less or more * peak / off peak / weekend schedule could be different * you can take bus A - B - C but on the way back bus doesn't serve stop B (ie C - A) It would be possible to limit number of row in that table using smarter encoding system for bus departure/arrival times. I didn't use it because I generated that table from official timetables. queries were simple; first query was something like this select * from schedule_table where stopA = :start then for each row from the previous query (and repeat this query): select * from schedule_table where stopA = :stopB and timeA result_timeB + :threshold After the second, third, ... query I did the following checks: - merge parts with the same bus number (ie A - B, B - C = A - C) - increment waiting/transfer and total travel time accordingly - remove stupid routes. This part is quite tricky and some heuristics is needed. I removed routes with many service changes and excessive waiting/travel times Today, I would try to use Postgres spatial data types/extensions because you can get bus stop locations from openstreetmap (or google maps). Moreover you can exclude bus stops (or complete routes) which are too far from from/to locations (again, some heuristics/rules could be necessary) -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres on SSD
Hi, On 12 August 2011 14:57, Greg Smith g...@2ndquadrant.com wrote: ioDrive hardware is fast at executing all sorts of I/O, but it particularly excels compared to normal drives with really random workloads. That's what I hope for :). It looks like that ioDrive is 3 to 5 times faster for seq IO comparing to our SAN Your tables are pretty big; not much of them will fit in memory. If your aggregated queries end up executing a lot of sequential scans of the data set in order to compute, or for them to be utilized, you will probably discover this is barely faster on FusionIO. About 99% disk activity is random IO (reads). Seq IO is caused by reading last X minutes of data by aggregates cron job. Majority of the queries are primary key scan queries plus some extra where condition filtering. Default random_page_cost and seq_page_cost wasn't way to go because planner chose seq scan and query execution took so long Is there a component to your workload that does a lot of random read or write requests? If so, is that chunk of the data set bigger than RAM, but small enough to fit on the FusionIO drive? Only when all those conditions are true does that hardware really make sense. Yes, almost all queries do random IO. Final result set is the top 1000 rows only but several weeks of data must be read and sorted before applying the limit. I have two cards available (= 2.4TB) so I can have entire dataset there. But at the same time, tests on database sizes that fit into RAM were slower on FusionIO than the regular disk array. When there's no random I/O to worry about, the slower read/write write of the SSD meant it lost the small database tests. Makes sense to me. checkpoint_segments | 48 maintenance_work_mem | 256MB shared_buffers | 9GB wal_buffers | 50MB work_mem | 256MB checkpoint_segments should be higher, at least 64 and probably 128 to 256. shared_buffers should be lower (at most 8GB, maybe even less). maintenance_work_mem should be 1 to 2GB on a server with 80GB of RAM. There's no proven benefit to increasing wal_buffers over 16MB. Yes, shared_buffers are high; based on your input and other sources I would like to try lower values. I really do not understand this part of the magic - I mean checkpoint_segments and WAL related settings. This setting for work_mem can easily allow your server to allocate over 250GB of RAM for query working memory, if all 100 connections do something. Either reduce that a lot, or decrease max_connections, if you want this server to run safely. Yes, I'm aware about this issue. There nothing like pg-pool between Apache (PHP) and Postgres and there is no more than 15 connections simultaneously Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres on SSD
Hi, 2011/8/11 Amitabh Kant amitabhk...@gmail.com: There have been several discussions for SSD in recent months although not specific to Fusion IO drives. See http://archives.postgresql.org/pgsql-general/2011-04/msg00460.php . You can search the archives for more such reference. I've read this one several days ago but the discussion turned into flamewar about SSD longevity... Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.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] Postgres on SSD
Hi, 2011/8/10 Tomas Vondra t...@fuzzy.cz: On 10 Srpen 2011, 1:17, Ondrej Ivanič wrote: - What needs to be changed at Postgres/Operating system level? The obvious one is to change random_page_cost (now: 2) and seq_page_cost (now: 4). What else should I look at? Are you sure about this? I'm not quite sure setting seq_page_cost=4 and random_page_cost=2 makes sense. Usually seq_page_cost is lower than random_page_cost, so I wonder why have you set it like that. Ups! Well spotted Tomas! The actual values are: random_page_cost = 2 seq_page_cost = 1 -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres on SSD
Hi, I'm about to evaluate this SSD card: FusionIO ioDrive Duo [1]. The main reason for this experiment is to see if SSD can significantly improve query performance. So, I have the following questions: - Could you please share your experience with SSD? Any issues? - What needs to be changed at Postgres/Operating system level? The obvious one is to change random_page_cost (now: 2) and seq_page_cost (now: 4). What else should I look at? Background: Database schema is pretty simple: Database size is around ~1.4TB. Main tables occupied around 1/3 (450GB, ~220mil rows) and aggregated data occupied 2/3 (900GB). All indexes are on separate table space (~550GB) 1) main table(s) This big fact table has ~200 columns and average row size is 1.5kb. This table is never updated and new data is inserted constantly using copy in chunks about 10k rows. Table is quite sparse so it is broken into 5 tables which are joined as necessary (regular query reads 10 - 40% of the row size). Tables are partitioned by month but I'm thinking to use week or two week partitions. Primary key is composite key (datetime, organisation, transaction_id). The transaction_id column is unique but datetime column is used for partitioning and all queries contains organisation= (multi-tetant database). In addition, there are ~15 single column indexes. Old data is deleted after 6 months (drop partition) The usual query looks like this: - select ... from T where organisation = ... and datetime between ... and ... where ... order by single col limit 1000 User can choose any column for sorting but we created indexes for the most popular/reasonable ones (those ~15 single column indexes). In the reality, query is more complex because of few Postgres issues: - partitions/limit/order issue described on Stackoverflow and fixed in 9.1 or 9.2 [2], [3]. - partitions/join issues ie left join on clause must contain datetime condition in order to avoid fulltable scan on joined table Query response time for indexed columns is between 5 to 30 sec (sometimes 200sec). The target is to have all queries under 5 sec. If query has order by on non-indexed column then response time is in hundreds seconds but desired response time should be 10sec (test query is over 1 month range and organisation has between 0.5 and 2 mil row per month; single partition has 30 mil rows) 2) materialised aggregate tables About 45 tables like this: agg_attribute1_attribute2(date, organisation, attribute1, attribute2, count) (= select datetime::date, organisation, attribute1, attribute2, count(*) from T where organisation = ... and datetime between ... and ... group by 1,2,3,4) Tables are updated by cron job every 15 minutes. Thanks for 8.3 HOT updates - almost no bloat! Monthly partitions are used (date column). Query response time is between 5 to 30 sec (sometimes 200sec) and the target is to have all queries under 5 sec Usual query is: select attribute1, count(*) from agg_attribute1_... where organisation = ... and datetime between ... and ... group by 1 limit 10 or select attribute1, attribute2 count(*) from agg_attribute1_attribute2 where organisation = ... and datetime between ... and ... group by 1 limit 10 Top N queries perform even worse -- the query response time is in minutes and the target is around 15 sec Current hardware setup: XEN host with 16 CPU (Intel(R) Xeon(R) CPU L5520 @ 2.27GHz). CentOS 5.6 80GB RAM Storage: some Hitachi Fibre channel SAN with two LUNs: 1st LUN has *everything* under $PG_DATA (used 850 GB) 2nd LUN has *all* indexes (index table space) (used 550GB) Postgres settings: name | current_setting --+-- version | PostgreSQL 8.4.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4. 1.2-48), 64-bit archive_command | walarchive.sh %p %f archive_mode | on autovacuum | on autovacuum_max_workers | 6 autovacuum_naptime | 5min autovacuum_vacuum_cost_delay | -1 checkpoint_completion_target | 0.9 checkpoint_segments | 48 constraint_exclusion | on default_statistics_target| 100 effective_cache_size | 20GB fsync| on lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 log_autovacuum_min_duration | 0 log_destination | csvlog log_min_duration_statement | 10s log_rotation_age | 1d log_rotation_size| 0 log_truncate_on_rotation | on logging_collector| on maintenance_work_mem | 256MB max_connections | 100 max_stack_depth | 2MB random_page_cost | 2 server_encoding | UTF8 shared_buffers | 9GB TimeZone | UTC
Re: [GENERAL] table / query as a prameter for PL/pgSQL function
Hi,
2011/8/9 Merlin Moncure mmonc...@gmail.com:
You have a few of different methods for passing sets between functions.
I do not want to pass data between functions. The ideal solution
should look like this:
select * from my_map_func(select query)
1) refcursor as David noted. reasonably fast. however, I find the
'FETCH' mechanic a little inflexible.
I've came across this but manual example wasn't (isn't) clear to me:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
What is the funccursor?
Anyway, I will try to use something like this
CREATE FUNCTION my_map_func(refcursor) returns setof text[] as $$
...
$$ LANGUAGE 'plpgsql';
BEGIN;
DECLARE my_cursor NO SCROLL CURSOR FOR query;
SELECT * FROM my_map_func(my_cursor);
COMMIT;
I'll keep you posted.
2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a
headache because a non temp table can get thrashed pretty hard a and a
'TEMP' can cause severe function plan invalidation issues if you're
not careful
I'm not familiar with this issue (function plan invalidation issues).
Could you please provide more details/links about it?
3) arrays of composites -- the most flexible and very fast for *small*
amounts of records (say less than 10,000):
My data set is huge: between 1 and 5 mil rows and avg row size is 100
- 400 bytes
--
Ondrej Ivanic
(ondrej.iva...@gmail.com)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table / query as a prameter for PL/pgSQL function
Hi, It is possible to pass query result (or cursor?) as function parameter? I need a function which emits zero or more rows per input row (map function from mapreduce paradigm). Function returns record (or array): (value1, value2, value3) I've tried the following: 1) create or replace function test (r record) returns setof record as $$ ... Doesn't work: PL/pgSQL functions cannot accept type record 2) pass query as text parameter and open no scroll cursor inside the function It works but it's ugly. 3) hardcode the query inside function Similar to (2) and looks better but I need several functions with different queries inside: ... for r in (query) loop ... end loop; ... 4) use function in select clause: select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query In this case I wasn't able figure out how to access record members returned by the function: select ?, ?, ?, count(*) from ( select my_map_func(col1, col2, col3, col4) as map_func_result from ... ) as map group by 1, 2, 3 The '?' should be something like map.map_func_result.value1 (both map.value1 and map_func_result.value1 doesn't not work). If function returns array then I can access value1 by using map_func_result[1] Is there a better way how to solve this? I'm kind of satisfied with 4 (maybe 3) but it is little bit cumbersome Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
