[GENERAL] Scalable cluster
Hi guys, I'm looking into setting up an HA scalable DB cluster. So far my tests with streaming replication proof that it is very very good indeed. However, problem seems to be on the connection pooling side. Ideally, we would love to have single point of connection to the cluster, but I do realise that it might not be feasible. So far I've been testing pgpool-II 3.2.3 and 3 DB servers. And as much as I'm impressed by postgresql itself. pgpool simply fails on pretty much every front. That is, in terms of scalability, running dbbench against it, proves to be much slower then direct connection to the master. It also goes into strange states when you overallocate connections, etc. Not really something I'd trust on a production server. I don't know of any other pooling solution that would be capable of handling the job, but focus only on the task of pooling (pgpool's fault probably is that it is trying to be jack of all trades) in HA replicated scenario. What is out there, free or paid - that would solve an HA DB Cluster running Postgresql (ideally 9.2), that you guys could suggest ? Personally, I think that having single connection point DB Cluster is not going to be ideal solution anyway. So question is, how would you guys go about designing a cluster that handles Java/C/C++ applications connecting from some number of servers ? It has scale (adding more servers, to improve performance, or aid in case storage has become a problem), but also be redundant in case hardware fails. Thanks. -- GJ
Re: [GENERAL] Scalable cluster
On 3 March 2013 22:56, John R Pierce wrote: > > did you look at pgbouncer ? thats the simple pooler for postgres, and its > quite robust, because its so simple. > > Yes, it is one of the solutions I do consider. Having applications decide whether they should write to master, or use slaves and/or master for read queries (for instance in case it is a transaction, etc). I wonder however, how others are handing it. There seems to be nothing out there apart from pgbouncer and pgpool. And only the latter can handle (albeit not really that quick) pooling between master and slaves. How do you guys go about designing such cluster. -- GJ
[GENERAL] table spaces
Performance related question. With Linux (centos 6.3+), 64bit, ext4 in mind, how would you guys go about distributing write load across disks. Lets say I have quite few disks, and I can partition them the way I want, in mirror configuration (to get some hardware failure resilience). Should I separate tables from indexes onto separate raids ? I know WAL has to go on a separate disk, for added performance. I'm looking for your experiences, and most importantly how do you go about deciding which way is best. I.e. which combinations make sense to try out first, short of all permutations :-) Thanks. -- GJ
Re: [GENERAL] table spaces
On 10 March 2013 02:19, Scott Marlowe wrote: > > First get a baseline for how things work with just pg_xlog on one > small set (RAID 1 is often plenty) and RAID-10 on all the rest with > all the data (i.e. base directory) there. With a fast HW RAID > controller this is often just about as fast as any amount of breaking > things out will be. But if you do break things out and they are fster > then you'll know by how much. If it's slower then you know you've got > a really busy set and some not so busy ones. And so on... > (side note, google mail in their infinite evilness make it tricky if not careful to reply below post using their webapp, beware). I might have a table that needs some heavy writes, and while it doesn't necessarily have to be fast TPS wise, I don't want it to bog down rest of the database. Reads are ok, as I'm planning for the DB to fit in RAM cache, so once read - it will be there - more or less. It's distributing writes that I care about mostly. I'll try iostat, whilst running characterisation scenarios. That was my plan anyway. I had no idea separating indexes from tables might help too. Would have thought, they both are interconnected so much in the code, that dividing them up won't help as much. What about table partitioning ? For heavy writes, would some sort of a strategy there make difference ? -- GJ
Re: [GENERAL] table spaces
Ok, So by that token (more drives the better), I should have raid 5 (or whichever will work) with all 6 drives in it ? I was thinking about splitting it up like this. I have 6 drives (and one spare). Combine them into 3 separate logical drives in mirrored configuration (for some hardware redundancy). And use one for base system, and some less frequently read tables, second one for WAL, third one for whatever tables/indexes happen to need separate space (subject to characterisation outcome). I was basically under impression that separating WAL is a big plus. On top of that, having separate partition to hold some other data - will do too. But it sounds - from what you said - like having all in single logical drive will work, because raid card will spread the load amongst number of drives. Am I understanding that correctly ?
Re: [GENERAL] table spaces
On 12 March 2013 21:59, John R Pierce wrote: > On 3/12/2013 2:31 PM, Gregg Jaskiewicz wrote: > >> I was basically under impression that separating WAL is a big plus. On >> top of that, having separate partition to hold some other data - will do >> too. >> But it sounds - from what you said - like having all in single logical >> drive will work, because raid card will spread the load amongst number of >> drives. >> Am I understanding that correctly ? >> >> > both those models have merits. > > doing a single raid 10 should fairly evenly distribute the IO workload > given adequate concurrency, and suitable stripe size and alignment. > there are scenarios where a hand tuned spindle layout can be more > efficient, but there's also the possibility of getting write bound on any > one of those 3 seperate raid1's, and having other disks sitting idle. I'm trying to get an understanding of all options. So out of 6 disks then having 4 in Raid 1+0 configuration and other two in mirror for WAL. That's another option then for me to test.
[GENERAL] get number and names of processes connected to postgresql
Basically, I got bunch of local processes connecting to postgresql, need to aggregate some sort of report about number of connections and its origin every so often. pg version is 8.3 Any ideas if there's tools to gather that info on linux ? Netstat is the only one I know, but I have to parse/awk its output to get something meaningful out of it. Ideas are welcomed. -- GJ -- 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 compress
can you pipe things on windows ? It's a desktop system after all, but dos had that sort of a feature - I seem to remember. -- 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 compress
Oh, neat. And I'll call myself wizard. People will think I am one... -- 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] get number and names of processes connected to postgresql
My apps share same databases, so no good in that. And I am very well aware of the new feature in 9.0 - but we're stuck in the 8.3 land for now. So far I managed to hack together a netstat+awk+other command line tools to get that information. (in your face - windows "server" developers/admins :P) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bytea insert difference between 8.3 and 9.x
So consider this code C++, using libpqxx: string = "INSERT INTO foo(x) VALUES( E'" + T.esc_raw(data) + "' )"; foo(x) is bytea , before you ask. On 8.3, it works fine. On 9.x: ERROR: invalid byte sequence for encoding "UTF8": 0x00 (if \000 is in the string). Now, I can take out the E'' and it will work fine on 9.X, but will whine about it on 8.3. (HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.) I need one piece of code that will work on both, what should I do in this case ? Thanks. -- GJ -- 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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
You can always store it divided in the database into two columns. Gist could also work for you.
Re: [GENERAL] bytea insert difference between 8.3 and 9.x
On 26 September 2011 14:39, Merlin Moncure wrote:
> urk -- I have to be honest -- that's a pretty lousy way to send bytea.
> Personally, I'd encode the string as hex and send it like this:
>
> "INSERT INTO foo(x) VALUES( decode('" + hex_string + "'))";
>
> libpqxx doesn't have the ability to parameterize queries?
>
Thanks Merin.
It does, and that's probably what I'll do. Your solution isn't great
either, because it requires extra function to be run on the postgresql
side. Me no likeey that ;)
When you say parameterized - it allows you to prepare queries - which
I do in 80% of select/insert/update cases, apart from some older code
that no one wants to touch.
But the time came for me to act on it, and try to put us forward using
9.1 instead of old rusty 8.3 (which is still better then 8.1 they used
before I started working here).
m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea",
pqxx::prepare::treat_binary);
Gotta try that one with both boys ;)
Btw, I hope 9.1.1 is out soon, gotta package some version for tests.
We used floating point timestamps, and I gotta repackage centos rpms
with that config option - otherwise it's pg_dump and restore of gigs
of data
--
GJ
--
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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
You can create your own type, but that means writing bit code in C. Please, stop the top posting! -- 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
speaking of DO INSTEAD, for insert/update case. Try using RETURNING with that and rules ;) Good luck -- 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
You're right, rules are perfect for very limited and narrow cases. And make it very hard to write complicated queries against. (i.e., updates that only touch few columns, likewise with inserts). I'm guessing the upside is that rules are faster then triggers. -- 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] Bulk processing & deletion
If you don't need the data for more then a transaction, or connection length - use temporary tables to store ids of data you need to delete. If those change, or move, or something - it means you are missing PK on that table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
what sort of queries you are running against it ? the select * from.. is not really (hopefully) a query you are running from your php app. -- 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] troubleshooting PGError
your transaction had an error, and any query after the first one that has failed will be ignored. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FK dissapearing
So I have a strange issue on one of our live systems. \d+ table shows me the FKs with cascaded deletes, but querying pg_trigger doesn't show me any specific triggers for the FK. Is that possible ? Or am I missing something here? The psql version is 8.3.7 . -- GJ -- 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] FK dissapearing
On 11 November 2011 12:25, Gregg Jaskiewicz wrote: > So I have a strange issue on one of our live systems. > > > \d+ table shows me the FKs with cascaded deletes, but querying > pg_trigger doesn't show me any specific triggers for the FK. > Is that possible ? Or am I missing something here? > > The psql version is 8.3.7 . What happened it seems was that the box run out of disk space (it's a test box), and postgresql (obviously) kicked the bucket. Was restarted, and worked fine until I've noticed the FK problem. I was trying to get a backup just now, and got this: pg_dump: failed sanity check, parent table OID 1026802 of pg_rewrite entry OID 1026968 not found Questions: - how to fix it - is it something that's been fixed in 8.3.x, where x > 7 ? -- GJ -- 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] CLONE DATABASE (with copy on write?)
NVM the implementation, but ability to clone the database without disconnects would be very good for backups and testing. We also create loads of templates, so that would make it more practical. -- 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] FK dissapearing
I know it's a no-no to respond to my own posts, but here's what I'm going to do. I'll test newer revisions of 8.3 and also 9.1 in the out-of-disk-space scenario and report back :P -- 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] Incremental backup with RSYNC or something?
pg_dump -Fc already compresses, no need to pipe through gzip -- 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] checkpoints are occurring too frequently
increase your checkpoint segments -- GJ -- 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 + fsync=off?
What if power supply goes ? What if someone trips on the cable, and both servers go ? -- 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")...
partition your table if it is too big. -- 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] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data
for the future it is better to just use text type, and: check length(field) < 35; -- 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 licensing of Postgresql
Get a lawyer that knows this stuff. Whilst asking around is good, if you want serious answer - you can't count on bunch of people on the list. Within GPL there are also variants, like LGPL, AGPL, etc. There are some lawyers that specialize in opensource, ask them. Most people here should have added to their post - IANAL.. -- 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] [BUGS] BUG #6325: Useless Index updates
btw, HOT was introduced in 8.3. On 6 December 2011 14:51, Daniel Migowski wrote: > > Continuing this talk on general, as requested by Craig. > > I have a functional Index on a table that is relative expensive to calculate. > Now I noticed on every update of even index-unrelated fields of the table the > index function is calculated again and again. > > I currenly understand that if the update moves the row to a new location (no > HOT replacement), the key to the index has to be calculated from the old and > the new row to update the index. > > This is expensive in my case, and useless, if the input to the immutable > index function has not changed in my update statement, and as such the > calculation should always be done just once. In case of HOT replacement, it > hasn't to be done at all. > > I assume, that comparing values to each other is in most times cheaper than > calling a function have these values as parameters. If there is a high cost > on the function (>1000?), it would be a good thing to always check if the > inputs to the function have changed, before calling this function once or > twice. Since I have a lot of functional indexes, I would greatly profit from > an improvement in this area. > > Is anyone interested in implementing this? > > Regards, > Daniel Migowski > > Von: Craig Ringer [ring...@ringerc.id.au] > Gesendet: Sonntag, 4. Dezember 2011 15:02 > Bis: Daniel Migowski > Cc: pgsql-b...@postgresql.org > Betreff: Re: [BUGS] BUG #6325: Useless Index updates > > On 12/04/2011 08:54 PM, dmigow...@ikoffice.de wrote: >> The following bug has been logged on the website: >> >> Bug reference: 6325 >> Logged by: Daniel Migowski >> Email address: dmigow...@ikoffice.de >> PostgreSQL version: 8.3.16 >> Operating system: Linux >> Description: >> >> It seems that an update to a row in a table always removes the element from >> an index and adds it again. Wouldn't it be faster to check for equality of >> the index parameters in the OLD and NEW record first? > > - This isn't a bug report, it's a feature/enhancement request. Please > use the mailing lists. > > - You're reporting this issue against an old patch release of an old > major release. Why not check with 9.1? > > - The index isn't always updated. Check out HOT (introduced in 8.4, the > release after your current one) which reduces unnecessary index > updates in cases where the old and new row can fit on the same > heap page. > > - In most other cases the index update can't be avoided, because > the new and old rows are on different database pages. The old index > entry has to remain in place so that still-running transactions that > can see the old row can still find it in the index, so it can't be > overwritten and instead a new entry has to be added. > >> I have this problem with an functional index using a relative expensive >> index function, and noticed that the index function is always called even if >> the parameter to the index function has not changed. Wouldn't it be better >> to validate that the input to the index functions has not changed, instead >> of calling the index function over and over again? Especially since the >> index functions seems to be called with the new and the old value anyway. > > That's a more interesting one. Perhaps you could write it up in more > detail, with a test case, and submit it to the pgsql-general mailing list? > > This isn't just about functions anyway. Pg would have to compare *all* > inputs to the old index expression to see if they were the same. > Otherwise, in an expression like f(g(x,y),z) Pg would not have any > stored value for the result of g(x,y) to compare against. It'd have to > instead compare (x1,y1,z1) to (x2,y2,z2) and decide that if they were > the same the result of the index expression hadn't changed. > > That's probably possible, but I'm not sure it'd be a win over just > evaluating the expression in most cases. How would Pg know when to do > it? Using function COST parameters? > > Essentially, this isn't as simple as it looks at face value. > >> I can understand that this might be a precaution in the case that the index >> function isn't stable (is it even possible to use such a function for an >> index?) > > No, it isn't possible. Index functions must be immutable, not just > stable, so their output must be determined entirely by their parameters. > At least on newer versions STABLE or VOLATILE functions should be > rejected in index expressions. > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- GJ -- 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] Why Hard-Coded Version 9.1 In Names?
Its because of pg_upgrade, 'in place' upgrade capabilities that are in pg since 8.4. For that to work you need both old and new (current) set of postgresql binaries. Etc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovacuum and deadlocks
What is a likelihood of a deadlock occurring, caused (or helped by) auto vacuum. This is on 8.3. The table with deadlocks was quite busy with updates, etc. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] language name case sensitivity, also known as plpgsql <> 'PLpgSQL'
Folks, I'm testing some code on 9.2dev (trunk), and I've noticed that postgresql seems to be fussy about language case when creating a function. So for instance: create function foo() returns int AS $$ BEGIN return 1; END; $$ LANGUAGE 'PLpgSQL'; Will be fine on 8.3 (my current version used in product), but not so fine when using 9.2dev. I think this is obviously a regression. What you say ? -- GJ -- 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] language name case sensitivity, also known as plpgsql <> 'PLpgSQL'
On 26 March 2012 16:41, Thom Brown wrote: > > Probably something to do with this: > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=67dc4eed42186ba6a2456578899bfd38d003201a Sounds very plausible. Would you call it a regression ? I would say so, but not sure what would be an argument on the other side then ? Mine is, that some of currently used code will fail on this for no apparent benefit. Plus the bit where he says, that params in quotes will be lowercased - obviously doesn't work then -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] oracle linux
They seem to claim up to 70% speed gain. Did anyone proved it, tested it - with PostgreSQL in particular ? They seem to run the same way as RHEL do, ie - you can download it for free, but pay for repo access. (thus updates). -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
