Re: [PERFORM] pg_xlog dir not getting swept

[Niels Kristian Schjødt]

Thanks, after a few more hours of waiting, things started get cleaned up. 
Things are back in order now.

Niels Kristian Schjødt
Co-founder & Developer

E-Mail: nielskrist...@autouncle.com <mailto:nielskrist...@autouncle.com>
Mobile: +45 28 73 04 93




www.autouncle.com <http://www.autouncle.com/>
Follow us: Facebook <https://www.facebook.com/AutoUncle>  |  Google+ 
<https://plus.google.com/+AutoUncle>  |  LinkedIn 
<http://www.linkedin.com/company/autouncle>  |  Twitter 
<https://twitter.com/AutoUncle>  
Get app for: iPhone & iPad 
<https://itunes.apple.com/en/app/autouncle/id533433816?mt=8>  |  Android 
<https://play.google.com/store/apps/details?id=com.autouncle.autouncle>




> Den 29. jun. 2016 kl. 21.19 skrev Jeff Janes :
> 
> On Wed, Jun 29, 2016 at 3:00 AM, Niels Kristian Schjødt
>  wrote:
>> About a day ago, there seems to have been some trouble in the network of my
>> database (postgresql 9.3).
>> 
>> I’m running my db with a streaming replication setup with wall shipping.
>> 
>> I sync wal logs to a mounted networkdrive using archive_command = 'rsync -a
>> %p /mnt/wal_drive/wals/%f > leading to my pg_xlog dir building up (590Gb). I rebooted the server, and
>> the archiving command seems to succeed now - however - After about an hour
>> of running, the pg_xlog drive has not decreased in size - I would have
>> expect that! I can see that lot’s of files get’s synced to the
>> /mnt/wal_drive/wals dir, but somehow the pg_xlog dir is not swept (yet)?
>> Will this happen automatically eventually, or do I need to do something
>> manually?
> 
> Successfully archived files are only removed by the checkpointer.  The
> logic is quite complex and it can be very frustrating trying to
> predict exactly when any given file will get removed.  You might want
> to run a few manual checkpoints to see if that cleans it up.  But turn
> on log_checkpoints and reload the configuration first.
> 
> Cheers,
> 
> Jeff

[PERFORM] pg_xlog dir not getting swept

[Niels Kristian Schjødt]

About a day ago, there seems to have been some trouble in the network of my 
database (postgresql 9.3). 

I’m running my db with a streaming replication setup with wall shipping. 

I sync wal logs to a mounted networkdrive using archive_command = 'rsync -a %p 
/mnt/wal_drive/wals/%f http://www.hivelogik.com/blog/?p=513, but I’m unsure 
if it’s necessary and if it can be dangerous?

Best

Re: [PERFORM] Guidelines on best indexing strategy for varying searches on 20+ columns

[Niels Kristian Schjødt]

Thanks for the answers.

> Den 30/06/2014 kl. 20.04 skrev Jeff Janes :
> 
> On Wed, Jun 25, 2014 at 1:48 AM, Niels Kristian Schjødt
>  wrote:
>> Hi,
>> I’m running a search engine for cars. It’s backed by a postgresql 9.3 
>> installation.
>> 
>> Now I’m unsure about the best approach/strategy on doing index optimization 
>> for the fronted search.
>> 
>> The problem:
>> 
>> The table containing the cars holds a around 1,5 million rows. People that 
>> searches for cars needs different criteria to search by. Some search by 
>> brand/model, some by year, some by mileage, some by price and some by 
>> special equipment etc. etc. - and often they combine a whole bunch of 
>> criteria together. Of cause some, like brand/mode and price, are used more 
>> frequently than others. In total we offer: 9 category criteria like 
>> brand/model or body type, plus 5 numeric criteria like price or mileage, 
>> plus 12 boolean criteria like equipment. Lastly people can order the results 
>> by different columns (year, price, mileage and a score we create about the 
>> cars). By default we order by our own generated score.
>> 
>> What I’ve done so far:
>> 
>> I have analyzed the usage of the criteria “lightly”, and created a few 
>> indexes (10). Among those, are e.g. indexes on price, mileage and a combined 
>> index on brand/model. Since we are only interested in showing results for 
>> cars which is actually for sale, the indexes are made as partial indexes on 
>> a sales state column.
> 
> I'd probably partition the data on whether it is for sale, and then
> search only the for-sale partition.

Hmm okay, I already did all the indexes partial based on the for-sales state. 
If the queries always queries for-sale, and all indexes are partial based on 
those, will it then still help performance / make sense to partition the tables?
> 
>> 
>> Questions:
>> 
>> 1. How would you go about analyzing and determining what columns should be 
>> indexed, and how?
> 
> I'd start out with intuition about which columns are likely to be used
> most often, and in a selective way.  And followup by logging slow
> queries so they can be dissected at leisure.
> 
>> 2. What is the best strategy when optimizing indexes for searches happening 
>> on 20 + columns, where the use and the combinations varies a lot? (To just 
>> index everything, to index some of the columns, to do combined indexes, to 
>> only do single column indexes etc. etc.)
> 
> There is no magic index.  Based on your description, you are going to
> be seq scanning your table a lot.  Focus on making it as small as
> possible, but vertical partitioning it so that the not-for-sale
> entries are hived off to an historical table, and horizontally
> partitioning it so that large columns rarely used in the where clause
> are in a separate table (Ideally you would tell postgresql to
> aggressively toast those columns, but there is no knob with which to
> do that)
> 
> 
>> 3. I expect that it does not make sense to index all columns?
> 
> You mean individually, or jointly?  Either way, probably not.
> 
>> 4. I expect it does not make sense to index boolean columns?
> 
> In some cases it can, for example if the data distribution is very
> lopsided and the value with the smaller side is frequently specified.
> 
>> 5. Is it better to do a combined index on 5 frequently used columns rather 
>> than having individual indexes on each of them?
> 
> How often are the columns specified together?  If they are completely
> independent it probably makes little sense to index them together.
> 
>> 6. Would it be a goof idea to have all indexes sorted by my default sorting?
> 
> You don't get to choose.  An btree index is sorted by the columns
> specified in the index, according to the operators specified (or
> defaulted).  Unless you mean that you want to add the default sort
> column to be the lead column in each index, that actually might make
> sense.
> 
>> 7. Do you have so experiences with other approaches that could greatly 
>> improve performance (e.g. forcing indexes to stay in memory etc.)?
> 
> If your queries are as unstructured as you imply, I'd forget about
> indexes for the most part, as you will have a hard time findings ones
> that work.  Concentrate on making seq scans as fast as possible.  If
> most of your queries end in something like "ORDER by price limit 10"
> then concentrate on index scans over price.  You will probably want to
> include heuristics in your UI such that if people configure queries to
> download half your database, you disallow that.  You will probably
> find that 90% of the workload comes from people who are just playing
> around with your website and don't actually intend to do business with
> you.
> 
> Cheers,
> 
> Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Guidelines on best indexing strategy for varying searches on 20+ columns

[Niels Kristian Schjødt]

Thanks for your suggestions, very useful. See comments inline:

Den 25/06/2014 kl. 23.48 skrev Merlin Moncure :

> On Wed, Jun 25, 2014 at 3:48 AM, Niels Kristian Schjødt
>  wrote:
>> Hi,
>> I’m running a search engine for cars. It’s backed by a postgresql 9.3 
>> installation.
>> 
>> Now I’m unsure about the best approach/strategy on doing index optimization 
>> for the fronted search.
>> 
>> The problem:
>> 
>> The table containing the cars holds a around 1,5 million rows. People that 
>> searches for cars needs different criteria to search by. Some search by 
>> brand/model, some by year, some by mileage, some by price and some by 
>> special equipment etc. etc. - and often they combine a whole bunch of 
>> criteria together. Of cause some, like brand/mode and price, are used more 
>> frequently than others. In total we offer: 9 category criteria like 
>> brand/model or body type, plus 5 numeric criteria like price or mileage, 
>> plus 12 boolean criteria like equipment. Lastly people can order the results 
>> by different columns (year, price, mileage and a score we create about the 
>> cars). By default we order by our own generated score.
>> 
>> What I’ve done so far:
>> 
>> I have analyzed the usage of the criteria “lightly”, and created a few 
>> indexes (10). Among those, are e.g. indexes on price, mileage and a combined 
>> index on brand/model. Since we are only interested in showing results for 
>> cars which is actually for sale, the indexes are made as partial indexes on 
>> a sales state column.
>> 
>> Questions:
>> 
>> 1. How would you go about analyzing and determining what columns should be 
>> indexed, and how?
> 
> mainly frequency of access.
> 
>> 2. What is the best strategy when optimizing indexes for searches happening 
>> on 20 + columns, where the use and the combinations varies a lot? (To just 
>> index everything, to index some of the columns, to do combined indexes, to 
>> only do single column indexes etc. etc.)
> 
> don't make 20 indexes.   consider installing pg_trgm (for optimized
> LIKE searching) or hstore (for optmized key value searching) and then
> using GIST/GIN for multiple attribute search.  with 9.4 we have
> another fancy technique to explore: jsonb searching via GIST/GIN.

Interesting, do you have any good resources on this approach?
> 
>> 3. I expect that it does not make sense to index all columns?
> 
> well, maybe.  if you only ever search one column at a time, then it
> might make sense.  but if you need to search arbitrary criteria and
> frequently combine a large number, then no -- particularly if your
> dataset is very large and individual criteria are not very selective.

So, to just clarify: I’m often combining a large number of search criteria and 
the individual criteria is often not very selective, in that case, are you 
arguing for or against indexing all columns? :-)
> 
>> 4. I expect it does not make sense to index boolean columns?
> 
> in general, no.  an important exception is if you are only interested
> in true or false and the number of records that have that interesting
> value is tiny relative to the size of the table.  in that case, a
> partial index can be used for massive optimization.

Thanks, hadn’t been thinking about using partial indexes here as an option.
> 
>> 5. Is it better to do a combined index on 5 frequently used columns rather 
>> than having individual indexes on each of them?
> 
> Only if you search those 5 columns together a significant portion of the time.
> 
>> 6. Would it be a goof idea to have all indexes sorted by my default sorting?
> 
> index order rarely matters.  if you always search values backwards and
> the table is very large you may want to consider it.  unfortunately
> this often doesn't work for composite indexes so sometimes we must
> explore the old school technique of reversing the value.
> 
>> 7. Do you have so experiences with other approaches that could greatly 
>> improve performance (e.g. forcing indexes to stay in memory etc.)?
> 
> as noted above, fancy indexing is the first place to look.   start
> with pg_trgm (for like optmization), hstore, and the new json stuff.
> the big limitation you will hit is that that most index strategies, at
> least fo the prepackaged stuff will support '=', or partial string
> (particularly with pg_trgm like), but not > or <: for range operations
> you have to post process the search or try to work the index from
> another angle.
> 
> merlin

[PERFORM] Guidelines on best indexing strategy for varying searches on 20+ columns

[Niels Kristian Schjødt]

Hi,
I’m running a search engine for cars. It’s backed by a postgresql 9.3 
installation. 

Now I’m unsure about the best approach/strategy on doing index optimization for 
the fronted search.

The problem:

The table containing the cars holds a around 1,5 million rows. People that 
searches for cars needs different criteria to search by. Some search by 
brand/model, some by year, some by mileage, some by price and some by special 
equipment etc. etc. - and often they combine a whole bunch of criteria 
together. Of cause some, like brand/mode and price, are used more frequently 
than others. In total we offer: 9 category criteria like brand/model or body 
type, plus 5 numeric criteria like price or mileage, plus 12 boolean criteria 
like equipment. Lastly people can order the results by different columns (year, 
price, mileage and a score we create about the cars). By default we order by 
our own generated score.

What I’ve done so far:

I have analyzed the usage of the criteria “lightly”, and created a few indexes 
(10). Among those, are e.g. indexes on price, mileage and a combined index on 
brand/model. Since we are only interested in showing results for cars which is 
actually for sale, the indexes are made as partial indexes on a sales state 
column.

Questions: 

1. How would you go about analyzing and determining what columns should be 
indexed, and how?
2. What is the best strategy when optimizing indexes for searches happening on 
20 + columns, where the use and the combinations varies a lot? (To just index 
everything, to index some of the columns, to do combined indexes, to only do 
single column indexes etc. etc.)
3. I expect that it does not make sense to index all columns?
4. I expect it does not make sense to index boolean columns?
5. Is it better to do a combined index on 5 frequently used columns rather than 
having individual indexes on each of them?
6. Would it be a goof idea to have all indexes sorted by my default sorting?
7. Do you have so experiences with other approaches that could greatly improve 
performance (e.g. forcing indexes to stay in memory etc.)?




 

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sudden crazy high CPU usage

[Niels Kristian Schjødt]

Sorry, but nothing unusual here either, I have compared the time just before 
with the same time the days before and the throughput pattern is exactly the 
same. No differences.


Den 31/03/2014 kl. 22.01 skrev Will Platnick :

> 
> In New Relic, go back a half hour before the problem started so you can't see 
> that this spike happened and send the same screenshot in. My guess is you 
> have increased activity hitting the DB. Do you have pgbouncer or some kind of 
> connection pooling sitting in front? 198 open server connections could 
> account for an increase in load like you're seeing. Do you have postgresql 
> addon in New Relic to show you how many queries are hitting the system to 
> correlate data to?
> 
> On Mon, Mar 31, 2014 at 1:36 PM, Sergey Konoplev  wrote:
> On Mon, Mar 31, 2014 at 3:25 AM, Niels Kristian Schjødt
>  wrote:
> > I'm running postgresql 9.3 on a production server. An hour ago, out of the 
> > "blue", I ran into an issue I have never encountered before: my server 
> > started to use CPU as crazy. The server is a standard ubuntu 12.04 LTE 
> > installation running only Postgres and Redis.
> >
> > The incident can be seen on the in numbers below:
> >
> > https://s3-eu-west-1.amazonaws.com/autouncle-public/other/cpu.png
> 
> The increase doesn't look so sudden. My guess is that the server got
> some new activity. The advice is to setup the statistics collecting
> script by the link [1] and review the results for a period of hour or
> so. It shows charts of statements by CPU/IO/calls with aggregated
> stats, so you could probably find out more than with pure
> pg_stat_statements.
> 
> [1] 
> https://github.com/grayhemp/pgcookbook/blob/master/statement_statistics_collecting_and_reporting.md
> 
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
> 
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray...@gmail.com
> 
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 

Re: [PERFORM] Sudden crazy high CPU usage

[Niels Kristian Schjødt]

Thanks, I don’t think overheating is an issue, it’s a large dell server, and I 
have checked the historic CPU temperature in the servers control panel, and no 
overheating has shown.

Zone_reclaim_mode is already set to 0 

Den 31/03/2014 kl. 16.50 skrev Scott Marlowe :

> On Mon, Mar 31, 2014 at 8:24 AM, Niels Kristian Schjødt
>  wrote:
>> 
>> Thanks, this seems to persist after a reboot of the server though, and I 
>> have never in my server's 3 months life time experienced anything like it.
> 
> Could it be overheating and therefore throttling the cores?
> 
> Also another thing to look at on large memory machines with > 1 CPU
> socket is zone_reclaim_mode being set to 1. Always set it to 0 on a
> linux machine running postgres.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sudden crazy high CPU usage

[Niels Kristian Schjødt]

Yes, I could install “perf”, though I’m not familiar with it. What would i do? :-)
Niels Kristian SchjødtCo-founder & DeveloperE-Mail: nielskrist...@autouncle.comMobile: 0045 28 73 04 93www.autouncle.comFollow us: Facebook  |  Google+  |  LinkedIn  |  Twitter  Get app for: iPhone & iPad  |  Android

Den 31/03/2014 kl. 16.36 skrev Merlin Moncure :perf

Re: [PERFORM] Sudden crazy high CPU usage

[Niels Kristian Schjødt]

Thanks, this seems to persist after a reboot of the server though, and I have never in my server’s 3 months life time experienced anything like it.
Niels Kristian SchjødtCo-founder & DeveloperE-Mail: nielskrist...@autouncle.comMobile: 0045 28 73 04 93www.autouncle.comFollow us: Facebook  |  Google+  |  LinkedIn  |  Twitter  Get app for: iPhone & iPad  |  Android

Den 31/03/2014 kl. 15.47 skrev Merlin Moncure <mmonc...@gmail.com>:On Mon, Mar 31, 2014 at 5:25 AM, Niels Kristian Schjødt<nielskrist...@autouncle.com> wrote:I'm running postgresql 9.3 on a production server. An hour ago, out of the "blue", I ran into an issue I have never encountered before: my server started to use CPU as crazy. The server is a standard ubuntu 12.04 LTE installation running only Postgres and Redis.The incident can be seen on the in numbers below:https://s3-eu-west-1.amazonaws.com/autouncle-public/other/cpu.pngI imidiatly took a look at pg_stat_activity but nothing in there seemed suspicious. I also had a look at the postgres log, but nothing was in there too. I have pg_stat_statements running, so I reseted that one, and nothing really suspicious occurred in there, expect for the fact, that all queries were taking 100x times longer than usual.I have tried the following with no luck:    * Restart clients connecting to the db    * Restart postgres    * Restart the whole serverI have run memory tests on the server as well, and nothing seems to be wrong.No changes in any software running on the servers has been made within the last 24 hours.The question is: I have a streaming replication server running, which I have now done a failover to, and it runs fine. However I still have no clue why my master suddenly has become so CPU consuming, and how I can debug / trace it further down?Using linux 6? One possible culprit is "Transparent Huge PageCompaction".  It tends to hit severs with a lot of memory, especiallyif they've configured a lot of shared buffers.  Google it a for a lotof info.There may be other issues masquerading as this one but it's the firstthing to rule out.  Symptoms are very high cpu utilization and poorperformance that strikes without warning and then resolves alsowithout warning (typically seconds or minutes after the event).For starters, take a look at the value of:/sys/kernel/mm/redhat_transparent_hugepage/enabledAnd do some due diligence research.merlin

[PERFORM] Sudden crazy high CPU usage

[Niels Kristian Schjødt]

I'm running postgresql 9.3 on a production server. An hour ago, out of the 
"blue", I ran into an issue I have never encountered before: my server started 
to use CPU as crazy. The server is a standard ubuntu 12.04 LTE installation 
running only Postgres and Redis.

The incident can be seen on the in numbers below: 

https://s3-eu-west-1.amazonaws.com/autouncle-public/other/cpu.png

I imidiatly took a look at pg_stat_activity but nothing in there seemed 
suspicious. I also had a look at the postgres log, but nothing was in there 
too. I have pg_stat_statements running, so I reseted that one, and nothing 
really suspicious occurred in there, expect for the fact, that all queries were 
taking 100x times longer than usual.

I have tried the following with no luck:

• Restart clients connecting to the db
• Restart postgres
• Restart the whole server

I have run memory tests on the server as well, and nothing seems to be wrong.

No changes in any software running on the servers has been made within the last 
24 hours.

The question is: I have a streaming replication server running, which I have 
now done a failover to, and it runs fine. However I still have no clue why my 
master suddenly has become so CPU consuming, and how I can debug / trace it 
further down?

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Optimal settings for RAID controller - optimized for writes

[Niels Kristian Schjødt]

Hi,

I’m kind of a noob when it comes to setting up RAID controllers and tweaking 
them so I need some advice here.

I’m just about to setup my newly rented DELL R720 12. gen server. It’s running 
a single Intel Xeon E5-2620 v.2 processor and 64GB ECC ram. I have installed 8 
300GB SSDs in it. It has an PERC H710 raid controller (Based on the LSI SAS 
2208 dual core ROC). 

Now my database should be optimized for writing. UPDATEs are by far my biggest 
bottleneck.

Firstly: Should I just put all 8 drives in one single RAID 10 array, or would 
it be better to have the 6 of them in one RAID 10 array, and then the remaining 
two in a separate RAID 1 array e.g. for having WAL log dir on it’s own drives?

Secondly: Now what settings should I pay attention to when setting this up, if 
I wan’t it to have optimal write performance (cache behavior, write back etc.)?

THANKS!

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] How to optimization database for heavy I/O from updates (software and hardware)

[Niels Kristian Schjødt]

I'm in the process of taking the next leap in performance optimization of our 
database, I just need some good advice on my journey. I posted the full 
question with images here on stackexchange if someone would be interested in 
commenting / answering it would be great!

Regards Niels Kristian

http://dba.stackexchange.com/questions/49984/how-to-optimization-database-for-heavy-i-o-from-updates-software-and-hardware

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Hstore VS. JSON

[Niels Kristian Schjødt]

Hi,

I'm in the process of implementing a table for storing some raw data in the 
format of a hash containing one level of keys and values. The hash can be quite 
big (up to 50 keys pointing at values varying from one to several hundred 
characters)

Now, I'm in doubt whether to use JSON or Hstore for this task. Here is the 
facts:

- I'm not going to search a lot (if any) in the data stored in the column, i'm 
only going to load it out.
- The data is going to be heavily updated (not only inserted). Keys and values 
are going to be added/overwritten quite some times.
- My database's biggest current issue is updates, so i don't want that to be a 
bottle neck.
- I'm on postgresql 9.2 

So, question is: Which will be better performance wise, especially for updates? 
Does the same issues with updates on the MVCC structure apply to updates in 
Hstore? What is taking up most space on the HDD?

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Fillfactor in postgresql 9.2

[Niels Kristian Schjødt]

Hi, 
I am experiencing a similar issue as the one mentioned in this post 
http://stackoverflow.com/questions/3100072/postgresql-slow-on-a-large-table-with-arrays-and-lots-of-updates/3100232#3100232
However the post is written for a 8.3 installation, so I'm wondering if the 
fillfactor problem is still roughly the same in 9.2, and hence would have a 
similar effect when adjusted?

Regards Niels Kristian 

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Solved it - thanks!

Den 10/06/2013 kl. 20.24 skrev Jeff Janes :

> On Mon, Jun 10, 2013 at 11:02 AM, Niels Kristian Schjødt 
>  wrote:
> Okay, cool
> 
> You mean that I should do the following right?:
> 
> 1. Stop slave server
> 
> 
> At this point, you don't have a slave server.  Not a usable one, anyway.  If 
> you used to have a hot-standby server, it is now simply a historical 
> reporting server.  If you have no need/use for such a reporting server, then 
> yes you should stop it, to avoid confusion.
> 
>  
> 2. set archive_command = 'true' in postgresql.conf on the master server
> 3. restart master server
> 
> You can simply do a reload rather than a full restart.
>  
> 4. run psql -c "SELECT pg_start_backup('label', true)" on master
> 
> No, you shouldn't do that yet without first having correctly functioning 
> archiving back in place.  After setting archive_command=true and reloading 
> the server, you have to wait a while for the "bad" WAL files to get 
> pseudo-archived and cleared from the system.  Once that has happened, you can 
> then return archive_command to its previous setting, and again reload/restart 
> the server.  Only at that point should you begin taking the new backup.  In 
> other words, steps 7 and 8 have to be moved up to before step 4.
>  
> 5. run rsync -av --exclude postmaster.pid --exclude pg_xlog 
> /var/lib/postgresql/9.2/main/ 
> postgres@192.168.0.2:/var/lib/postgresql/9.2/main/" on master server
> 6. run psql -c "SELECT pg_stop_backup();" on master server
> 7. change archive_command back on master
> 8. restart master
> 9. start slave
> 
> Just to confirm the approach :-)
> 
> 
> Cheers,
> 
> Jeff
> 
> 

Re: [PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Thanks,

> No, you shouldn't do that yet without first having correctly functioning 
> archiving back in place.  After setting archive_command=true and reloading 
> the server, you have to wait a while for the "bad" WAL files to get 
> pseudo-archived and cleared from the system.

How do I know when this is done?



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Okay, cool

You mean that I should do the following right?:

1. Stop slave server
2. set archive_command = 'true' in postgresql.conf on the master server
3. restart master server
4. run psql -c "SELECT pg_start_backup('label', true)" on master
5. run rsync -av --exclude postmaster.pid --exclude pg_xlog 
/var/lib/postgresql/9.2/main/ 
postgres@192.168.0.2:/var/lib/postgresql/9.2/main/" on master server
6. run psql -c "SELECT pg_stop_backup();" on master server
7. change archive_command back on master
8. restart master
9. start slave

Just to confirm the approach :-)



Den 10/06/2013 kl. 19.53 skrev Jeff Janes :

> On Mon, Jun 10, 2013 at 8:35 AM, Niels Kristian Schjødt 
>  wrote:
> 
> WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
> archived (1920 seconds elapsed)
> HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
> can be canceled safely, but the database backup will not be usable without 
> all the WAL segments.
> 
> When looking at ps aux on the master, I see the following:
> 
> postgres 30930  0.0  0.0  98412  1632 ?Ss   15:59   0:02 postgres: 
> archiver process   failed on 00020E1B00A9
> 
> The file mentioned is the one that it was about to archive, when the standby 
> server failed. Somehow it must still be trying to "catch up" from that file 
> which of cause isn't there any more, since I had to remove those in order to 
> get more space on the HDD.
> 
> So the archive_command is failing because it is trying to archive a file that 
> no longer exists.
> 
> One way around this is to remove the .ready files from the 
> pg_xlog/archive_status directory, which correspond to the WAL files you 
> manually removed.  
> 
> Another way would be to temporarily replace the archive_command with one that 
> will report success even when the archiving fails, until the archiver gets 
> paste this stretch.  In fact you could just replace the command with 'true', 
> so it reports success without even doing anything.
> 
> Cheers,
> 
> Jeff

Re: [PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Den 10/06/2013 kl. 17.51 skrev bricklen :

> 
> On Mon, Jun 10, 2013 at 8:35 AM, Niels Kristian Schjødt 
>  wrote:
> I can't seem to figure out which steps I need to do, to get the standby 
> server wiped and get it started as a streaming replication again from 
> scratch. I tried to follow the steps, from step 6, in here 
> http://wiki.postgresql.org/wiki/Streaming_Replication but the process seems 
> to fail when I reach the point where I try to do a psql -c "SELECT 
> pg_stop_backup()". It just says:
> 
> 
> 
> If you use pg_basebackup you don't need to manually put the master into 
> backup mode.
> Be aware that if you are generating a lot of WAL segments and your filesystem 
> backup is large (and takes a while to ship to the slave), you will need to 
> set "wal_keep_segments" quite high on the master to prevent the segments from 
> disappearing during the setup of the slave -- or at least that's the case 
> when you use "--xlog-method=stream".
> 

Okay thanks,
I did the base backup, and I ran the rsync command and it succeeded. However 
then I try to do pg_stop_backup() it just "hangs" and I have a feeling, that 
it's rather because of some information mismatch than actual loading time, 
since nothing is transferred to the slave and I keep on seeing that "postgres 
30930  0.0  0.0  98412  1632 ?Ss   15:59   0:02 postgres: archiver 
process   failed on 00020E1B00A9" in the process overview, and I 
know that exactly that file was the one it has been trying to sync ever since 
the connection dropped. I saw something in here 
http://postgresql.1045698.n5.nabble.com/safe-to-clear-pg-xlog-archive-status-directory-td5738029.html,
 about wiping the pg_xlog/archive_status directly in order to "reset" the sync 
between the servers before running the pg_backup_start(), but I'm unsure if 
it's right, and when I would do it…

Re: [PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Den 10/06/2013 kl. 16.36 skrev bricklen :

> On Mon, Jun 10, 2013 at 4:29 AM, Niels Kristian Schjødt 
>  wrote:
> 
> 2013-06-10 11:21:45 GMT FATAL:  could not connect to the primary server: 
> could not connect to server: No route to host
> Is the server running on host "192.168.0.4" and accepting
> TCP/IP connections on port 5432?
> 
> Did anything get changed on the standby or master around the time this 
> message started occurring?
> On the master, what do the following show?
> show port;
> show listen_addresses;
> 
> The master's IP is still 192.168.0.4?
> 
> Have you tried connecting to the master using something like:
> psql -h 192.168.0.4 -p 5432 -U postgres -d postgres
>  
> Does that throw a useful error or warning?
> 

It turned out that the switch port that the server was connected to was faulty, 
and hence no successful connection between master and slave was established. 
This resolved in pg_xlog building up very fast, because our system performs a 
lot of changes on the data we store. 

I ended up running pg_archivecleanup on the master to get some space freed 
urgently. Then I got the switch changed with a new one. Now I'm trying to the 
streaming replication setup from scratch again, but with no luck.

I can't seem to figure out which steps I need to do, to get the standby server 
wiped and get it started as a streaming replication again from scratch. I tried 
to follow the steps, from step 6, in here 
http://wiki.postgresql.org/wiki/Streaming_Replication but the process seems to 
fail when I reach the point where I try to do a psql -c "SELECT 
pg_stop_backup()". It just says:

NOTICE:  pg_stop_backup cleanup done, waiting for required WAL segments to be 
archived
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (60 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (120 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (240 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (480 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (960 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (1920 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.

When looking at ps aux on the master, I see the following:

postgres 30930  0.0  0.0  98412  1632 ?Ss   15:59   0:02 postgres: 
archiver process   failed on 00020E1B00A9

The file mentioned is the one that it was about to archive, when the standby 
server failed. Somehow it must still be trying to "catch up" from that file 
which of cause isn't there any more, since I had to remove those in order to 
get more space on the HDD. Instead of trying to catch up from the last 
succeeded file, I want it to start over from scratch with the replication - I 
just don't know how.

[PERFORM] URGENT issue: pg-xlog growing on master!

[Niels Kristian Schjødt]

Hi, My pg_xlog dir has been growing rapidly the last 4 days, and my disk is now 
almost full (1000Gb) even though the database is only 50Gb. I have a streaming 
replication server running, and in the log of the slave it says:

cp: cannot stat `/var/lib/postgresql/9.2/wals/00020E1B00A9': No 
such file or directory
cp: cannot stat `/var/lib/postgresql/9.2/wals/00020E1B00A9': No 
such file or directory
2013-06-10 11:21:45 GMT FATAL:  could not connect to the primary server: could 
not connect to server: No route to host
Is the server running on host "192.168.0.4" and accepting
TCP/IP connections on port 5432?

All the time. 

I have tried to restart the server, but that didn't help. I checked the master, 
and the file /var/lib/postgresql/9.2/wals/00020E1B00A9 does not 
exist! I'm pretty lost here, can someone help me solve this and get my master 
server cleaned up. What is causing this, and what do I need to do?

Kind regards

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Best practice when reindexing in production

[Niels Kristian Schjødt]

Thanks

Can you think of a way to select all the indexes programmatically from a table 
and run CREATE INDEX CONCURRENTLY for each of them, so that I don't have to 
hardcode every index name + create statement ?



Den 29/05/2013 kl. 14.26 skrev Magnus Hagander :

> On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt
>  wrote:
>> Hi,
>> 
>> I have a database with quite some data (millions of rows), that is heavily 
>> updated all the time. Once a day I would like to reindex my database (and 
>> maybe re cluster it - don't know if that's worth it yet?). I need the 
>> database to be usable while doing this (both read and write). I see that 
>> there is no way to REINDEX CONCURRENTLY - So what approach would you suggest 
>> that I take on this?
> 
> If you have the diskspaec, it's generally a good idea to do a CREATE
> INDEX CONCURRENTLY, and then rename the new one into place (typically
> in a transaction). (If your app, documentation or dba doesn't mind the
> index changing names, you don't need to rename of course, you can just
> drop the old one).
> 
> 
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Best practice when reindexing in production

[Niels Kristian Schjødt]

I looked at pg_repack - however - is it "safe" for production? 
It seems very intrusive and black-box-like to me...


Den 29/05/2013 kl. 14.30 skrev Armand du Plessis :

> 
> On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander  wrote:
> On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt
>  wrote:
> > Hi,
> >
> > I have a database with quite some data (millions of rows), that is heavily 
> > updated all the time. Once a day I would like to reindex my database (and 
> > maybe re cluster it - don't know if that's worth it yet?). I need the 
> > database to be usable while doing this (both read and write). I see that 
> > there is no way to REINDEX CONCURRENTLY - So what approach would you 
> > suggest that I take on this?
> 
> If you have the diskspaec, it's generally a good idea to do a CREATE
> INDEX CONCURRENTLY, and then rename the new one into place (typically
> in a transaction). (If your app, documentation or dba doesn't mind the
> index changing names, you don't need to rename of course, you can just
> drop the old one).
> 
> If you wish to recluster it online you can also look into pg_repack - 
> https://github.com/reorg/pg_repack Great tool allows you to repack and 
> reindex your database without going offline. 
>  

[PERFORM] Best practice when reindexing in production

[Niels Kristian Schjødt]

Hi,

I have a database with quite some data (millions of rows), that is heavily 
updated all the time. Once a day I would like to reindex my database (and maybe 
re cluster it - don't know if that's worth it yet?). I need the database to be 
usable while doing this (both read and write). I see that there is no way to 
REINDEX CONCURRENTLY - So what approach would you suggest that I take on this?

Regards Niels Kristian

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Advice on optimizing select/index

[Niels Kristian Schjødt]

Hi, I have a database where one of my tables (Adverts) are requested a LOT. 
It's a relatively narrow table with 12 columns, but the size is growing pretty 
rapidly. The table is used i relation to another one called (Car), and in the 
form of "cars has many adverts". I have indexed the foreign key car_id on 
Adverts.

However the performance when doing a "SELECT .* FROM cars LEFT OUTER JOIN 
adverts on cars.id = adverts.car_id WHERE cars.brand = 'Audi'" is too poor. I 
have identified that it's the Adverts table part that performs very bad, and 
it's by far the biggest of the two. I would like to optimize the query/index, 
but I don't know if there at all is any theoretical option of actually getting 
a performance boost on a join, where the foreign key is already indexed?

One idea I'm thinking of my self is that I have a column called state on the 
adverts which can either be 'active' or 'deactivated'. The absolute amount of 
'active adverts are relatively constant (currently 15%) where the remaining and 
growing part is 'deactivated'.

In reality the adverts that are selected is all 'active'. I'm hence wondering 
if it theoretically (and in reality of cause) would make my query faster if I 
did something like:  "SELECT .* FROM cars LEFT OUTER JOIN adverts on cars.id = 
adverts.car_id WHERE cars.brand = 'Audi' AND adverts.state = 'active'" with a 
partial index on "INDEX adverts ON (car_id) WHERE state = 'active'"?

Regards Niels Kristian

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Why is my pg_xlog directory so huge?

[Niels Kristian Schjødt]

Thanks! it worked! :-)


Den 18/03/2013 kl. 15.38 skrev Magnus Hagander :

> On Mon, Mar 18, 2013 at 2:08 PM, Niels Kristian Schjødt
>  wrote:
>> Okay, thanks. It' seems you were right! Now I have fixed the issue (it was 
>> an ssh key).
>> So I started a:
>> SELECT pg_start_backup('backup', true);
>> 
>> And when done, I executed a:
>> sudo -u postgres rsync -av --exclude postmaster.pid --exclude pg_xlog 
>> /var/lib/postgresql/9.2/main/ 
>> postgres@192.168.0.2:/var/lib/postgresql/9.2/main/
>> 
>> Then I tried to finish off the backup by doing a:
>> SELECT pg_stop_backup();
>> 
>> But It keeps on telling me:
>> WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
>> archived (480 seconds elapsed)
>> HINT:  Check that your archive_command is executing properly.  
>> pg_stop_backup can be canceled safely, but the database backup will not be 
>> usable without all the WAL segments.
>> 
>> And I could see in the log that it's some kind of permission issue. So I 
>> canceled it, and started the streaming replication on my slave, and it seems 
>> to work fine. However the pg_xlog dir on the master is still HUGE 153G - so 
>> how can I get this mess sorted, and cleaned up that directory?
> 
> Once you have your archive_command working, it will transfer all your
> xlog to the archive. Once it is, the xlog directory should
> automatically clean up fairly quickly.
> 
> If you still have a permissions problem with the archive, you
> obviously need to fix that first.
> 
> If you don't care about your archive you could set your
> archive_command to e.g. /bin/true, and that will make it pretend it
> has archived the files, and should clean it up quicker. But that will
> mean you have no valid archive and thus no valid backups, until you
> start over froma new base.
> 
> 
> -- 
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Why is my pg_xlog directory so huge?

[Niels Kristian Schjødt]

Okay, thanks. It' seems you were right! Now I have fixed the issue (it was an 
ssh key). 
So I started a: 
SELECT pg_start_backup('backup', true);

And when done, I executed a: 
sudo -u postgres rsync -av --exclude postmaster.pid --exclude pg_xlog 
/var/lib/postgresql/9.2/main/ postgres@192.168.0.2:/var/lib/postgresql/9.2/main/

Then I tried to finish off the backup by doing a:
SELECT pg_stop_backup();

But It keeps on telling me: 
WARNING:  pg_stop_backup still waiting for all required WAL segments to be 
archived (480 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup 
can be canceled safely, but the database backup will not be usable without all 
the WAL segments.

And I could see in the log that it's some kind of permission issue. So I 
canceled it, and started the streaming replication on my slave, and it seems to 
work fine. However the pg_xlog dir on the master is still HUGE 153G - so how 
can I get this mess sorted, and cleaned up that directory?


Den 18/03/2013 kl. 10.26 skrev Magnus Hagander :

> On Mon, Mar 18, 2013 at 10:14 AM, Niels Kristian Schjødt
>  wrote:
>> After installing my new server I just discovered something that doesn't seem 
>> right:
>> 
>> sudo du -h /var/lib/postgresql/9.2/main
> 
> 
> 
>> As you can see the pg_xlog folder is 202G, which is more than my entire 
>> database - this seems wrong to me, however I have no clue why this would 
>> happen.
> 
> My first guess would be that your archive_command is failing - so
> check your logs for that. If that command fails, no xlog files will
> ever be rotated (since it would invalidate your backups).
> 
> -- 
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Why is my pg_xlog directory so huge?

[Niels Kristian Schjødt]

After installing my new server I just discovered something that doesn't seem 
right:

sudo du -h /var/lib/postgresql/9.2/main

4.0K/var/lib/postgresql/9.2/main/pg_snapshots
4.0K/var/lib/postgresql/9.2/main/pg_serial
4.0K/var/lib/postgresql/9.2/main/pg_tblspc
29M /var/lib/postgresql/9.2/main/pg_clog
6.8G/var/lib/postgresql/9.2/main/pg_log
104K/var/lib/postgresql/9.2/main/pg_stat_tmp
81G /var/lib/postgresql/9.2/main/base/27132
6.1M/var/lib/postgresql/9.2/main/base/12040
4.0K/var/lib/postgresql/9.2/main/base/pgsql_tmp
6.0M/var/lib/postgresql/9.2/main/base/12035
6.0M/var/lib/postgresql/9.2/main/base/1
81G /var/lib/postgresql/9.2/main/base
80K /var/lib/postgresql/9.2/main/pg_multixact/members
108K/var/lib/postgresql/9.2/main/pg_multixact/offsets
192K/var/lib/postgresql/9.2/main/pg_multixact
12K /var/lib/postgresql/9.2/main/pg_notify
4.0K/var/lib/postgresql/9.2/main/pg_twophase
160K/var/lib/postgresql/9.2/main/pg_subtrans
752K/var/lib/postgresql/9.2/main/pg_xlog/archive_status
202G/var/lib/postgresql/9.2/main/pg_xlog
496K/var/lib/postgresql/9.2/main/global
289G/var/lib/postgresql/9.2/main

As you can see the pg_xlog folder is 202G, which is more than my entire 
database - this seems wrong to me, however I have no clue why this would happen.

In short, this is my postgresql.conf

data_directory = '/var/lib/postgresql/9.2/main' # use data in another 
directory
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'   # host-based 
authentication file
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'   # ident configuration 
file
external_pid_file = '/var/run/postgresql/9.2-main.pid'  # write an 
extra PID file
listen_addresses = '192.168.0.4, localhost'  # what IP 
address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 300   # (change requires restart)
unix_socket_directory = '/var/run/postgresql'   # (change requires 
restart)
wal_level = hot_standby # minimal, archive, or hot_standby
synchronous_commit = on# synchronization level; on, off, or 
local
checkpoint_segments = 100   # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
archive_mode = on   # allows archiving to be done
archive_command = 'rsync -a %p 
postgres@192.168.0.2:/var/lib/postgresql/9.2/wals/%f http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Setup of four 15k SAS disk with LSI raid controller

[Niels Kristian Schjødt]

Den 13/03/2013 kl. 20.01 skrev Joshua D. Drake :

> 
> On 03/13/2013 11:45 AM, Vasilis Ventirozos wrote:
>> Its better to split WAL segments and data just because these two have
>> different io requirements and because its easier to measure and tune
>> things if you have them on different disks.
> 
> Generally speaking you are correct but we are talking about RAID 0 here.
So your suggestion is?
> JD
> 
> -- 
> Command Prompt, Inc. - http://www.commandprompt.com/
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC
> @cmdpromptinc - 509-416-6579
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Setup of four 15k SAS disk with LSI raid controller

[Niels Kristian Schjødt]

Den 13/03/2013 kl. 19.15 skrev Vasilis Ventirozos :

> raid0 tends to linear scaling so 3 of them should give something close to 
> 300% increased write speed. So i would say 1. but make sure you test your 
> configuration as soon as you can with bonnie++ or something similar
> 
> On Wed, Mar 13, 2013 at 7:43 PM, Niels Kristian Schjødt 
>  wrote:
> I have a server with 32GB ram, one intel E3-1245 and four 15k SAS disks with 
> a BB LSI MegaRaid controller. I wan't the optimal performance for my server, 
> which will be pretty write heavy at times, and less optimized for redundancy, 
> as my data is not very crucial and I will be running a streaming replication 
> along side.
> 
> Now what would you prefer:
> 
> 1) 3 disks in RAID 0 containing PGDATA + 1 containing SYSTEM and WAL
> 2) All four in RAID 10 containing both PGDATA, SYSTEM AND WAL
> 3) 2 disks in RAID 1 containing PGDATA + 2 disks in RAID 1 containing SYSTEM 
> and WAL
> 4) Something different?
A 5. option could also be to simply have all 4 disk in a RAID 0 containing all 
PGDATA, SYSTEM and WAL
> 
> 
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 

[PERFORM] Setup of four 15k SAS disk with LSI raid controller

[Niels Kristian Schjødt]

I have a server with 32GB ram, one intel E3-1245 and four 15k SAS disks with a 
BB LSI MegaRaid controller. I wan't the optimal performance for my server, 
which will be pretty write heavy at times, and less optimized for redundancy, 
as my data is not very crucial and I will be running a streaming replication 
along side.

Now what would you prefer:

1) 3 disks in RAID 0 containing PGDATA + 1 containing SYSTEM and WAL
2) All four in RAID 10 containing both PGDATA, SYSTEM AND WAL
3) 2 disks in RAID 1 containing PGDATA + 2 disks in RAID 1 containing SYSTEM 
and WAL
4) Something different? 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Risk of data corruption/loss?

[Niels Kristian Schjødt]

Den 13/03/2013 kl. 18.13 skrev Jeff Janes :

> On Wed, Mar 13, 2013 at 8:24 AM, Niels Kristian Schjødt 
>  wrote:
> I'm considering the following setup:
> 
> - Master server with battery back raid controller with 4 SAS disks in a RAID 
> 0 - so NO mirroring here, due to max performance requirements.
> - Slave server setup with streaming replication on 4 HDD's in RAID 10. The 
> setup will be done with synchronous_commit=off and synchronous_standby_names 
> = ''
> 
> Out of curiosity, in the presence of BB controller, is synchronous_commit=off 
> getting you additional performance?

Time will show :-)
> 
> 
> So as you might have noticed, clearly there is a risk of data loss, which is 
> acceptable, since our data is not very crucial. However, I have quite a hard 
> time figuring out, if there is a risk of total data corruption across both 
> server in this setup? E.g. something goes wrong on the master and the wal 
> files gets corrupt. Will the slave then apply the wal files INCLUDING the 
> corruption (e.g. an unfinished transaction etc.), or will it automatically 
> stop restoring at the point just BEFORE the corruption, so my only loss is 
> data AFTER the corruption?
> 
> It depends on where the corruption happens.  WAL is checksummed, so the slave 
> will detect a mismatch and stop applying records.  However, if the corruption 
> happens in RAM before the checksum is taken, the checksum will match and it 
> will attempt to apply the records.
> 
> Cheers,
> 
> Jeff

[PERFORM] Risk of data corruption/loss?

[Niels Kristian Schjødt]

I'm considering the following setup:

- Master server with battery back raid controller with 4 SAS disks in a RAID 0 
- so NO mirroring here, due to max performance requirements.
- Slave server setup with streaming replication on 4 HDD's in RAID 10. The 
setup will be done with synchronous_commit=off and synchronous_standby_names = 
''

So as you might have noticed, clearly there is a risk of data loss, which is 
acceptable, since our data is not very crucial. However, I have quite a hard 
time figuring out, if there is a risk of total data corruption across both 
server in this setup? E.g. something goes wrong on the master and the wal files 
gets corrupt. Will the slave then apply the wal files INCLUDING the corruption 
(e.g. an unfinished transaction etc.), or will it automatically stop restoring 
at the point just BEFORE the corruption, so my only loss is data AFTER the 
corruption?

Hope my question is clear



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

[Niels Kristian Schjødt]

Hi, thanks for answering. See comments inline.

Den 05/03/2013 kl. 15.26 skrev Julien Cigar :

> On 03/05/2013 15:00, Niels Kristian Schjødt wrote:
>> Hi,
>> 
>> I'm running a rails app, where I have a model called Car that has_many 
>> Images. Now when I tell rails to include those images, when querying say 50 
>> cars, then it often decides to use a SELECT * from images WHERE car_id IN 
>> (id1,id2,id3,id4…) instead of doing a join.
> 
> why do you want a join here ? if you don't need any "cars" data there is no 
> need to JOIN that table.
I need both
> Now a select ... from ... where id in (id1, id2, ..., idn) isn't very 
> scalable.
> 
> Instead of passing id1, id2, ..., idn you'be better pass the condition and do 
> a where id in (select ... ), or where exists (select 1 ... where ...), or a 
> join, or …
> 
I tried this now, and it doesn't seem to do a very big difference unfortunately…

>> Now either way it uses the index I
>> have on car_id:
>> 
>> Index Scan using car_id_ix on adverts  (cost=0.47..5665.34 rows=1224 
>> width=234)
>>  Index Cond: (car_id = ANY 
>> ('{7097561,7253541,5159633,6674471,...}'::integer[]))
>> 
>> But it's slow, it's very slow. In this case it took 3,323ms
> 
> 3ms isn't slow
> 
Sorry, it's 3323ms!

>> Can I do anything to optimize that query or maybe the index or something?
> 
> your index is already used

Okay this leaves me with - "get better hardware" or?

> 
>> The table has 16.000.000 rows
>> 
> 
> 
> -- 
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
> 
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] New server setup

[Niels Kristian Schjødt]

Okay, thanks - but hey - if I put it at session pooling, then it says in the 
documentation: "default_pool_size: In session pooling it needs to be the number 
of max clients you want to handle at any moment". So as I understand it, is it 
true that I then have to set default_pool_size to 300 if I have up to 300 
client connections? And then what would the pooler then help on my performance 
- would that just be exactly like having the 300 clients connect directly to 
the database???

-NK


Den 05/03/2013 kl. 19.03 skrev "Benjamin Krajmalnik" :

>  

Re: [PERFORM] New server setup

[Niels Kristian Schjødt]

Thanks, that was actually what I just ended up doing yesterday. Any suggestion 
how to tune pgbouncer?

BTW, I have just bumped into an issue that caused me to disable pgbouncer again 
actually. My web application is querying the database with a per request based 
SEARCH_PATH. This is because I use schemas to provide country based separation 
of my data (e.g. english, german, danish data in different schemas). I have 
pgbouncer setup to have a transactional behavior (pool_mode = transaction) - 
however some of my colleagues complained that it sometimes didn't return data 
from the right schema set in the SEARCH_PATH - you wouldn't by chance have any 
idea what is going wrong wouldn't you?

 pgbouncer.ini
[databases]
production =

[pgbouncer]

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = localhost
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 500
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 10
#


Den 05/03/2013 kl. 17.34 skrev Kevin Grittner :

> Niels Kristian Schjødt  wrote:
> 
>> So my question is, should I also get something like pgpool2 setup
>> at the same time? Is it, from your experience, likely to increase
>> my throughput a lot more, if I had a connection pool of eg. 20
>> connections, instead of 300 concurrent ones directly?
> 
> In my experience, it can make a big difference.  If you are just
> using the pooler for this reason, and don't need any of the other
> features of pgpool, I suggest pgbouncer.  It is a simpler, more
> lightweight tool.
> 
> -- 
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

[Niels Kristian Schjødt]

Hi,

I'm running a rails app, where I have a model called Car that has_many Images. 
Now when I tell rails to include those images, when querying say 50 cars, then 
it often decides to use a SELECT * from images WHERE car_id IN 
(id1,id2,id3,id4…) instead of doing a join. 

Now either way it uses the index I have on car_id:

Index Scan using car_id_ix on adverts  (cost=0.47..5665.34 rows=1224 width=234)
Index Cond: (car_id = ANY 
('{7097561,7253541,5159633,6674471,...}'::integer[]))

But it's slow, it's very slow. In this case it took 3,323ms

Can I do anything to optimize that query or maybe the index or something?

The table has 16.000.000 rows

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] What setup would you choose for postgresql 9.2 installation?

[Niels Kristian Schjødt]

That's around the behavior I'm seeing - I'll be testing tonight! :-)


Den 04/03/2013 kl. 16.23 skrev Scott Marlowe :

> however

[PERFORM] What setup would you choose for postgresql 9.2 installation?

[Niels Kristian Schjødt]

LSI MegaRAID SAS 9260-4i with four Intel SSDSC2CW240A3K5 SSDs OR four Hitachi 
Ultrastar 15K600 SAS drives?

My app is pretty write heavy and I have a lot of concurrent connections 300 - 
(though considering adding pgpool2 in front to increase throughput).

Regards Niels Kristian

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] New server setup

[Niels Kristian Schjødt]

Thanks both of you for your input.

Earlier I have been discussing my extremely high IO wait with you here on the 
mailing list, and have tried a lot of tweaks both on postgresql config, wal 
directly location and kernel tweaks, but unfortunately my problem persists, and 
I think I'm eventually down to just bad hardware (currently two 7200rpm disks 
in a software raid 1). So changing to 4 15000rpm SAS disks in a raid 10 is 
probably going to change a lot - don't you think? However, we are running a lot 
of background processing 300 connections to db sometimes. So my question is, 
should I also get something like pgpool2 setup at the same time? Is it, from 
your experience, likely to increase my throughput a lot more, if I had a 
connection pool of eg. 20 connections, instead of 300 concurrent ones directly?

Den 01/03/2013 kl. 16.28 skrev Craig James :

> On Fri, Mar 1, 2013 at 3:43 AM, Niels Kristian Schjødt 
>  wrote:
> Hi, I'm going to setup a new server for my postgresql database, and I am 
> considering one of these: 
> http://www.hetzner.de/hosting/produkte_rootserver/poweredge-r720 with four 
> SAS drives in a RAID 10 array. Has any of you any particular 
> comments/pitfalls/etc. to mention on the setup? My application is very write 
> heavy.
> 
> I can only tell you our experience with Dell from several years ago.  We 
> bought two Dell servers similar (somewhat larger) than the model you're 
> looking at.  We'll never buy from them again.
> 
> Advantages:  They work.  They haven't failed.
> 
> Disadvantages: 
> 
> Performance sucks.  Dell costs far more than "white box" servers we buy from 
> a "white box" supplier (ASA Computers).  ASA gives us roughly double the 
> performance for the same price.  We can buy exactly what we want from ASA.
> 
> Dell did a disk-drive "lock in."  The RAID controller won't spin up a 
> non-Dell disk.  They wanted roughly four times the price for their disks 
> compared to buying the exact same disks on Amazon.  If a disk went out today, 
> it would probably cost even more because that model is obsolete (luckily, we 
> bought a couple spares).  I think they abandoned this policy because it 
> caused so many complaints, but you should check before you buy. This was an 
> incredibly stupid RAID controller design.
> 
> Dell tech support doesn't know what they're talking about when it comes to 
> RAID controllers and serious server support.  You're better off with a 
> white-box solution, where you can buy the exact parts recommended in this 
> group and get technical advice from people who know what they're talking 
> about.  Dell basically doesn't understand Postgres.
> 
> They boast excellent on-site service, but for the price of their computers 
> and their service contract, you can buy two servers from a white-box vendor.  
> Our white-box servers have been just as reliable as the Dell servers -- no 
> failures.
> 
> I'm sure someone in Europe can recommend a good vendor for you.
> 
> Craig James
>  
> 
> 
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 

[PERFORM] New server setup

[Niels Kristian Schjødt]

Hi, I'm going to setup a new server for my postgresql database, and I am 
considering one of these: 
http://www.hetzner.de/hosting/produkte_rootserver/poweredge-r720 with four SAS 
drives in a RAID 10 array. Has any of you any particular comments/pitfalls/etc. 
to mention on the setup? My application is very write heavy.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

Den 11/12/2012 kl. 18.25 skrev Jeff Janes :

> On Tue, Dec 11, 2012 at 2:04 AM, Niels Kristian Schjødt
>  wrote:
>> Den 11/12/2012 kl. 00.58 skrev Jeff Janes :
>> 
>>> 
>>> The fact that there is much more writing than reading tells me that
>>> most of your indexes are in RAM.  The amount of index you are rapidly
>>> reading and dirtying is large enough to fit in RAM, but is not large
>>> enough to fit in shared_buffers + kernel's dirty-buffer comfort level.
> 
>> Maybe I should mention, that I never see more than max 5Gb out of my total 
>> 32Gb being in use on the server… Can I somehow utilize more of it?
> 
> What tool do you use to determine that?  Is that on top of the 4GB
> shared_buffers, are including it?

Okay I might not have made myself clear, I was talking "physical" memory 
utilization. Here is the stats:
free -m
total   used   free sharedbuffers cached
Mem: 32075  25554   6520  0 69  22694
-/+ buffers/cache:   2791  29284
Swap: 2046595   1451
> 
> How big is your entire data set?  Maybe all your data fits in 5GB
> (believable, as all your indexes listed below sum to < 2.5GB) so there
> is no need to use more.

It doesn't we are a search engine for used cars, and there are quite a lot of 
those out there :-) However, my indexes are almost all partial indexes, which 
mean that they are only on cars which is still for sale, so in that sense, the 
indexes them selves doesn't really grow, but the tables do.

> 
> Or maybe you have hit an bug in the 3.2 kernel.  At least one of those
> has been frequently discussed.
> 
Might be true - but likely?
> 
>>> You could really crank up shared_buffers or vm.dirty_background_ratio,
>>> but doing so might cause problems with checkpoints stalling and
>>> latency spikes.  That would probably not be a problem during the
>>> night, but could be during the day.
> 
>> What do you have in mind here? Tweaking what parameters to what values?
> 
> I'd set shared_buffers to 20GB (or 10GB, if that will hold all of your

I had that before, Shaun suggested that I changed it to 4GB as he was talking 
about a strange behavior when larger than that on 12.04. But I can say, that 
there has not been any notable difference between having it at 4Gb and at 8Gb.

> data) and see what happens.  And probably increase checkpoint_timeout
> and checkpoint_segments about 3x each. Also, turn on log_checkpoints
> so you can see what kinds of problem those changes may be causing
> there (i.e. long sync times).  Preferably you do this on some kind of
> pre-production or test server.
> 
> But if your database is growing so rapidly that it soon won't fit on
> 240GB, then cranking up shared_buffers won't do for long.  If you can
> get your tables and all of their indexes clustered together, then you
> can do the updates in an order that makes IO more efficient.  Maybe
> partitioning would help.

Can you explain a little more about this, or provide me a good link?
> 
> 
>>> I don't know how big each disk is, or how big your various categories
>>> of data are.  Could you move everything to SSD?  Could you move all
>>> your actively updated indexes there?
> 
>> With table spaces you mean?
> 
> Yes.  Or moving everything to SSD if it fits, then you don't have go
> through and separate objects.
> 
> The UPDATE you posted in a previous thread looked like the table
> blocks might also be getting dirtied in a fairly random order, which
> means the table blocks are in the same condition as the index blocks
> so maybe singling out the indexes isn't warranted.
> 
> Cheers,
> 
> Jeff



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

Well, In fact I do (as you can see from my configuration). I have a similar 
server running with hot standby replication - and it runs two 3T HDD in a RAID1 
array.

So - is it still very bad if I choose to put four intel 520 disks in a RAID10 
array on the other production server?

Den 12/12/2012 kl. 03.47 skrev Craig Ringer :

> On 12/12/2012 10:13 AM, Evgeny Shishkin wrote:
>> 
>> Yes, i am aware of this issue. Never experienced this neither on intel 520, 
>> no ocz vertex 3.
>> 
> 
> I wouldn't trust either of those drives. The 520 doesn't have Intel's " 
> Enhanced Power Loss Data Protection"; it's going to lose its buffers if it 
> loses power. Similarly, the Vertex 3 doesn't have any kind of power 
> protection. See:
> 
> http://www.intel.com/content/www/us/en/solid-state-drives/ssd-320-series-power-loss-data-protection-brief.html
> http://ark.intel.com/products/family/56572/Intel-SSD-500-Family
> 
> http://www.ocztechnology.com/res/manuals/OCZ_SSD_Breakdown_Q2-11_1.pdf
> 
> The only way I'd use those for a production server was if I had synchronous 
> replication running to another machine with trustworthy, durable storage - 
> and if I didn't mind some downtime to restore the corrupt DB from the replica 
> after power loss.
> 
> -- 
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

Are you using a hardware based raid controller with them?
Den 11/12/2012 20.11 skrev "Evgeny Shishkin" :

>
> On Dec 11, 2012, at 10:54 PM, Niels Kristian Schjødt <
> nielskrist...@autouncle.com> wrote:
>
> And what is your experience so far?
>
> Increased tps by a factor of 10, database no longer a limiting factor of
> application.
> And it is cheaper than brand rotating drives.
>
>
> Den 11/12/2012 18.16 skrev "Evgeny Shishkin" :
>
>>
>> On Dec 11, 2012, at 5:35 PM, Niels Kristian Schjødt <
>> nielskrist...@autouncle.com> wrote:
>>
>> >
>> > Den 11/12/2012 kl. 14.29 skrev Craig Ringer :
>> >
>> >> On 12/11/2012 06:04 PM, Niels Kristian Schjødt wrote:
>> >>>
>> >>> Maybe I should mention, that I never see more than max 5Gb out of my
>> total 32Gb being in use on the server… Can I somehow utilize more of it?
>> >> For an update-mostly workload it probably won't do you tons of good so
>> >> long as all your indexes fit in RAM. You're clearly severely
>> >> bottlenecked on disk I/O not RAM.
>> >>> The SSD's I use a are 240Gb each which will grow too small within a
>> >>> few months - so - how does moving the whole data dir onto four of
>> >>> those in a RAID5 array sound?
>> >>
>> >> Not RAID 5!
>> >>
>> >> Use a RAID10 of four or six SSDs.
>> >>
>> >> --
>> >> Craig Ringer   
>> >> http://www.2ndQuadrant.com/<http://www.2ndquadrant.com/>
>> >> PostgreSQL Development, 24x7 Support, Training & Services
>> >>
>> > Hehe got it - did you have a look at the SSD's I am considering
>> building it of?
>> http://ark.intel.com/products/66250/Intel-SSD-520-Series-240GB-2_5in-SATA-6Gbs-25nm-MLC
>> > Are they suitable do you think?
>> >
>>
>> I am not Craig, but i use them in production in raid10 array now.
>>
>> >
>> >
>> > --
>> > Sent via pgsql-performance mailing list (
>> pgsql-performance@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

And what is your experience so far?
Den 11/12/2012 18.16 skrev "Evgeny Shishkin" :

>
> On Dec 11, 2012, at 5:35 PM, Niels Kristian Schjødt <
> nielskrist...@autouncle.com> wrote:
>
> >
> > Den 11/12/2012 kl. 14.29 skrev Craig Ringer :
> >
> >> On 12/11/2012 06:04 PM, Niels Kristian Schjødt wrote:
> >>>
> >>> Maybe I should mention, that I never see more than max 5Gb out of my
> total 32Gb being in use on the server… Can I somehow utilize more of it?
> >> For an update-mostly workload it probably won't do you tons of good so
> >> long as all your indexes fit in RAM. You're clearly severely
> >> bottlenecked on disk I/O not RAM.
> >>> The SSD's I use a are 240Gb each which will grow too small within a
> >>> few months - so - how does moving the whole data dir onto four of
> >>> those in a RAID5 array sound?
> >>
> >> Not RAID 5!
> >>
> >> Use a RAID10 of four or six SSDs.
> >>
> >> --
> >> Craig Ringer   http://www.2ndQuadrant.com/
> >> PostgreSQL Development, 24x7 Support, Training & Services
> >>
> > Hehe got it - did you have a look at the SSD's I am considering building
> it of?
> http://ark.intel.com/products/66250/Intel-SSD-520-Series-240GB-2_5in-SATA-6Gbs-25nm-MLC
> > Are they suitable do you think?
> >
>
> I am not Craig, but i use them in production in raid10 array now.
>
> >
> >
> > --
> > Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

Den 11/12/2012 kl. 14.29 skrev Craig Ringer :

> On 12/11/2012 06:04 PM, Niels Kristian Schjødt wrote:
>> 
>> Maybe I should mention, that I never see more than max 5Gb out of my total 
>> 32Gb being in use on the server… Can I somehow utilize more of it?
> For an update-mostly workload it probably won't do you tons of good so
> long as all your indexes fit in RAM. You're clearly severely
> bottlenecked on disk I/O not RAM.
>> The SSD's I use a are 240Gb each which will grow too small within a
>> few months - so - how does moving the whole data dir onto four of
>> those in a RAID5 array sound? 
> 
> Not RAID 5!
> 
> Use a RAID10 of four or six SSDs.
> 
> -- 
> Craig Ringer   http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
> 
Hehe got it - did you have a look at the SSD's I am considering building it of? 
http://ark.intel.com/products/66250/Intel-SSD-520-Series-240GB-2_5in-SATA-6Gbs-25nm-MLC
 
Are they suitable do you think?



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

Den 11/12/2012 kl. 00.58 skrev Jeff Janes :

> On Mon, Dec 10, 2012 at 2:51 PM, Niels Kristian Schjødt
>  wrote:
> 
>> synchronous_commit = off
>> 
>> The pg_xlog folder has been moved onto the SSD array (md3), and symlinked
>> back into the postgres dir.
> 
> With synchronous_commit = off, or with large transactions, there is
> probably no advantage to moving those to SSD.
> 
> 
>> 2)
>> When the database is loaded like this, I see a lot of queries talking up to
>> 1000 times as long, as they would when the database is not loaded so
>> heavily.
> 
> What kinds of queries are they?  single-row look-ups, full table scans, etc.
Well Mostly they are updates. Like the one shown in the previous question I 
referenced.
>> 
>>  Notes and thoughts
>> ##
>> 
>> As you can see, even though I have moved the pg_xlog folder to the SSD array
>> (md3) the by far largest amount of writes still goes to the regular HDD's
>> (md2), which puzzles me - what can that be?
> 
> Every row you insert or non-HOT update has to do maintenance on all
> indexes of that table.  If the rows are not inserted/updated in index
> order, this means you every row inserted/updated dirties a randomly
> scattered 8KB for each of the indexes.  If you have lots of indexes
> per table, that adds up fast.
> 
> The fact that there is much more writing than reading tells me that
> most of your indexes are in RAM.  The amount of index you are rapidly
> reading and dirtying is large enough to fit in RAM, but is not large
> enough to fit in shared_buffers + kernel's dirty-buffer comfort level.
Maybe I should mention, that I never see more than max 5Gb out of my total 32Gb 
being in use on the server… Can I somehow utilize more of it?
> So you are redirtying the same blocks over and over, PG is
> desperately dumping them to the kernel (because shared_buffers it too
> small to hold them) and the kernel is desperately dumping them to
> disk, because vm.dirty_background_ratio is so low.  There is little
> opportunity for write-combining, because they don't sit in memory long
> enough to accumulate neighbors.
> 
> How big are your indexes?
This is a size list of all my indexes: 117 MB, 118 MB, 11 MB, 12 MB, 12 MB, 12 
MB, 12 MB, 140 MB, 15 MB, 15 MB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 
kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 MB, 16 MB, 176 kB, 176 kB, 17 MB, 18 
MB, 19 MB, 23 MB, 240 kB, 24 MB, 256 kB, 25 MB, 25 MB, 26 MB, 26 MB, 27 MB, 27 
MB, 27 MB, 27 MB, 280 MB, 2832 kB, 2840 kB, 288 kB, 28 MB, 28 MB, 28 MB, 28 MB, 
28 MB, 28 MB, 28 MB, 28 MB, 29 MB, 29 MB, 3152 kB, 3280 kB, 32 kB, 32 MB, 32 
MB, 3352 kB, 3456 kB, 34 MB, 36 MB, 3744 kB, 3776 kB, 37 MB, 37 MB, 3952 kB, 
400 kB, 408 kB, 40 kB, 40 kB, 40 kB, 416 kB, 416 kB, 42 MB, 432 kB, 4520 kB, 
4720 kB, 47 MB, 48 kB, 496 kB, 49 MB, 512 kB, 52 MB, 52 MB, 5304 kB, 5928 kB, 
6088 kB, 61 MB, 6224 kB, 62 MB, 6488 kB, 64 kB, 6512 kB, 71 MB, 72 kB, 72 kB, 
8192 bytes, 8400 kB, 88 MB, 95 MB, 98 MB
> You could really crank up shared_buffers or vm.dirty_background_ratio,
> but doing so might cause problems with checkpoints stalling and
> latency spikes.  That would probably not be a problem during the
> night, but could be during the day.
What do you have in mind here? Tweaking what parameters to what values?
> .
> Rather than moving maintenance to the day and hoping it doesn't
> interfere with normal operations, I'd focus on making night-time
> maintenance more efficient, for example by dropping indexes (either
> just at night, or if some indexes are not useful, just get rid of them
> altogether), or cranking up shared_buffers at night, or maybe
> partitioning or look into pg_bulkload.
> 
>> From stat 3) (the iostat) I notice that the SSD's doesn't seem to be
>> something near fully utilized - maybe something else than just pg_xlog could
>> be moved her?
> 
> I don't know how big each disk is, or how big your various categories
> of data are.  Could you move everything to SSD?  Could you move all
> your actively updated indexes there?
With table spaces you mean?
> Or, more fundamentally, it looks like you spent too much on CPUs (86%
> idle) and not nearly enough on disks.  Maybe you can fix that for less
> money than it will cost you in your optimization time to make the best
> of the disks you already have.
The SSD's I use a are 240Gb each which will grow too small within a few months 
- so - how does moving the whole data dir onto four of those in a RAID5 array 
sound?
> 
> Cheers,
> 
> Jeff



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Do I have a hardware or a software problem?

[Niels Kristian Schjødt]

 Pitch 
##
I previously posted this question 
http://archives.postgresql.org/pgsql-performance/2012-11/msg00289.php about a 
performance issue with an update query. 
The question evolved into a more general discussion about my setup, and about a 
lot of I/O wait that I was encountering. Since then, I have gotten a whole lot 
more familiar with measuring things, and now I "just" need some experienced 
eyes to judge which direction I should go in - do I have a hardware issue, or a 
software issue - and what action should I take?

#  My setup 
#
The use case:
At night time we are doing a LOT of data maintenance, and hence the load on the 
database is very different from the day time. However we would like to be able 
to do some of it in the daytime, it's simply just too "heavy" on the database 
as is right now. The stats shown below is from one of those "heavy" load times.

Hardware: 
  - 32Gb ram 
  - 8 core Xeon E3-1245 processor
  - Two SEAGATE ST33000650NS drives (called sdc and sdd in the stats) in a 
softeware RAID1 array (called md2 in the stats)
  - Two INTEL SSDSC2CW240A3 SSD drives (called sda and sdb in the stats) in a 
software RAID1 (called md3 in the stats)

Software:
Postgres 9.2 running on 64bit ubuntu 12.04 with kernel 3.2

Configuration:
# postgresql.conf (a shortlist of everything changed from the default)
data_directory = '/var/lib/postgresql/9.2/main'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.2-main.pid'
listen_addresses = '192.168.0.2, localhost'
port = 5432
max_connections = 300
unix_socket_directory = '/var/run/postgresql'
wal_level = hot_standby
synchronous_commit = off
archive_mode = on
archive_command = 'rsync -a %p 
postgres@192.168.0.4:/var/lib/postgresql/9.2/wals/%f https://rpm.newrelic.com/public/charts/6ewGRle6bmc

2)
When the database is loaded like this, I see a lot of queries talking up to 
1000 times as long, as they would when the database is not loaded so heavily.

3)
sudo iostat -dmx (typical usage)
Linux 3.2.0-33-generic (master-db)  12/10/2012  _x86_64_(8 CPU)

Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s avgrq-sz 
avgqu-sz   await r_await w_await  svctm  %util
sda   0.00 6.523.59   26.61 0.22 0.7465.49 
0.010.400.770.35   0.14   0.43
sdb   0.00 8.310.03   28.38 0.00 0.9769.63 
0.010.520.270.52   0.15   0.43
sdc   1.7146.01   34.83  116.62 0.56 4.0662.47 
1.90   12.57   21.819.81   1.89  28.66
sdd   1.6746.14   34.89  116.49 0.56 4.0662.46 
1.58   10.43   21.667.07   1.89  28.60
md1   0.00 0.000.000.00 0.00 0.00 2.69 
0.000.000.000.00   0.00   0.00
md0   0.00 0.000.110.24 0.00 0.00 8.00 
0.000.000.000.00   0.00   0.00
md2   0.00 0.00   72.99  161.95 1.11 4.0645.10 
0.000.000.000.00   0.00   0.00
md3   0.00 0.000.05   32.32 0.00 0.7447.00 
0.000.000.000.00   0.00   0.00

3)
sudo iotop -oa (running for about a minute or so)
TID  PRIO  USER DISK READ  DISK WRITE  SWAPIN IO>COMMAND
  292be/4 root   0.00 B  0.00 B0.00 % 99.33 % 
[md2_raid1]
 2815  be/4 postgres 19.51 M 25.90 M  0.00 % 45.49 % postgres: 
autovacuum worker process   production
32553 be/4 postgres 45.74 M  9.38 M  0.00 % 37.89 % postgres: user 
production 192.168.0.3(58866) UPDATE
32570 be/4 postgres  6.91 M 35.02 M  0.00 % 16.71 % postgres: user 
production 192.168.0.3(35547) idle
32575 be/4 postgres  4.06 M 43.90 M  0.00 % 16.62 % postgres: user 
production 192.168.0.3(35561) SELECT
31673 be/4 postgres  4.14 M 52.16 M  0.00 % 16.24 % postgres: user 
production 192.168.0.3(39112) idle
32566 be/4 postgres  4.73 M 44.95 M  0.00 % 15.66 % postgres: user 
production 192.168.0.3(35531) idle
32568 be/4 postgres  4.50 M 33.84 M  0.00 % 14.62 % postgres: user 
production 192.168.0.3(35543) SELECT
32573 be/4 postgres  3.20 M 34.44 M  0.00 % 13.98 % postgres: user 
production 192.168.0.3(35559) idle
31590 be/4 postgres  3.23 M 29.72 M  0.00 % 13.90 % postgres: user 
production 192.168.0.3(50690) idle in transaction
32577 be/4 postgres  5.09 M 25.54 M  0.00 % 13.63 % postgres: user 
production 192.168.0.3(35563) idle
32565 be/4 postgres  2.06 M 35.93 M  0.00 % 13.41 % postgres: user 
production 192.168.0.3(35529) SELECT
32546 be/4 postgres  4.48 M 36.49 M  0.00 % 13.39 % postgres: user 
production 192.168.0.3(56927) UPDATE waitin

Re: [PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

[Niels Kristian Schjødt]

Where as I can't say I yet tried out the 3.4 kernel, I can say that I am 
running 3.2 too, and maybe there is a connection to the past issues of strange 
CPU behavior I have had (as you know and have been so kind to try helping me 
solve). I will without a doubt try out 3.4 or 3.6 within the coming days, and 
report back on the topic.

   
Den 05/12/2012 kl. 19.28 skrev Shaun Thomas :

> Hey guys,
> 
> This isn't a question, but a kind of summary over a ton of investigation
> I've been doing since a recent "upgrade". Anyone else out there with
> "big iron" might want to confirm this, but it seems pretty reproducible.
> This seems to affect the latest 3.2 mainline and by extension, any
> platform using it. My tests are restricted to Ubuntu 12.04, but it may
> apply elsewhere.
> 
> Comparing the latest official 3.2 kernel to the latest official 3.4
> kernel (both Ubuntu), there are some rather striking differences. I'll
> start with some pgbench tests.
> 
> * This test is 800 read-only clients, with 2 controlling threads on a
> 55GB database (scaling factor of 3600) for 3 minutes.
>  * With 3.4:
>* Max TPS was 68933.
>* CPU was between 50 and 55% idle.
>* Load average was between 10 and 15.
>  * With 3.2:
>* Max TPS was 17583. A total loss of 75% performance.
>* CPU was between 12 and 25% idle.
>* Load average was between 10 and 60---effectively random.
>  * Next, we checked minimal write tests. This time, with only two
> clients. All other metrics are the same.
>* With 3.4:
>  * Max TPS was 4548.
>  * CPU was between 88 and 92% idle.
>  * Load average was between 1.7 and 2.5.
>* With 3.2:
>  * Max TPS was 4639.
>  * CPU was between 88 and 92% idle.
>  * Load average was between 3 and 4.
> 
> Overall, performance was _much_ worse in 3.2 by almost every metric
> except for very low contention activity. More CPU for less transactions,
> and wildly inaccurate load reporting. The 3.2 kernel in its current
> state should be considered detrimental and potentially malicious under
> high task contention.
> 
> I'll admit not letting the tests run for more than 10 iterations, but I
> didn't really need more than that. Even one iteration is enough to see
> this in action. At least every Ubuntu 3.2 kernel since 3.2.0-31 exhibits
> this, but I haven't tested further back. I've also examined both
> official Ubuntu 3.2 and Ubuntu mainline kernels as obtained from here:
> 
> http://kernel.ubuntu.com/~kernel-ppa/mainline
> 
> The 3.2.34 mainline also has these problems. For reference, I tested the
> 3.4.20 Quantal release on Precise because the Precise 3.4 kernel hasn't
> been maintained.
> 
> Again, anyone running 12.04 LTS, take a good hard look at your systems.
> Hopefully you have a spare machine to test with. I'm frankly appalled
> this thing is in an LTS release.
> 
> I'll also note that all kernels exhibit some extent of client threads
> bloating load reports. In a pgbench for-loop (run, sleep 1, repeat), 
> sometimes load will jump to some very high number between iterations, but on 
> a 3.4, it will settle down again. On a 3.2, it just jumps randomly. I tested 
> that with this script:
> 
> nLoop=0
> 
> while [ 1 -eq 1 ]; do
> 
>  if [ $[$nLoop % 20] -eq 0 ]; then
>echo -e "Stat Time\t\tSleep\tRun\tLoad Avg"
>  fi
> 
>  stattime=$(date +"%Y-%m-%d %H:%M:%S")
>  sleep=$(ps -emo stat | egrep -c 'D')
>  run=$(ps -emo stat | egrep -c 'R')
>  loadavg=$(cat /proc/loadavg | cut -d ' ' -f 1)
> 
>  echo -e "${stattime}\t${sleep}\t${run}\t${loadavg}"
>  sleep 1
> 
>  nLoop=$[$nLoop + 1]
> 
> done
> 
> The jumps look like this:
> 
> Stat Time Sleep   Run Load Avg
> 2012-12-05 12:23:13   0   16  7.66
> 2012-12-05 12:23:14   0   12  7.66
> 2012-12-05 12:23:15   0   7   7.66
> 2012-12-05 12:23:16   0   17  7.66
> 2012-12-05 12:23:17   0   1   24.51
> 2012-12-05 12:23:18   0   2   24.51
> 
> It's much harder to trigger on 3.4, but still happens.
> 
> If anyone has tested against 3.6 or 3.7, I'd love to hear your input. 
> Inconsistent load reports are one thing... strangled performance and inflated 
> CPU usage are quite another.
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 100
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Den 30/11/2012 kl. 17.06 skrev Shaun Thomas :

> On 11/30/2012 09:44 AM, Niels Kristian Schjødt wrote:
> 
> Just a note on your iostat numbers. The first reading is actually just a 
> summary. You want the subsequent readings.
> 
>> The pgsql_tmp dir is not changing at all it's constantly empty (a size
>> of 4.0K).
> 
> Good.
> 
>> Filesystem 1K-blocksUsed Available Use% Mounted on
>> /dev/md3   230619228 5483796 213420620   3% /ssd
> 
> Good.
> 
> You could just be seeing lots of genuine activity. But going back on the 
> thread, I remember seeing this in your postgresql.conf:
> 
> shared_buffers = 7680MB
> 
> Change this to:
> 
> shared_buffers = 4GB
> 
> I say that because you mentioned you're using Ubuntu 12.04, and we were 
> having some problems with PG on that platform. With shared_buffers over 4GB, 
> it starts doing really weird things to the memory subsystem. Whatever it does 
> causes the kernel to purge cache rather aggressively. We saw a 60% reduction 
> in read IO by reducing shared_buffers to 4GB. Without as many reads, your 
> writes should be much less disruptive.
> 
> You'll need to restart PG to adopt that change.
> 
> But I encourage you to keep iostat running in a terminal window so you can 
> watch it for a while. It's very revealing.
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email
Couldn't this be if you haven't changed these: 
http://www.postgresql.org/docs/9.2/static/kernel-resources.html ?
I have changed the following in my configuration:

kernel.shmmax = 8589934592 #(8GB)
kernel.shmall = 17179869184 #(16GB)



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Hmm very very interesting. Currently I run at "medium" load compared to the 
very high loads in the night.
This is what the CPU I/O on new relic show: 
https://rpm.newrelic.com/public/charts/8RnSOlWjfBy
And this is what iostat shows:

Linux 3.2.0-33-generic (master-db)  11/30/2012  _x86_64_(8 CPU)

Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s avgrq-sz 
avgqu-sz   await r_await w_await  svctm  %util
sda   0.00 3.46   26.62   57.06 1.66 0.6857.41 
0.040.430.770.28   0.09   0.73
sdb   0.0316.850.01   70.26 0.00 2.3568.36 
0.060.810.210.81   0.10   0.73
sdc   1.9656.37   25.45  172.56 0.53 3.7243.98
30.83  155.70   25.15  174.96   1.74  34.46
sdd   1.8356.52   25.48  172.42 0.52 3.7243.90
30.50  154.11   25.66  173.09   1.74  34.37
md1   0.00 0.000.000.00 0.00 0.00 3.02 
0.000.000.000.00   0.00   0.00
md0   0.00 0.000.570.59 0.00 0.00 8.00 
0.000.000.000.00   0.00   0.00
md2   0.00 0.00   54.14  227.94 1.05 3.7234.61 
0.000.000.000.00   0.00   0.00
md3   0.00 0.000.01   60.46 0.00 0.6823.12 
0.000.000.000.00   0.00   0.00

A little reminder md3 is the raid array of the ssd drives sda and sdb and the 
md0-2 is the array of the regular hdd drives sdc and sdd

The pgsql_tmp dir is not changing at all it's constantly empty (a size of 4.0K).

So It doesn't seem like the ssd drives is at all utilized but the regular 
drives certainly is. but now i know for sure that the /ssd is mounted correctly:

"sudo df /ssd"
Filesystem 1K-blocksUsed Available Use% Mounted on
/dev/md3   230619228 5483796 213420620   3% /ssd



 

Den 30/11/2012 kl. 16.00 skrev Shaun Thomas :

> On 11/30/2012 08:48 AM, Niels Kristian Schjødt wrote:
> 
>> I forgot to run 'sudo mount -a' I feel so embarrassed now :-( - In
>> other words no the drive was not mounted to the /ssd dir.
> 
> Yeah, that'll get ya.
> 
>> I still see a lot of CPU I/O when doing a lot of writes, so the
>> question is, what's next. Should I try and go' for the connection
>> pooling thing or monitor that
>> /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do
>> you mean by monitor - size?)
> 
> Well, like Keven said, if you have more than a couple dozen connections on 
> your hardware, you're losing TPS. It's probably a good idea to install 
> pgbouncer or pgpool and let your clients connect to those instead. You should 
> see a good performance boost from that.
> 
> But what concerns me is that your previous CPU charts showed a lot of iowait. 
> Even with the SSD taking some of the load off your write stream, something 
> else is going on, there. That's why you need to monitor the "size" in MB, or 
> number of files, for the pgsql_tmp directory. That's where PG puts temp files 
> when sorts are too big for your work_mem. If that's getting a ton of 
> activity, that would explain some of your write overhead.
> 
>> PPS. I talked with New Relic and it turns out there is something
>> wrong with the disk monitoring tool, so that's why there was nothing
>> in the disk charts but iostat showed a lot of activity.
> 
> Yeah. Next time you need to check IO, use iostat. It's not as pretty, but it 
> tells everything. ;) Just to help out with that, use:
> 
> iostat -dmx
> 
> That will give you extended information, including the % utilization of your 
> drives. TPS stats are nice, but I was just guessing your drives were stalling 
> out based on experience. Getting an outright percentage is better. You should 
> also use sar. Just a plain:
> 
> sar 1 100
> 
> Will give you a lot of info on what the CPU is doing. You want that %iowait 
> column to be as low as possible.
> 
> Keep us updated.
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Den 30/11/2012 kl. 15.02 skrev Shaun Thomas :

> On 11/29/2012 08:32 PM, Niels Kristian Schjødt wrote:
> 
>> If I do a "sudo iostat -k 1"
>> I get a lot of output like this:
>> Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
>> sda   0.00 0.00 0.00  0  0
>> sdb   0.00 0.00 0.00  0  0
>> sdc 546.00  2296.00  6808.00   2296   6808
>> sdd 593.00  1040.00  7416.00   1040   7416
>> md1   0.00 0.00 0.00  0  0
>> md0   0.00 0.00 0.00  0  0
>> md21398.00  3328.00 13064.00   3328  13064
>> md3   0.00 0.00 0.00  0  0
>> 
> 
>> The storage thing is, that the sda and sdb is the SSD drives and the
>> sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid
>> arrays on the HDD's and the md3 is the raid on the SSD's. Neither of
>> the md3 or the SSD's are getting utilized - and I should expect that
>> since they are serving my pg_xlog right?
> 
> No, that's right. They are, but it would appear that the majority of your 
> traffic actually isn't due to transaction logs like I'd suspected. If you get 
> a chance, could you monitor the contents of:
> 
> /var/lib/postgresql/9.2/main/base/pgsql_tmp
> 
> Your main drives are getting way, way more writes than they should. 13MB per 
> second is ridiculous even under heavy write loads. Based on the TPS count, 
> you're basically saturating the ability of those two 3TB drives. Those writes 
> have to be coming from somewhere.
> 
>> #   sudo mkdir -p /ssd/pg_xlog
> 
> This is going to sound stupid, but are you *sure* the SSD is mounted at /ssd ?
> 
>> #   sudo chown -R  postgres.postgres /ssd/pg_xlog
>> #   sudo chmod 700 /ssd/pg_xlog
>> #   sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog
>> #   sudo mv /var/lib/postgresql/9.2/main/pg_xlog 
>> /var/lib/postgresql/9.2/main/pg_xlog_old
>> #   sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog
>> #   sudo /etc/init.d/postgresql-9.2 start
> 
> The rest of this is fine, except that you probably should have added:
> 
> sudo chown -R postgres:postgres /ssd/pg_xlog/*
> 
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email

Oh my, Shaun once again you nailed it! That's what you get from working too 
late in the night - I forgot to run 'sudo mount -a' I feel so embarrassed now 
:-( - In other words no the drive was not mounted to the /ssd dir. 
So now it is, and this has gained me a performance increase of roughly around 
20% - a little less than what I would have hoped for but still better - but 
anyways yes that's right.
I still see a lot of CPU I/O when doing a lot of writes, so the question is, 
what's next. Should I try and go' for the connection pooling thing or monitor 
that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you 
mean by monitor - size?)

PS. comment on the "Why not move the data to the SSDs" you are exactly right. i 
don't think the SSD's will be big enough for the data within a not too long 
timeframe, so that is exactly why I want to keep my data on the "big" drives.
PPS. I talked with New Relic and it turns out there is something wrong with the 
disk monitoring tool, so that's why there was nothing in the disk charts but 
iostat showed a lot of activity.




-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Okay, So to understand this better before I go with that solution: 
In theory what difference should it make to the performance, to have a pool in 
front of the database, that all my workers and web servers connect to instead 
of connecting directly? Where is the performance gain coming from in that 
situation?

Den 30/11/2012 kl. 13.03 skrev "Kevin Grittner" :

> Niels Kristian Schjødt wrote:
> 
>>> You said before that you were seeing high disk wait numbers. Now
>>> it is zero accourding to your disk utilization graph. That
>>> sounds like a change to me.
> 
>> Hehe, I'm sorry if it somehow was misleading, I just wrote "a lot
>> of I/O" it was CPU I/O
> 
>>>> A lot of both read and writes takes more than a 1000 times as
>>>> long as they usually do, under "lighter" overall load.
>>> 
>>> As an odd coincidence, you showed your max_connections setting
>>> to be 1000.
>>> 
>>> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
> 
>> Back to the issue: Could it be that it is the fact that I'm using
>> ubuntus built in software raid to raid my disks, and that it is
>> not at all capable of handling the throughput?
> 
> For high performance situations I would always use a high quality
> RAID controller with battery-backed RAM configured for write-back;
> however:
> 
> The graphs you included suggest that your problem has nothing to do
> with your storage system. Now maybe you didn't capture the data for
> the graphs while the problem was occurring, in which case the
> graphs would be absolutely useless; but based on what slim data you
> have provided, you need a connection pool (like maybe pgbouncer
> configured in transaction mode) to limit the number of database
> connections used to something like twice the number of cores.
> 
> If you still have problems, pick the query which is using the most
> time on your database server, and post it with the information
> suggested on this page:
> 
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
> 
> -Kevin



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not 
really working as it should., and maybe new relic is not monitoring as It 
should.

If I do a "sudo iostat -k 1"
I get a lot of output like this:
Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda   0.00 0.00 0.00  0  0
sdb   0.00 0.00 0.00  0  0
sdc 546.00  2296.00  6808.00   2296   6808
sdd 593.00  1040.00  7416.00   1040   7416
md1   0.00 0.00 0.00  0  0
md0   0.00 0.00 0.00  0  0
md21398.00  3328.00 13064.00   3328  13064
md3   0.00 0.00 0.00  0  0

The storage thing is, that the sda and sdb is the SSD drives and the sdc and 
sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and 
the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting 
utilized - and I should expect that since they are serving my pg_xlog right? - 
so maybe I did something wrong in the setup. Here is the path I followed:

# 1) First setup the SSD drives in a software RAID1 setup:
#   
http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1
#
# 2) Then move the postgres pg_xlog dir
#   sudo /etc/init.d/postgresql-9.2 stop 
#   sudo mkdir -p /ssd/pg_xlog 
#   sudo chown -R  postgres.postgres /ssd/pg_xlog 
#   sudo chmod 700 /ssd/pg_xlog 
#   sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog 
#   sudo mv /var/lib/postgresql/9.2/main/pg_xlog 
/var/lib/postgresql/9.2/main/pg_xlog_old 
#   sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog 
#   sudo /etc/init.d/postgresql-9.2 start

Can you spot something wrong?


 
Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt 
:

> Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" :
> 
>> Niels Kristian Schjødt wrote:
>> 
>>> Okay, now I'm done the updating as described above. I did the
>>> postgres.conf changes. I did the kernel changes, i added two
>>> SSD's in a software RAID1 where the pg_xlog is now located -
>>> unfortunately the the picture is still the same :-( 
>> 
>> You said before that you were seeing high disk wait numbers. Now it
>> is zero accourding to your disk utilization graph. That sounds like
>> a change to me.
>> 
>>> When the database is under "heavy" load, there is almost no
>>> improvement to see in the performance compared to before the
>>> changes.
>> 
>> In client-visible response time and throughput, I assume, not
>> resource usage numbers?
>> 
>>> A lot of both read and writes takes more than a 1000 times as
>>> long as they usually do, under "lighter" overall load.
>> 
>> As an odd coincidence, you showed your max_connections setting to
>> be 1000.
>> 
>> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>> 
>> -Kevin
> 
> Hehe, I'm sorry if it somehow was misleading, I just wrote "a lot of I/O" it 
> was CPU I/O, it also states that in the chart in the link. 
> However, as I'm not very familiar with these deep down database and server 
> things, I had no idea wether a disk bottle neck could hide in this I/O, so i 
> went along with Shauns great help, that unfortunately didn't solve my issues. 
> Back to the issue: Could it be that it is the fact that I'm using ubuntus 
> built in software raid to raid my disks, and that it is not at all capable of 
> handling the throughput?
> 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" :

> Niels Kristian Schjødt wrote:
> 
>> Okay, now I'm done the updating as described above. I did the
>> postgres.conf changes. I did the kernel changes, i added two
>> SSD's in a software RAID1 where the pg_xlog is now located -
>> unfortunately the the picture is still the same :-( 
> 
> You said before that you were seeing high disk wait numbers. Now it
> is zero accourding to your disk utilization graph. That sounds like
> a change to me.
> 
>> When the database is under "heavy" load, there is almost no
>> improvement to see in the performance compared to before the
>> changes.
> 
> In client-visible response time and throughput, I assume, not
> resource usage numbers?
> 
>> A lot of both read and writes takes more than a 1000 times as
>> long as they usually do, under "lighter" overall load.
> 
> As an odd coincidence, you showed your max_connections setting to
> be 1000.
> 
> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
> 
> -Kevin

Hehe, I'm sorry if it somehow was misleading, I just wrote "a lot of I/O" it 
was CPU I/O, it also states that in the chart in the link. 
However, as I'm not very familiar with these deep down database and server 
things, I had no idea wether a disk bottle neck could hide in this I/O, so i 
went along with Shauns great help, that unfortunately didn't solve my issues. 
Back to the issue: Could it be that it is the fact that I'm using ubuntus built 
in software raid to raid my disks, and that it is not at all capable of 
handling the throughput?



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Den 28/11/2012 kl. 17.54 skrev Shaun Thomas :

> On 11/28/2012 10:19 AM, Niels Kristian Schjødt wrote:
> 
>> https://rpm.newrelic.com/public/charts/h2dtedghfsv
> 
> Doesn't this answer your question?
> 
> That iowait is crushing your server into the ground. It's no surprise updates 
> are taking several seconds. That update you sent us *should* execute on the 
> order of only a few milliseconds.
> 
> So I'll reiterate that you *must* move your pg_xlog location elsewhere. 
> You've got row lookup bandwidth conflicting with writes. There are a couple 
> other changes you should probably make to your config:
> 
>> checkpoint_segments = 16
> 
> This is not enough for the workload you describe. Every time the database 
> checkpoints, all of those changes in pg_xlog are applied to the backend data 
> files. You should set these values:
> 
> checkpoint_segments = 100
> checkpoint_timeout = 10m
> checkpoint_completion_target = 0.9
> 
> This will reduce your overall write workload, and make it less active. Too 
> many checkpoints massively reduce write throughput. With the settings you 
> have, it's probably checkpointing constantly while your load runs. Start with 
> this, but experiment with increasing checkpoint_segments further.
> 
> If you check your logs now, you probably see a ton of "checkpoint starting: 
> xlog" in there. That's very bad. It should say "checkpoint starting: time" 
> meaning it's keeping up with your writes naturally.
> 
>> work_mem = 160MB
> 
> This is probably way too high. work_mem is used every sort operation in a 
> query. So each connection could have several of these allocated, thus 
> starting your system of memory which will reduce that available for page 
> cache. Change it to 8mb, and increase it in small increments if necessary.
> 
>> So correct me if I'm wrong here: my theory is, that I have too many
>> too slow update queries, that then often end up in a situation, where
>> they "wait" for each other to finish, hence the sometimes VERY long
>> execution times.
> 
> Sometimes this is the case, but for you, you're running into IO contention, 
> not lock contention. Your 3TB RAID-1 is simply insufficient for this workload.
> 
> If you check your logs after making the changes I've suggested, take a look 
> at your checkpoint sync times. That will tell you how long it took the kernel 
> to physically commit those blocks to disk and get a confirmation back from 
> the controller. If those take longer than a second or two, you're probably 
> running into controller buffer overflows. You have a large amount of RAM, so 
> you should also make these two kernel changes to sysctl.conf:
> 
> vm.dirty_ratio = 10
> vm.dirty_writeback_ratio = 1
> 
> Then run this:
> 
> sysctl -p
> 
> This will help prevent large IO write spikes caused when the kernel decides 
> to write out dirty memory. That can make checkpoints take minutes to commit 
> in some cases, which basically stops all write traffic to your database 
> entirely.
> 
> That should get you going, anyway. You still need more/better disks so you 
> can move your pg_xlog directory. With your write load, that will make a huge 
> difference.
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email

Okay, now I'm done the updating as described above. I did the postgres.conf 
changes. I did the kernel changes, i added two SSD's in a software RAID1 where 
the pg_xlog is now located - unfortunately the the picture is still the same 
:-( 
When the database is under "heavy" load, there is almost no improvement to see 
in the performance compared to before the changes. A lot of both read and 
writes takes more than a 1000 times as long as they usually do, under "lighter" 
overall load. 

I added All the overview charts I can get hold on from new relic beneath. What 
am I overlooking? There must be an obvious bottleneck? Where should I dive in?

Database server CPU usage
https://rpm.newrelic.com/public/charts/cEdIvvoQZCr

Database server load average
https://rpm.newrelic.com/public/charts/cMNdrYW51QJ

Database server physical memory
https://rpm.newrelic.com/public/charts/c3dZBntNpa1

Database server disk I/O utulization
https://rpm.newrelic.com/public/charts/9YEVw6RekFG

Database server network I/O (Mb/s)
https://rpm.newrelic.com/public/charts/lKiZ0Szmwe7

Top 5 database operations by wall clock time
https://rpm.newrelic.com/public/charts/dCt45YH12FK

Database throughput
https://rpm.newrelic.com/public/charts/bIbtQ1mDzMI

Database response time
https://rpm.newrelic.com/public/charts/fPcNL8WA6xx

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Hi, I have started to implement your suggestions . I have a small error so far 
though. The "vm.dirty_writeback_ratio = 1" command rerurns: 
  error: "vm.dirty_writeback_ratio" is an unknown key
I'm on ubuntu 12.04


Den 28/11/2012 kl. 17.54 skrev Shaun Thomas :

> On 11/28/2012 10:19 AM, Niels Kristian Schjødt wrote:
> 
>> https://rpm.newrelic.com/public/charts/h2dtedghfsv
> 
> Doesn't this answer your question?
> 
> That iowait is crushing your server into the ground. It's no surprise updates 
> are taking several seconds. That update you sent us *should* execute on the 
> order of only a few milliseconds.
> 
> So I'll reiterate that you *must* move your pg_xlog location elsewhere. 
> You've got row lookup bandwidth conflicting with writes. There are a couple 
> other changes you should probably make to your config:
> 
>> checkpoint_segments = 16
> 
> This is not enough for the workload you describe. Every time the database 
> checkpoints, all of those changes in pg_xlog are applied to the backend data 
> files. You should set these values:
> 
> checkpoint_segments = 100
> checkpoint_timeout = 10m
> checkpoint_completion_target = 0.9
> 
> This will reduce your overall write workload, and make it less active. Too 
> many checkpoints massively reduce write throughput. With the settings you 
> have, it's probably checkpointing constantly while your load runs. Start with 
> this, but experiment with increasing checkpoint_segments further.
> 
> If you check your logs now, you probably see a ton of "checkpoint starting: 
> xlog" in there. That's very bad. It should say "checkpoint starting: time" 
> meaning it's keeping up with your writes naturally.
> 
>> work_mem = 160MB
> 
> This is probably way too high. work_mem is used every sort operation in a 
> query. So each connection could have several of these allocated, thus 
> starting your system of memory which will reduce that available for page 
> cache. Change it to 8mb, and increase it in small increments if necessary.
> 
>> So correct me if I'm wrong here: my theory is, that I have too many
>> too slow update queries, that then often end up in a situation, where
>> they "wait" for each other to finish, hence the sometimes VERY long
>> execution times.
> 
> Sometimes this is the case, but for you, you're running into IO contention, 
> not lock contention. Your 3TB RAID-1 is simply insufficient for this workload.
> 
> If you check your logs after making the changes I've suggested, take a look 
> at your checkpoint sync times. That will tell you how long it took the kernel 
> to physically commit those blocks to disk and get a confirmation back from 
> the controller. If those take longer than a second or two, you're probably 
> running into controller buffer overflows. You have a large amount of RAM, so 
> you should also make these two kernel changes to sysctl.conf:
> 
> vm.dirty_ratio = 10
> vm.dirty_writeback_ratio = 1
> 
> Then run this:
> 
> sysctl -p
> 
> This will help prevent large IO write spikes caused when the kernel decides 
> to write out dirty memory. That can make checkpoints take minutes to commit 
> in some cases, which basically stops all write traffic to your database 
> entirely.
> 
> That should get you going, anyway. You still need more/better disks so you 
> can move your pg_xlog directory. With your write load, that will make a huge 
> difference.
> 
> -- 
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> stho...@optionshouse.com
> 
> __
> 
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimize update query

[Niels Kristian Schjødt]

Okay guys,

Thanks for all the great help and advice already! Let me just clear some 
things, to make my question a little easier to answer :-)
Now my site is a search engine for used cars - not just a car shop with a few 
hundred cars.
The update query you look at, is an update that is executed once a day in 
chunks for all active adverts, so we know they are still for sale (one car can 
be advertised at several places hence several "adverts"). So it's not a 
"constant stream" but it has a fairly high volume especially at night time 
though.

A compressed version of my .conf looks like this (note: there is some tweaks at 
the end of the file)
  data_directory = '/var/lib/postgresql/9.2/main'
  hba_file = '/etc/postgresql/9.2/main/pg_hba.conf' 
  ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
  external_pid_file = '/var/run/postgresql/9.2-main.pid' 
  listen_addresses = '192.168.0.2, localhost'
  port = 5432
  max_connections = 1000 
  unix_socket_directory = '/var/run/postgresql'
  wal_level = hot_standby
  synchronous_commit = off
  archive_mode = onarchive_command = 'rsync -a %p 
postgres@192.168.0.4:/var/lib/postgresql/9.2/wals/%f https://rpm.newrelic.com/public/charts/h2dtedghfsv"; width="500" 
height="300" scrolling="no" frameborder="no">

This shows me, that the by far most time-consuming queries are updates (in 
general). On avg. a query like the one I showed you, take 1,3 sec (but often it 
takes several minutes - which makes me wonder). So correct me if I'm wrong 
here: my theory is, that I have too many too slow update queries, that then 
often end up in a situation, where they "wait" for each other to finish, hence 
the sometimes VERY long execution times. So my basic idea here is, that if I 
could reduce the cost of the updates, then I could get a hight throughput 
overall.

Here is a sample of the pgBadger analysis:

Queries that took up the most time (N) ^
RankTotal duration  Times executed  Av. duration (s)Query
1   1d15h28m38.71s  
948,711
0.15s   
COMMIT;

2   1d2h17m55.43s   
401,002
0.24s   
INSERT INTO "car_images" ( "car_id", "created_at", "image", "updated_at" ) 
VALUES ( '', '', '', '' ) returning "id";

3   23h18m33.68s
195,093
0.43s   
SELECT DISTINCT "cars".id FROM "cars" LEFT OUTER JOIN "adverts" ON 
"adverts"."car_id" = "cars"."id" LEFT OUTERJOIN "sellers" ON "sellers"."id" = 
"adverts"."seller_id" WHERE "cars"."sales_state" = '' AND "cars"."year" = 0 
AND"cars"."engine_size" = 0.0 AND ( ( "cars"."id" IS NOT NULL AND 
"cars"."brand" = '' AND "cars"."model_name" = ''AND "cars"."fuel" = '' AND 
"cars"."km" = 0 AND "cars"."price" = 0 AND "sellers"."kind" = '' ) ) LIMIT 0;

4   22h45m26.52s
3,374,133
0.02s   
SELECT "adverts".* FROM "adverts" WHERE ( source_name = '' AND md5 ( url ) = 
md5 ( '' ) ) LIMIT 0;

5   10h31m37.18s
29,671
1.28s   
UPDATE "adverts" SET "last_observed_at" = '', "data_source_id" = '' WHERE 
"adverts"."id" IN ( ... ) ;

6   7h18m40.65s 
396,393
0.07s   
UPDATE "cars" SET "updated_at" = '' WHERE "cars"."id" = 0;

7   7h6m7.87s   
241,294
0.11s   
UPDATE "cars" SET "images_count" = COALESCE ( "images_count", 0 ) + 0 WHERE 
"cars"."id" = 0;

8   6h56m11.78s 
84,571
0.30s   
INSERT INTO "failed_adverts" ( "active_record_object_class", 
"advert_candidate", "created_at", "exception_class","exception_message", 
"from_rescraper", "last_retried_at", "retry_count", "source_name", 
"stack_trace","updated_at", "url" ) VALUES ( NULL, '', '', '', '', NULL, NULL, 
'', '', '', '', '' ) returning "id";

9   5h47m25.45s 
188,402
0.11s   
INSERT INTO "adverts" ( "availability_state", "car_id", "created_at", 
"data_source_id", "deactivated_at","first_extraction", "last_observed_at", 
"price", "seller_id", "source_id", "source_name", "updated_at", "url" )VALUES ( 
'', '', '', '', NULL, '', '', '', '', '', '', '', '' ) returning "id";

10  3h4m26.86s  
166,235
0.07s   
UPDATE "adverts" SET "deactivated_at" = '', "availability_state" = '', 
"updated_at" = '' WHERE "adverts"."id" = 0;

(Yes I'm already on the task of improving the selects)

Den 28/11/2012 kl. 16.11 skrev Willem Leenen :

> 
> I assume that SQL databases ( Banks? Telecom?) can handle an used car shop. 
> No need for an unstructured data tool.
> 
> 
> 
> > +1, sql databases has limited number of inserts/updates per second. Even
> > with highend hardware you won't have more than XXX operations per
> > second. As Thomas said, you should feed something like nosql database
> > from www server and use other tool to do aggregation and batch inserts
> > to postgresql. It will scale much better.
> > 
> > Marcin

Re: [PERFORM] Database design - best practice

[Niels Kristian Schjødt]

Thanks for the advice.

Currently I see a lot of I/O related to update/inserts, so I'm trying to track 
down these guys at first. In relation to question 2, I read somewhere in the 
documentation that because of MVCC, the whole row has to be rewritten even 
though I just update one single column in that row. Hence if the table is wider 
(has more columns), the update will be slower. Does this match your 
understanding?

Den 28/11/2012 kl. 14.10 skrev Willem Leenen :

> Niels,
> 
> " I can't see why it would make sense to put that into a separate table and 
> join in the values " 
> You don't normalize for performance. People DEnormalize for performance.
> 
> 
> Questions: (AFAIK)
> 
> 1) This is a way to disaster. Get yourself a book on RDBMS from for example 
> Celko. Do NOT go against the flow of the RDBMS rules, as here in rule #1 
> atomic values of a column. 
> 
> 2) This is not the big fish you are after. First benchmark your setup and 
> compare the results with your desired performance level. First quantify your 
> problem, if there is any, before using tricks.
> 
> 3) A row will need more memory when it is wider, this may be amplified during 
> hash joins. 
> 
> 4) People DEnormalize for performance. 
> 
> 5) " Is it significantly faster to select * from a table with 20 columns, 
> than selecting the same 20 in a table with 150 columns?" 
> 
> I know the answer, but i encourage you to simply test this. I have seen lot's 
> of urban legends about performance ( including the dropping of the 
> referential integrity be cause that would make a difference ). 
> Of course , when it's a full table scan, and it are ALL disk reads, (or ALL 
> memory reads_) you can simply calculate it too. But just get into the habit 
> of  testing for learning.
> 
> 
> My advice:
> - know what performance you need.
> - test if you have this, varying tablecontent and systemload
> - do not tamper with the RDBMS rules, this will haunt you.
> - if you have the latest postgres version, you can use covering indexes: 
> tables aren't accessed at all, bypassing most of your questions. Check with 
> peers if you've got the indexes right.
> 
> Regards,
> Willem
> 
> 
> 
> > From: nielskrist...@autouncle.com
> > Subject: [PERFORM] Database design - best practice
> > Date: Wed, 28 Nov 2012 13:41:14 +0100
> > To: pgsql-performance@postgresql.org
> > 
> > Hi,
> > 
> > I'm on the hunt for some solid knowledge on a theoretical level about the 
> > performance of postgresql. My question is regarding best practices, and how 
> > architectural decisions might influence the performance. First a little 
> > background:
> > 
> > The setup:
> > I have a database which holds informations on used cars. The database has 
> > mainly 3 tables of interest for this case:
> > A cars table, an adverts table and a sellers table. One car has many 
> > adverts and one seller has many adverts. One advert belongs to one car and 
> > one seller.
> > The database is powering a website for searching used cars. When searching 
> > for used cars, the cars table is mainly used, and a lot of the columns 
> > should be directly available for searching e.g. color, milage, price, 
> > has_automatic_transmission etc.
> > 
> > So my main concern is actually about the cars table, since this one 
> > currently has a lot of columns (151 - I expect thats quite a lot?), and a 
> > lot of data (4 mil. rows, and growing). Now you might start by thinking, 
> > this could sound like a regular need for some normalization, but wait a 
> > second and let me explain :-)
> > The columns in this table is for the most very short stings, integers, 
> > decimals or booleans. So take for an example has_automatic_transmission 
> > (boolean) I can't see why it would make sense to put that into a separate 
> > table and join in the values. Or the milage or the price as another 
> > example. The cars table used for search is indexed quite a lot.
> > 
> > The questions:
> > Having the above setup in mind, what impact on performance, in terms of 
> > read performance and write performance, does it have, whether I do the 
> > following:
> > 1) In general would the read and/or the write on the database be faster, if 
> > I serialized some of the not searched columns in the table into a single 
> > text columns instead of let's say 20 booleans?
> > 2) Lets say I'm updating a timestamp in a single one of the 151 columns in 
> > the cars table. The update statement is using the id to find the car. Would 
> > the write performance of that UPDATE be affected, if the table had fewer 
> > columns?
> > 3) When adding a new column to the table i know that it becomes slower the 
> > more rows is in the table, but what about the "width" of the table does 
> > that affect the performance when adding new columns?
> > 4) In general what performance downsides do you get when adding a lot of 
> > columns to one table instead of having them in separate tables?
> > 5) Is it significantly faster to select * fro

[PERFORM] Optimize update query

[Niels Kristian Schjødt]

Hi, i have these update queries, that run very often, and takes too long time, 
in order for us to reach the throughput we are aiming at. However, the update 
query is very simple, and I can't figure out any way to improve the situation. 
The query looks like this:

UPDATE "adverts" SET "last_observed_at" = '2012-11-28 00:02:30.265154', 
"data_source_id" ='83d024a57bc2958940f3ca281bddcbf4' WHERE"adverts"."id" IN ( 
1602382, 4916432, 3221246, 4741057, 3853335, 571429, 3222740, 571736, 3544903, 
325378,5774338, 5921451, 4295768, 3223170, 5687001, 4741966, 325519, 580867, 
325721, 4412200, 4139598, 325567, 1616653,1616664, 6202007, 3223748, 325613, 
3223764, 325615, 4296536, 3854595, 4971428, 3224146, 5150522, 4412617, 
5073048,325747, 325771, 1622154, 5794384, 5736581, 1623767, 5686945, 3224627, 
5073009, 3224747, 3224749, 325809, 5687051,3224811, 5687052, 4917824, 5073013, 
3224816, 3224834, 4297331, 1623907, 325864, 1623947, 6169706, 325869, 
325877,3225074, 3225112, 325893, 325912, 3225151, 3225184, 3225175, 1624659, 
325901, 4033926, 325904, 325911, 4412835,1624737, 5073004, 5921434, 325915, 
3225285, 3225452, 4917672, 1624984, 3225472, 325940, 5380611, 325957, 
5073258,3225500, 1625002, 5923489, 4413009, 325952, 3961122, 363 ) ;

An explain outputs me the following:

"Update on adverts  (cost=0.12..734.27 rows=95 width=168)"
"  ->  Index Scan using adverts_pkey on adverts  (cost=0.12..734.27 rows=95 
width=168)"
"Index Cond: (id = ANY 
('{1602382,4916432,3221246,4741057,3853335,571429,3222740,571736,3544903,325378,5774338,5921451,4295768,3223170,5687001,4741966,325519,580867,325721,4412200,4139598,325567,1616653,1616664,6202007,3223748,325613,3223764,325615,4296536,3854595,4971428,3224146,5150522,4412617,5073048,325747,325771,1622154,5794384,5736581,1623767,5686945,3224627,5073009,3224747,3224749,325809,5687051,3224811,5687052,4917824,5073013,3224816,3224834,4297331,1623907,325864,1623947,6169706,325869,325877,3225074,3225112,325893,325912,3225151,3225184,3225175,1624659,325901,4033926,325904,325911,4412835,1624737,5073004,5921434,325915,3225285,3225452,4917672,1624984,3225472,325940,5380611,325957,5073258,3225500,1625002,5923489,4413009,325952,3961122,363}'::integer[]))"

So as you can see, it's already pretty optimized, it's just not enough :-) So 
what can I do? the two columns last_observed_at and data_source_id has an 
index, and it is needed elsewhere, so I can't delete those.

PS. I'm on postgres 9.2 on a server with 32gb ram, 8 cores and two 3T disks in 
a software raid 1 setup.

Is the only way out of this really a SSD disk?

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Database design - best practice

[Niels Kristian Schjødt]

Hi,

I'm on the hunt for some solid knowledge on a theoretical level about the 
performance of postgresql. My question is regarding best practices, and how 
architectural decisions might influence the performance. First a little 
background:

The setup:
I have a database which holds informations on used cars. The database has 
mainly 3 tables of interest for this case:
A cars table, an adverts table and a sellers table. One car has many adverts 
and one seller has many adverts. One advert belongs to one car and one seller.
The database is powering a website for searching used cars. When searching for 
used cars, the cars table is mainly used, and a lot of the columns should be 
directly available for searching e.g. color, milage, price, 
has_automatic_transmission etc.

So my main concern is actually about the cars table, since this one currently 
has a lot of columns (151 - I expect thats quite a lot?), and a lot of data (4 
mil. rows, and growing). Now you might start by thinking, this could sound like 
a regular need for some normalization, but wait a second and let me explain :-)
The columns in this table is for the most very short stings, integers, decimals 
or booleans. So take for an example has_automatic_transmission (boolean) I 
can't see why it would make sense to put that into a separate table and join in 
the values. Or the milage or the price as another example. The cars table used 
for search is indexed quite a lot.

The questions:
Having the above setup in mind, what impact on performance, in terms of read 
performance and write performance, does it have, whether I do the following:
1) In general would the read and/or the write on the database be 
faster, if I serialized some of the not searched columns in the table into a 
single text columns instead of let's say 20 booleans?
2) Lets say I'm updating a timestamp in a single one of the 151 columns 
in the cars table. The update statement is using the id to find the car. Would 
the write performance of that UPDATE be affected, if the table had fewer 
columns?
3) When adding a new column to the table i know that it becomes slower 
the more rows is in the table, but what about the "width" of the table does 
that affect the performance when adding new columns?
4) In general what performance downsides do you get when adding a lot 
of columns to one table instead of having them in separate tables?
5) Is it significantly faster to select * from a table with 20 columns, 
than selecting the same 20 in a table with 150 columns?

Hope there is some good answers out there :-)

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance