[GENERAL] Load Increase
PostgreSQL 9.0.3 has been running very smooth for us and we have streaming replication running on it as well as WAL archiving. Things have run consistently and we are extremely happy with the performance. During the early morning hours, we have processes that run and import certain data from clients, nothing too crazy: about 4-5 Mb CSV files being imported in. This runs flawlessly, however, this morning the load of the servers were high and a few of the input processes were running for over 2 hours. The load was around 4.00 and stayed there for a while. The import scripts eventually finished and the load went back down, however, any time there was a heavy write, the load would spike. I don't know whether this is because traffic on the database box increased or whether it was Postgres/Kernel related. I saw this in my dmesg: Things appear to be normal but I want to ask: what is a heavy load just by looking at uptime and also what causes the load to increase under reasonably heavy writes? Is it the streaming that could be causing some load increase? Thank you Ogden [3215764.704206] INFO: task postmaster:5087 blocked for more than 120 seconds. [3215764.704236] "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message. [3215764.704281] postmasterD 0 5087 20996 0x [3215764.704285] 88043e46b880 0086 [3215764.704289] 8800144ffe48 8800144ffe48 f9e0 8800144fffd8 [3215764.704293] 00015780 00015780 88043b50d4c0 88043b50d7b8 [3215764.704296] Call Trace: [3215764.704302] [] ? __mutex_lock_common+0x122/0x192 [3215764.704306] [] ? getname+0x23/0x1a0 [3215764.704309] [] ? mutex_lock+0x1a/0x31 [3215764.704314] [] ? virt_to_head_page+0x9/0x2a [3215764.704318] [] ? generic_file_llseek+0x22/0x53 [3215764.704322] [] ? sys_lseek+0x44/0x64 [3215764.704325] [] ? system_call_fastpath+0x16/0x1b [3215764.704328] INFO: task postmaster:5090 blocked for more than 120 seconds. [3215764.704357] "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message. [3215764.704402] postmasterD 0 5090 20996 0x [3215764.704406] 88043e46b880 0082 [3215764.704410] 88001433de48 88001433de48 f9e0 88001433dfd8 [3215764.704414] 00015780 00015780 88043b7569f0 88043b756ce8 [3215764.704418] Call Trace: [3215764.704421] [] ? __mutex_lock_common+0x122/0x192 [3215764.704425] [] ? getname+0x23/0x1a0 [3215764.704428] [] ? mutex_lock+0x1a/0x31 [3215764.704431] [] ? virt_to_head_page+0x9/0x2a [3215764.704435] [] ? generic_file_llseek+0x22/0x53 [3215764.704438] [] ? sys_lseek+0x44/0x64 [3215764.704441] [] ? system_call_fastpath+0x16/0x1b -- 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] Web Hosting
On Mar 6, 2011, at 2:11 AM, Brent Wood wrote: > Rimu hosting allows you to install whatever you want, including Postgres... > which I have done before now. If your project is in support of Open Source > software in any way, ask what discount they can offer, they have been pretty > generous in that arena. > > http://rimuhosting.com/ Something similar is http://www.linode.com/ and even http://www.slicehost.com. I personally have not used either but linode comes with great recommendations from friends. Ogden
Re: [GENERAL] Web Hosting
On Mar 5, 2011, at 7:07 PM, Bret Fledderjohn wrote: > > On 5 March 2011 16:08, matty jones wrote: > I already have a domain name but I am looking for a hosting company that I > can use PG with. The few I have contacted have said that they support MySQL > only and won't give me access to install what I need or they want way to > much. I don't need a dedicated host which so far seems the only way this > will work, all the companies I have researched so far that offer shared > hosting or virtual hosting only use MySQL. I will take care of the setup and > everything myself but I have already written my code using PG/PHP and I have > no intention of switching. > > Thanks. > I am using A2 Hosting (www.a2hosting.com ) which offers 8.4... They are > inexpensive and so far reliable. > Wow, that's super cheap. Is there some catch - $5.57 / month for unlimited everything? No hidden anything? Ogden
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
Thank you for letting me know about pg_controldata. I have been playing around
with this tool.
I notice on my master server I have:
Latest checkpoint location: 1E3/F220
Prior checkpoint location:1E3/F120
Latest checkpoint's REDO location:1E3/F220
And on the slave server (where it is archiving to), I have:
Latest checkpoint location: 1E3/EF20
Prior checkpoint location:1E3/EF20
Latest checkpoint's REDO location:1E3/EF20
These are the main differences - should these match or is this a sign of being
too out of sync? How can I best use this tool?
Thank you
Ogden
On Feb 8, 2011, at 8:47 PM, Ray Stell wrote:
>
> pg_controldata command is helpful.
>
> Archiving wal not required, but you can roll it either way.
>
>
>
>
>
>
> On Tue, Feb 08, 2011 at 04:46:51PM -0600, Ogden wrote:
>> Hello all,
>>
>> I have set up PostgreSQL Streaming Replication and all seems to work fine
>> when updating records as the records are instantaneously updated on the
>> slave, however, I was wondering perhaps if someone can give me some
>> verification that what I am doing is alright or some more insight into what
>> I am doing. Perhaps this will also help others in the future.
>>
>> First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
>>
>>
>> #!/bin/sh
>>
>> LOG_FILE="/tmp/postgres_wal_archiving.log"
>>
>> log() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; }
>> log_error() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; exit 1; }
>>
>> wal_path="$1"
>> wal_file="$2"
>> backup_server="slave01"
>> remote_archive="/var/lib/pgsql/walfiles/$wal_file"
>>
>> log "Transfering file to backup server, filename: $wal_file"
>> rsync "$wal_path" "$backup_server:$remote_archive"
>> if [ "$?" -eq 0 ]; then
>>log "Transfer to slave server completed"
>> else
>>log_error "Sending $wal_file failed."
>> fi
>>
>> On the slave, I create the directory /var/lib/pgsql/walfiles
>> (remote_archive) for the script to copy the walfiles over to.
>>
>> Then, within the master's postgresql.conf I have:
>>
>> wal_level = hot_standby
>> archive_mode = on
>> archive_command = '/var/lib/pgsql/data/standby.sh %p %f > The same script as above
>> archive_timeout = 30
>> max_wal_senders = 5
>> wal_keep_segments = 32
>> #hot_standby = off
>>
>> I start up the master server and verify that files are indeed being SCPed
>> over to /var/lib/pgsql/walfiles (also processes shows: 'archiver process
>> last was 00010003001E').
>>
>> After starting up on the master, I rsync over the data/ directory to the
>> slave:
>>
>> /path/to/psql -c "SELECT pg_start_backup('label', true)"
>> rsync -avz --delete /var/lib/pgsql/data/ slave01:/var/lib/pgsql/data
>> --exclude postmaster.pid
>> /path/to/psql -c "SELECT pg_stop_backup()"
>>
>> And I add recovery.conf over on the the slave's data/ directory:
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=master_ip port=5432 user=postgres'
>> trigger_file = '/tmp/trigger'
>> restore_command='cp /var/lib/pgsql/walfiles/%f "%p"'
>>
>> And in the slave's postgresql.conf, I remove the comment on :
>>
>> hot_standby = on
>>
>> Upon starting the slave, everything works fine and updates to records occur
>> on the slave immediately (what is the actual timing for this)?
>>
>> My confusion is: does streaming replication require WAL archiving as I have
>> illustrated above or is it a "just in case" scenario? Also, the
>> restore_command on the slave - is this correct, assuming that the master is
>> dropping off files via SCP to /var/lib/pgsql/walfiles ?
>>
>> Thank you very much
>>
>> Ogden Nefix
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
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 9.0 Streaming Replication Configuration
On Feb 8, 2011, at 8:47 PM, Ray Stell wrote: > > pg_controldata command is helpful. > > Archiving wal not required, but you can roll it either way. > > That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections). Thank you Ogden -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
Hello all,
I have set up PostgreSQL Streaming Replication and all seems to work fine when
updating records as the records are instantaneously updated on the slave,
however, I was wondering perhaps if someone can give me some verification that
what I am doing is alright or some more insight into what I am doing. Perhaps
this will also help others in the future.
First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
#!/bin/sh
LOG_FILE="/tmp/postgres_wal_archiving.log"
log() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; }
log_error() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; exit 1; }
wal_path="$1"
wal_file="$2"
backup_server="slave01"
remote_archive="/var/lib/pgsql/walfiles/$wal_file"
log "Transfering file to backup server, filename: $wal_file"
rsync "$wal_path" "$backup_server:$remote_archive"
if [ "$?" -eq 0 ]; then
log "Transfer to slave server completed"
else
log_error "Sending $wal_file failed."
fi
On the slave, I create the directory /var/lib/pgsql/walfiles (remote_archive)
for the script to copy the walfiles over to.
Then, within the master's postgresql.conf I have:
wal_level = hot_standby
archive_mode = on
archive_command = '/var/lib/pgsql/data/standby.sh %p %f http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] mod_perl and PostgreSQL 8.3.3 causing "message type 0x44 arrived from server while idle"
Hello all, We upgraded our application servers to Apache 2.2.16 and upgraded our (hand built) Perl of 5.10.1, mod_perl (for Catalyst) and the modules (such as DBI, DBD::Pg) through CPAN. Our PostgreSQL server has not changed at all and it is running 8.3.3. Since the upgrade, we are noticing the following messages in our Apache logs: message type 0x54 arrived from server while idle message type 0x44 arrived from server while idle message type 0x43 arrived from server while idle We have never had this issue before and from searching the little information I found on the topic, it seems that this is thread related. However, what is the culprit here? Apache? mod_perl? Software versions: perl, v5.10.1 (*) built for x86_64-linux-thread-multi and PostgreSQL 8.3.3. Any help is appreciated. Thank you Ogden -- 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] Warm Standby Setup Documentation
On Mar 28, 2010, at 7:45 PM, Yar Tykhiy wrote: > On Fri, Mar 26, 2010 at 01:35:43PM -0500, Ogden wrote: >> On Mar 26, 2010, at 1:32 PM, Greg Smith wrote: >> >>> Bryan Murphy wrote: >>>> The one thing you should be aware of is that when you fail over, your >>>> spare has no spares. I have not found a way around this problem yet. So, >>>> when you fail over, there is a window where you have no backups while >>>> you're building the new spares. This can be pretty nerve wracking if your >>>> database is like ours and it takes 3-6 hours to bring a new spare online >>>> from scratch. >>> >>> If there's another server around, you can have your archive_command on the >>> master ship to two systems, then use the second one as a way to jump-start >>> this whole process. After fail-over, just start shipping from the new >>> primary to that 3rd server, now the replacement standby, and sync any files >>> it doesn't have. Then switch it into recovery. Much faster than doing a >>> new base backup from the standby on larger systems. >> >> How is it possible to use the archive_command to ship to different ones? >> >> archive_command = 'rsync -a %p >> postg...@192.168.x.x:/usr/local/pgsql/walfiles/%f > archive_timeout = 120# force a logfile segment switch after >> this >> >> I suppose you can put multiple commands there then? > > You can always wrap as many commands as you like in a script. > However, there is a pitfall to watch out for when shipping WALs to > multiple standby servers. Namely your script has to handle failures > of individual WAL shipping targets so that a single target going down > doesn't disrupt operation of the whole cluster. Please see > http://archives.postgresql.org/pgsql-general/2009-10/msg00590.php > for discussion. Is it as simple as doing this: archive_command = '/var/lib/pgsql/data/warm_standby.sh %p %f http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm Standby Setup Documentation
On Mar 26, 2010, at 1:32 PM, Greg Smith wrote: > Bryan Murphy wrote: >> The one thing you should be aware of is that when you fail over, your spare >> has no spares. I have not found a way around this problem yet. So, when >> you fail over, there is a window where you have no backups while you're >> building the new spares. This can be pretty nerve wracking if your database >> is like ours and it takes 3-6 hours to bring a new spare online from scratch. > > If there's another server around, you can have your archive_command on the > master ship to two systems, then use the second one as a way to jump-start > this whole process. After fail-over, just start shipping from the new > primary to that 3rd server, now the replacement standby, and sync any files > it doesn't have. Then switch it into recovery. Much faster than doing a new > base backup from the standby on larger systems. How is it possible to use the archive_command to ship to different ones? archive_command = 'rsync -a %p postg...@192.168.x.x:/usr/local/pgsql/walfiles/%f http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Warm Standby Setup Documentation
I have looked all over but could not find any detailed docs on setting up a warm standby solution using PostgreSQL 8.4. I do know of http://www.postgresql.org/docs/8.4/static/warm-standby.html but was wondering if there was a more detailed document on this topic. Are people using this as a viable backup/hot spare solution? How has it worked out? Thank you Ogden -- 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] Optimizations
On Mar 5, 2010, at 2:26 AM, Craig Ringer wrote: > Ogden wrote: >> We run a student scoring system with PostgreSQL as a backend. After the >> results for each student are inputted into the system, we display many >> reports for them. We haven't had a problem with efficiency or speed, but it >> has come up that perhaps storing the rolled up scores of each student may be >> better than calculating their score on the fly. I have always coded the SQL >> to calculate on the fly and do not see any benefit from calculating on the >> fly. For a test with over 100 questions and with 950 students having taken >> it, it calculates all their relevant score information in less than half a >> second. Would there be any obvious benefit to caching the results? > > Caching the results would mean storing the same information in two > places (individual scores, and aggregates calculated from them). That's > room for error if they're permitted to get out of sync in any way for > any reason. For that reason, and because it's complexity you don't need, > I'd avoid it unless I had a reason not to. > > On the other hand if you expect the number of students you have to > report on to grow vastly then it's worth considering. > > If you do go ahead with it, first restructure all queries that use that > information so they go view a view that calculates that data on the fly. > > Then look at replacing that view with a table that's automatically > updated by triggers when the data source is updated (say, a student has > a new score recorded). Craig, Thank you for the response and insight. While it sounds good in practice, I know storing the results will vastly increase the size (the table holding the results is over 5Gb in one case) and calculating results from it takes not more than a second for a huge data set. Would searching a huge table be as fast as calculating or about the same? I'll have to run some tests on my end but I am very impressed by the speed of which PostgreSQL executes aggregate functions. Do you suggest looking at this option when we see the reporting to slow down? At that point do you suggest we go back to the drawing board? Thank you Ogden -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimizations
We run a student scoring system with PostgreSQL as a backend. After the results for each student are inputted into the system, we display many reports for them. We haven't had a problem with efficiency or speed, but it has come up that perhaps storing the rolled up scores of each student may be better than calculating their score on the fly. I have always coded the SQL to calculate on the fly and do not see any benefit from calculating on the fly. For a test with over 100 questions and with 950 students having taken it, it calculates all their relevant score information in less than half a second. Would there be any obvious benefit to caching the results? I would greatly appreciate any thoughts on this. Here is the structure: A database to store and calculate student results. Information about the student and which test they took: \d test_registration; Table "public.test_registration" Column|Type | Modifiers -+-+ id | uuid| not null sid | character varying(36) | not null created_date| timestamp without time zone | not null default now() modified_date | timestamp without time zone | not null test_administration | uuid| not null The actual results (what the student marked): \d test_registration_result (linked to test_registration.id above) Table "public.test_registration_result" Column | Type | Modifiers ---+---+--- test_registration | uuid | not null question | uuid | not null answer| character varying(15) | \d question (information on each question) Table "public.question" Column | Type | Modifiers ---++--- id| uuid | not null test | uuid | not null question | integer| not null weight| double precision | \d question_answer (the answers for the question) Table "public.question_answer" Column | Type | Modifiers --+---+--- question | uuid | not null answer | character varying(15) | not null With a SQL query: SELECT sid, raw_score, weighted_score, number_questions, total_weights, ( weighted_score / total_weights ) * 100.00 as mp_percentage, total_weights FROM ( SELECT tr.sid as sid, sum ( ( SELECT (case when a.answer = r.answer then 1 else 0 end ) ) ) as raw_score, sum ( ( SELECT (case when a.answer = r.answer THEN q.weight end ) ) ) as weighted_score, . For 953 students on a test with 145 questions, this takes less than half a second to calculate. Is is worth storing the score? \d score_set Table "public.score_set" Column |Type | Modifiers +-+ id | uuid| not null sid| uuid| not null test_registration_id | uuid| not null test_administration_id | uuid| not null score | double precision| not null Will it be much faster? I know more storage will be needed. Thank you Ogden -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
