recycling. You might also check
pg_stat_replication to get an easier view of things, rather than relying
on ps. ps is correct, it's just harder to check.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadra
On 06/20/2012 11:38 PM, wangqi wrote:
> shared_buffer maximum limit is ?
> I set shared_buffer more than 250G, I got error at startup pg.
There's not much evidence that values larger than 16GB are really
productive. You really do not want put all their memory in just that
one place
static/sql-createfunction.html
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail
://archives.postgresql.org/pgsql-hackers/2011-12/msg00822.php
You might use some of that code and make your own pg_cancel_backend-like
function that loads into the database.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
e canceled when those cleanup records are replicated. The
fact that A is never changed and doesn't have autovacuum enabled doesn't
matter. Because it's possible for Q to see B, it's canceled regardless
of whether it's looked at it yet.
--
Greg Smith 2ndQuadrant US
y lowering that a lot. The 256MB to 1GB range
is where I normally end up on servers where lower latency is prioritized
instead of maximum throughput.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
tive. Aim to commit and
freeze as fast as possible afterwards, avoiding some of the intermediate
writes you might otherwise see. That's the best that can be done
without some hacking on the server code.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Tra
licensing an already built package would sell for. Companies who aren't
willing to throw some money toward buying/customizing at least some
PostgreSQL software, if it allows escaping from Oracle licensing, should
reconsider their strategy. Not everything you'll want to make a
conversion eas
estore using it. That would have discovered
this problem during testing.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make
an that be accomplished ?
You might be able to get what you want here by setting statement_timeout
to 'n' seconds when doing a write transaction.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
er this year, synchronous
replication is available on a per-transaction basis. That resolves the
concern you have--important transactions can be confirmed on one of the
slaves as a requirement before they commit.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
Comprehensive
tem before, you might want to do the standard fix suggested using
8.4.8. But if this has always been an 8.4 system, you can just go right
to 9.0.4.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"Po
n't get the one they started
with to stop using prepared statements, and those gave them terrible
query plans.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance"
r anything that crosses the two
databases together.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sent via pgsql-admin
s properly.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To
he plan will help confirm what's going on.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sent via pgsql-admin mailing l
my book
is the main extended commentary discussing this area available.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sen
sure what differences
between 8.4.6 and earlier 8.4 versions you think you've observed.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadran
moving data over. Relying on the database's transactional
scheme to avoid making any mistakes here--making it so a goof will
ROLLBACK--and avoiding any need for downtime are normally higher
priorities in a partition migration than making the move happen as fast
as possible.
--
Greg Smith 2ndQ
never meet its
criteria. There's nothing wrong with that. Writing out dirty buffers
only once per checkpoint is in theory the most efficient way to handle
regularly changed data.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, a
ract the available data
using the right data recovery techniques, especially if there's a
pg_dump available too; we offer some services in this area. But if any
serious changes are made to the database before we get to it, odds of
successful recovery can drop fast.
--
Greg Smith 2ndQ
fiber-channel switch level is pretty straightforward. DAS running over
fiber-channel can offer the same basic features though, it's just not as
common to use a switch in that environment.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, a
ere is not the database's postgresql.conf,
but instead the database cluster list you see when running pg_lsclusters.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": ht
o how complicated systems are.
Benchmarking yourself is the only reasonable defense against this very
common problem.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance&qu
about unclean shutdown, but no data should be lost.
The "service" scripts don't do that though. You'll need to do something
like this instead, presuming you start as root:
su postgres
pg_ctl -D /var/lib/pgsql/data -m immedate stop
--
Greg Smith, 2ndQuadrant US g...@2ndquadra
experiment at just how long that timeout should
be. If you set log_min_duration_statement (which is a general good idea
in this situation anyway) and look at what kind of runtime common
intense but not crippling queries take, that's one way to get feedback
on where the timeout should be.
/wiki/Version_History that should provide some
additional guidance if you read through them a bit.
By the time you get that done, 9.0 should be a better tested and stable
release, and you can do a quick binary upgrade from 8.4 to 9.0 using the
pg_upgrade utility.
--
Greg Smith, 2ndQuadrant
ACUUM_FULL interesting as well.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
ter integration with the application itself normally in the
process.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"Pre-ordering at:
https://www.packtpub.com/postgr
your system causing the problem instead, but from the fact that you're
using the 192.168.*.* block I'd assume your client and the server are on
the same local LAN.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQua
server restart for. Those are
the main three (along with maintenance_work_mem) that impact how fast
VACUUM work progresses.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performan
y
arrangements work in any time that isn't measure in large multiples of
hours, so "10 seconds" I'm skeptical of.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.
partition.
That can happen when a major disk-level problem occurs; system remounts
as read-only because it doesn't think it can safely write to it
anymore. I'd check into the system kernel logs as soon as possible, to
try and find a disk error that kicked the whole thing off.
--
x27;t know why the crash happened, either. If you know
for sure how it crashed, but only then found corruption, Scott's right
that it's more likely to be a lying disk drive instead.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services a
nd reload that before you start using it. You have no
idea what state all of the tables are really in after a crash like this
without such an exercise.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, &qu
If your storage is on EBS, I'm
not sure how that handles two attempts to attach a writer to the store,
but you'd want to test that out too.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, &quo
nteractive/catalog-pg-statistic.html
and to
http://www.postgresql.org/docs/current/interactive/planner-stats-details.html
for more details.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Pe
other than the main database sofware you can have
installed will rely on those. Here's what I do to get rid of all the
rest of them in one easy command:
rpm -qa | egrep "^postgresql" | xargs rpm -e
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Tra
rved characters that are input.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
working well than what I just described.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
wrote above) isn't mentioned. Seems like noting
this file is created in the unix_socket_directory entry of the docs, and
the rationale for why in the source code, would make a useful
improvement. Anybody want to write a little documentation patch?
--
Greg Smith 2ndQuadrant US Baltimore, MD
counted on packaged builds of PostgreSQL I'd be leaning how to do
that myself fast--before they just remove resources related to it
without warning anyone (again).
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
-
http://freebsdfoundation.blogspot.com/2010/06/dtrace-userland-project.html
which will give me yet another reason to consider deploying on that OS
instead of Linux.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.u
e and therefore won't start with your current
binaries.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to you
ump even if log shipping is the main backup
mechanism for a database, just so that corruption in the underlying
files is caught as early as possible by trying to read every block and
confirm it has valid data.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@
ot depends on the value of your data and whether
it can be retained at some higher level when this happens instead.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin
be more fragile and buggy
than just using 9.0--the bugs would be just be in your own code rather
than in the core server.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql
have queries to optimize better.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Cliff Pratt wrote:
What are the main 'knobs' that can be used to tune PostgreSQL?
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent
/postgresql/pgrpm.htm
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
e accurate bloat numbers, but most people consider that too
much work relative to the improvement you get over the simpler
check_postgres estimate.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
.
So would taking a snapshot before and after this import be the right
way to start the analysis?
Sure. Might want to grab one at those two points, then at a point right
before the next big import happens, so that you have what a relatively
idle period looks like for comparison sake.
--
of
pg_stat_bgwriter data. But that doesn't help once you've reached the
point where you want to change something and measure how response
changes afterwards. That requires more regularly sampling the data, so
you have a delta between two times.
--
Greg Smith 2ndQuadrant US
from a single master table. It can
make this whole class of problem go away.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to you
Greg Smith wrote:
You don't much with a single snapshot of pg_stat_bgwriter data. Try
saving this instead:
select *,now() from pg_stat_bgwriter;
And then take another snapshot at least a few hours later, preferably
the next day. With two snapshots and timestamps on them, then it's
other snapshot at least a few hours later, preferably
the next day. With two snapshots and timestamps on them, then it's
possible to make some sense of the numbers.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
-
ents that already have open connections.
I recommend using SHOW or looking at pg_settings after making a change
and reloading the server config to confirm it took.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQua
he counts at the end, and -A is the shortcut for what you had as "-P
'format=unaligned'". I throw "-At" into almost every use of psql from a
bash script I do, that's the usual combination that gets the basic
format to be right; then tweak things like the fiel
an expecting people to know the convention
that the first non-option passed to psql is a database name.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
ou're on a system that supports returning
memory info using getconf, it outputs the lines you need to put into the
kernel configuration.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
#!/bin/bash
# Ou
n this particular area, and they're impossible to solve just
by throwing hardware at the problem.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To
hile it's busy, and that's the situation the "use a UPS"
idea doesn't improve.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@pos
mps, is to dump from both 8.3 and 8.4 clients
and look at the difference between the two, to help get an idea what
changed syntax is responsible for the problems.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
how to restore.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
scale just like IOPS, but that's pretty uncommon.
[Rant about making sure not to drink the storage vendor IOPS Kool-Aid
deleted]
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing li
, and they didn't do very much of that in the
Perc 6 series. They seem to be changing around the newer models more
again, which is always bad news.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent
That can be as important as
the drives when you have larger arrays. The LSI Megaraid card Dell is
using for the Perc6i is quite fast, and you'll need to make sure you get
something just as good for the new server.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Se
ge as you seem to think they are though.
Partitions will still be inherited tables, the improvements are just
going to reduce the amount of setup/trigger code you need to write and
make management tasks easier.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Supp
g/pgsql-committers/2008-12/msg00096.php
It's "database-wide" now instead of "full-database".
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admi
ing that to clean up tables isn't an option)
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://ww
='configuration file'
To figure out all the things that were set at initdb time from a new
running database. You can also look at the postgresql.conf file it
generated and search for lines that are not commented out with "#" at
the start.
--
Greg Smith 2ndQuadrant US Balt
;-k 256" is a reasonable starter setting that
will save the last 4GB of archive logs shipped over on the standby,
which is high enough to make deleting one of them prematurely unlikely
(but not impossible for the right difficult workload).
--
Greg Smith 2ndQuadrant US Baltimore, MD
Pos
Renato Oliveira wrote:
I am going to gather the figures about our database and I will email to the
list, if I am allowed to.
Number of tables, number of transactions per day etc.
A quick snapshot from "vmstat 1" during a busy time is also very helpful.
--
Greg Smith 2ndQ
gram, and by showing where they did much better is suggest
the areas that community Postgres struggles relative to software that
handles parallel query across multiple cores/servers.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.
Rodger Donaldson wrote:
On Tue, March 30, 2010 06:09, Greg Smith wrote:
You answered your own question here. Ramiro is looking for suggestions
for how to scale up to >500 connections at once, and it's not that
likely virtualization can fill any useful role in that context.
ony and have minimal
tolerance for downtime, you really need to come up with a long-term
approach to coping with that from an application architecture
perspective. Unfortunately you're not going to find any quick fix for
that combination of requirements.
--
Greg Smith 2ndQuadrant US
/static/routine-reindex.html
describes that issue, and that bit of documentation and the underlying
behavior is unchanged in later releases. It's much more likely that
you're running into the very common situation instead where you're
running VACUUM FULL infrequently, where
d in business requirements for reliability and the inevitable vendor
preference, you're likely to end up with a stack of possible
configurations you could consider to sort through. There is no generic
recommendation for larger systems.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreS
eability tradeoffs when deciding
if virtualized deployment makes sense, and for smaller workloads it's
easy to dismiss the performance side of things as not a limiting factor
and therefore favor VMs.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g..
tasts to make the caches clear, the only easy
way to make things more clear is to reboot the whole server.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://
80 matches
Mail list logo
