[PERFORM] Use of * affect the performance

[Marcos]

Hi,

I always think that use of * in SELECT affected in the performance,
becoming the search slowest.

But I read in the a Postgres book's that it increases the speed of
search.

And now What the more fast?

Thanks


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[PERFORM] Use of Stored Procedures and

[Marcos]

Hi,

I already read the documentation for to use the SPI_PREPARE and
SPI_EXEC... but sincerely I don't understand how I will use this
resource in my statements.

I looked for examples, but I din't good examples :(..

Somebody can help me?

Thanks.

Marcos.




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[PERFORM] Simple Question of Performance ILIKE or Lower

[Marcos]

Hi,

I have a simple question about performance using two resources.

What's have the best performance?

lower( col1 ) LIKE lower( 'myquestion%' )

OR

col1 ILIKE 'myquestion%'

Thanks.



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Use of Stored Procedures and

[Marcos]

> What statements?  

Sorry. Statements is my code.

> What problem are you trying to solve?

I want know how I make to use a prepared plan
( http://www.postgresql.org/docs/8.1/static/sql-prepare.html ). I read
that I need to use the SPI_PREPARE and SPI_EXEC in my code, but I didn't
understand how make it.

Thanks


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Use of Stored Procedures and

[Marcos]

> Which interface are you using to connect to PostgreSQL? libpq, libpqxx,
> pgjdbc, python-popy?
> 
> E. G. PGJDBC handles prepared plans transparently by using the
> PreparedStatement class.
> 
> If you use command line PSQL, you can use the PREPARE commands.

I'm using the adodb to call the stored procedure (plpgsql).

Thanks..


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] [PERFORMANCE] Stored Procedures

[Marcos]

Em Sex, 2006-01-20 às 15:34 -0600, Jim C. Nasby escreveu:
> On Fri, Jan 20, 2006 at 08:38:23PM +0100, Rikard Pavelic wrote:
> > This would solve problems with prepare which is per session, so for 
> > prepared function to be
> > optimal one must use same connection.
> 
> If you're dealing with something that's performance critical you're not
> going to be constantly re-connecting anyway, so I don't see what the
> issue is.

This one was my doubt, perhaps in based desktop applications this is
true, but in web applications this is not the re-connecting is
constant :(.

Then the preprare not have very advantage because your duration is per
session.

Marcos.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] [PERFORMANCE] Stored Procedures

[Marcos]

Hi Markus

> You really should consider using a connection pool (most web application
> servers provide pooling facilities) or some other means to keep the
> connection between several http requests.

Yes. I'm finding a connection pool, I found the pgpool but yet don't
understand how it's work I'm go read more about him.

Thanks

Marcos


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[PERFORM] Creating a correct and real benchmark

[Marcos]

Hi, 

I'm developing a search engine using the postgresql's databas. I've
already doing some tunnings looking increase the perform. 

Now, I'd like of do a realistic test of perfom with number X of queries
for know the performance with many queries. 

What the corret way to do this? 

Thanks.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Creating a correct and real benchmark

[Marcos]

Thanks for advises :-D.

Marcos


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[PERFORM] Decide between Postgresql and Mysql (help of comunity)

[Marcos]

Hi,

I'm a Postgresql's user and I think that it's very very good and
robust. 

In my work we're confuse between where database is the best choose:
Postgresql or Mysql. The Mysql have the reputation that is very fast
working in the web but in our application we are estimating many access
simultaneous, then I think that the Postgresql is the best choice. 

Am I right?

Our server have 1 GB of RAM, how many users can it support at the same
time with this memory?

Thanks in advanced

Marcos


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Decide between Postgresql and Mysql (help of

[Marcos]

> So, what exactly are you planning on doing?

The application will be a chat for web, the chats will be stored in the
server. In a determined interval of time... more or less 2 seconds, the
application will be looking for new messages.

I believe that it will make many accesses. The write in disc will be
constant.

Thanks :o)

Marcos


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can helpe me.

[Marcos Borges]

07/12/2006 04:31

SQL_CALC_FOUND_ROWS in POSTGRESQL

In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax.
SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name
<> '' LIMIT 0, 10
to have the recorset data.
and
SELECT FOUND_ROWS();
to have the total of registers found.

I dont want to use the command count(*), because the performance will
fall down, depending of the quantyt of tables and "joins".

The Data base postgresql have something similar ???


---

07/12/2006 04:31

SQL_CALC_FOUND_ROWS no POSTGRESQL
Dúvida NINJA no POSTGRESQL
No mysql utilizo o comando SQL_CALC_FOUND_ROWS na seguinte sintax
SELECT SQL_CALC_FOUND_ROWS nome, email, telefone FROM tabela WHERE nome
<> '' LIMIT 0, 10
para obter o meu recordset
e
SELECT FOUND_ROWS();
para obter o total de resgitros que realmente existem em minha tabela
condicionado pelo WHERE, sem ser limitado pelo LIMIT.

Não quero usar o count(*) pois o desempenho cai dependendo da
quantidade de tabelas selecionadas e quantidade de registros.


O postgreSQL possui algo similar? Caso sim pode me informar qual e
fornecer um exemplo. 

Re: [PERFORM] PgPool II configuration with PostgreSQL 8.4

[Marcos Ortiz]

El 06/05/2010 6:17, Neha Mehta escribió:

  
  
  
  
  
  
   
  Hi,
   
  I
am trying to have synchronous master-master replication in
PostgreSQL8.4 using PgPool II. I am not able to configure PgPool on the
system, it gives me an error, libpq is not installed or libpq is old.
   
  I
have tried the command , ./configure –with-pgsql = PostgreSQL dir
–with-pgsql-libdir = PostgreSQL dir/lib/
   
  But
still could not resolve the issue.
   
   
  
  Thanks
& Regards,
  Neha
Mehta
   
   
  
  
  
  This Email may contain
confidential or privileged information for the intended recipient (s)
If you are not the intended recipient, please do not use or disseminate
the information, notify the sender and delete it from your system.
  
  
__

PgPool-II is a master-slave system, if you want to use a Master-Master
System yo can take a look to Bucardo(http://www.bucardo.org)
Which is the error?
Have you installed all dependencies of PgPool? libpq5 for example.
Which is you operating system?

We need all this information to help to find a certain solution.

Regards

Re: [PERFORM] debugging handle exhaustion and 15 min/ 5mil row delete

[Marcos Ortiz]

El 07/05/2010 15:37, Mark Stosberg escribió:

Hello,

We've been a satified user of PostgreSQL for several years, and use it
to power a national pet adoption website: http://www.adoptapet.com/

Recently we've had a regularly-timed middle-of-the-night problem where
database handles are exhausted for a very brief period.

In tracking it down, I have found that the event seems to correspond to
a time when a cron script is deleting from a large logging table, but
I'm not certain if this is the cause or a correlation.

We are deleting about 5 million rows from a time-based logging table
that is replicated by Slony. We are currently using a single delete
statement, which takes about 15 minutes to run. There is no RI on the
table, but the use of Slony means that a trigger call and action is made
for every row deleted, which causes a corresponding insertion in another
table so the deletion can be replicated to the slave.

My questions:

- Could this kind of activity lead to an upward spiral in database
   handle usage?

- Would it be advisable to use several small DELETE statements instead,
   to delete rows in batches of 1,000. We could use the recipe for this
   that was posted earlier to this list:

   delete from table where pk in
 (select pk from table where delete_condition limit X);

Partitions seems attractive here, but aren't easy to use Slony. Perhaps
once we migrate to PostgreSQL 9.0 and the hot standby feature we can
consider that.

Thanks for your help!

 Mark

  . . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark StosbergPrincipal Developer
m...@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
  . . . . . http://www.summersault.com/ . . . . . . . .



   
You can use TRUNCATE instead DELETE. TRUNCATE is more efficient and 
faster that DELETE.
Now, we need more information about your system to give you a certain 
solution:

Are you using a RAID controller for you data?
Do you have separated the xlog directory from the data directory?
Which is your Operating System?
Which is you architecture?

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] debugging handle exhaustion and 15 min/ 5mil row delete

[Marcos Ortiz]

El 07/05/2010 16:10, Mark Stosberg escribió:
   

You can use TRUNCATE instead DELETE. TRUNCATE is more efficient and
faster that DELETE.
 

Thanks for the suggestion. However, TRUNCATE is not compatible with
Slony, and we also have some rows which remain in table.

   

Now, we need more information about your system to give you a certain
solution:
Are you using a RAID controller for you data?
 

Yes.

   

Do you have separated the xlog directory from the data directory?
 

No.

   

Which is your Operating System?
 

FreeBSD.

   

Which is you architecture?
 

i386.

Thanks for the feedback. I'm going to try batching the deletes for now,
which is approach was worked well for some of our other long-running
deletes.

 Mark

   

Have you valorated to use a 64 bits version of FreeBSD for that?
The 64 bits OS can help you very much on large databases because yo can 
use actually all available RAM that you have on the server.


Many experts on this list recommende to separate the xlog directory on a 
RAID 1 configuration and the data directory on RAID 10 to obtain a 
better performance.
The filesystems are very diverse, but I ´ve seen that ZFS is very useful 
on these cases.


Which version of Slony-I are you using?

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] Server Configuration

[Marcos Ortiz]

On Wed, 2011-02-02 at 15:15 -0300, Cesar Arrieta wrote:
> Hi, I have a Server with Fedora Core 11, Tomcat and Postgresql 8.3.
> With Hardware:
> * 8GB RAM
> * 8 processors Intel Xeon E5520 @2.27GHz
> * 250GB SATA DISK
> 
> Actually, it serves at most 250 connections. 
> The problem happends when it serves many many connections at a time,
> tables and queries began to get blocked, then I have to kill some
> processes 
> in order to allow other people continue working.
> 
> Wich recommendations could you give me for to configure
> postgresql.conf, and could it be eficcient to buy another server with
> almost same hardware
> in order to use pgPool2 with replication, load balance and parallel
> query?.
My first recommedation is to update your PostgreSQL version to 9.0 and
later you can use the PgPool-II version 3.0 in order to use the Hot
standby/Streaming Replication features with it.

Here is a example of the configuration of PgPool-II 3.0.1 and
PostgreSQL-9.0.2
http://lists.pgfoundry.org/pipermail/pgpool-general/2011-February/003338.html

Regards



-- 
Ing. Marcos Luís Ortíz Valmaseda
System Engineer -- Database Administrator

Centro de Tecnologías de Gestión de Datos (DATEC)
Universidad de las Ciencias Informáticas
http://postgresql.uci.cu


-- 
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] i want to ask monitory peformance memory postgresql with automatically

[Marcos Ortiz]

you can use Hyperic HQ too from SpringSource (a division of Vmware), but 
the most usfel tools are the command tools (iostat, vmstat, top, free, lsof)

Regards

El 6/7/2011 5:58 AM, Craig Ringer escribió:

On 7/06/2011 3:47 PM, Didik Prasetyo wrote:

Hi friend I Want to ask, is there any solutions or tools for monitoring
memory performance in postgre automatically, for example, will send
allert if Peformance memory has exceeded 90%


Use standard system monitoring tools like Nagios. There is a 
PostgreSQL agent for Nagios that will help do some postgresql-specific 
monitoring, but most of the monitoring you will want to do is system 
level and is already supported by Nagios and similar tools.




--
Marcos Luís Ortíz Valmaseda
 Software Engineer (UCI)
 http://marcosluis2186.posterous.com
 http://twitter.com/marcosluis2186
  



--
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] postgresql.conf setting for max_fsm_pages

[Marcos Ortiz]

On 04/04/2012 05:22 AM, ahchuan wrote:

Hi All,


I am new in using postgresSQL, I now support a system that been
running on postgressql. Recently I found that the database are
consuming the diskspace rapidly, it starting from 9GB and it now grow
until 40GB within 4-5 month.

I try to do a full vacuum to the database but then i get this error

NOTICE:  number of page slots needed (1277312) exceeds max_fsm_pages
(819200)
HINT:  Consider increasing the configuration parameter "max_fsm_pages"
to a value over 1277312.
VACUUM
If you using max_fsm_pages, you are using the version 8.3. I recommend 
to you that
you should update your system to major version. In PostgreSQL 9.0, for 
example, VACUUM

FULL was rewritten and it does a better job.
Try to use autovacumm = on always


I did a vacuum verbose.
postgres=# vacuum verbose;

and below is the result i got.

INFO:  free space map contains 1045952 pages in 1896 relations
DETAIL:  A total of 819200 page slots are in use (including overhead).
1114192 page slots are required to track all free space.
Current limits are:  819200 page slots, 2000 relations, using 5007 kB.
NOTICE:  number of page slots needed (1114192) exceeds max_fsm_pages
(819200)
HINT:  Consider increasing the configuration parameter "max_fsm_pages"
to a value over 1114192.
VACUUM



As from the postgres documentation, it was advice to set it to 20K to
200K which my current setting is set to 819200 which also over 200K
already, so i just wonder what is the max number that i can set for
the max_fsm_pages?

My advice that you have to test your environment with a double value to 
1114192,

postgres# SET max_fsm_pages = 2228384; if you need to use 8.3 versions yet.

But, again, you should upgrade your system to major version. There are a 
lot of performance improvements

in the new versions.



Is that any impact if i set the value to over 2M ?

Thanks.

Regards,
Chio Chuan



--
Marcos Luis Ortíz Valmaseda (@marcosluis2186)
 Data Engineer at UCI
 http://marcosluis2186.posterous.com



10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS 
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Re: [PERFORM] Slow Performance on a XEON E5504

[Marcos Ortiz]

On 08/24/2012 05:47 AM, Felix Schubert wrote:

Hello List,

I've got a system on a customers location which has a XEON E5504 @ 
2.00GHz Processor (HP Proliant)


It's postgres 8.4 on a Debian Squeeze System running with 8GB of ram:

The Postgres Performance on this system measured with pgbench is very 
poor:


transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 40
number of transactions per client: 100
number of transactions actually processed: 4000/4000
tps = 158.283272 (including connections establishing)
tps = 158.788545 (excluding connections establishing)

The same database on a Core i7 CPU 920 @ 2.67GHz, 8 cores with 8GB RAM 
same distro and Postgresql Version is much faster:


transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 40
number of transactions per client: 100
number of transactions actually processed: 4000/4000
tps = 1040.534002 (including connections establishing)
tps = 1065.215134 (excluding connections establishing)

Even optimizing the postgresql.conf values doesn't change a lot on the 
tps values. (less than 10%)


Tried Postgresql 9.1 on the Proliant:
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 4000/4000
tps = 53.114978 (including connections establishing)
tps = 53.198667 (excluding connections establishing)

Next was to compare the diskperformance which was much better on the 
XEON than on the Intel i7.


Any idea where to search for the bottleneck?

Regards, Felix.
There are many question there:
- Are you using the same disc models in both systems (Xeon and Intel i7)?
- Which are the values for work_mem, shared_buffers, 
maintainance_work_men, effective_io_cache, etc ?

- Is PostgreSQL the unique service in these servers?

My first advice is that PostgreSQL 9.2 was released today, which has a 
lot of major performance improvements, so,
you should update your both installations to this new version to obtain 
a better performance, security and stability.


Best wishes


Mit freundlichen Grüßen

Felix Schubert

FEScon
... and work flows!

felix schubert
haspelgasse 5
69117 heidelberg

mobil: +49-151-25337718
mail: in...@fescon.de 
skype: fesmac








10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS 
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Re: [PERFORM] AppScale backend datastore (NoSQL again kind of)

[Marcos Ortiz]

Regards, Ross.
Dimitri Fontaine gave a excellent talk in the last PgCon about the 
migration of Fotolog from MySQL to
PostgreSQL with amazing advices around this, so you can contact him for 
his advice.


On 09/13/2012 02:11 PM, Ross Reedstrom wrote:

Hey PostgreSQL speed demons -
At work, we're considering an AppScale deployment (that's the Google App Engine
roll-your-own http://appscale.cs.ucsb.edu/). It supports multiple technologies
to back the datastore part of the platform (HBase, Hypertable, MySQL Cluster,
Cassandra, Voldemort, MongoDB, MemcacheDB, Redis). Oddly enough, in performance
tests, the MySQL Cluster seems like the general winner (their tests, we haven't
done any yet)

So, my immediate thought was "How hard would it be to replace the MySQL
Cluster bit w/ PostgreSQL?" I'm thinking hot standby/streaming rep. May or may
not need a pooling solution in there as well (I need to look at the AppScale
abstraction code, it may already be doing the pooling/direction bit.)

It depends of many factors:
- size of the MySQL cluster
- size of the involving data, etc

For the pooling solution, I recommend you to see PgBouncer, it´s a great
project widely used for this topic.


Any thoughts from those with more experience using/building PostgreSQL clusters?
Replacing MySQL Cluster? Clearly they must be using a subset of functionality,
since they support so many different backend stores. I'll probably have to
set up an instance of all this, run some example apps, and see what's actually
stored to get a handle on it. The GAE api for the datastore is sort of a ORM,
w/ yet another query language, that seems to map to SQL better than to NoSQL,
in any case. There seems to be a fairly explicit exposure of a table==class
sort of mapping.

Ross

Best wishes
--

Marcos Luis Ortíz Valmaseda
*Data Engineer && Sr. System Administrator at UCI*
about.me/marcosortiz <http://about.me/marcosortiz>
My Blog <http://marcosluis2186.posterous.com>
Tumblr's blog <http://marcosortiz.tumblr.com/>
@marcosluis2186 <http://twitter.com/marcosluis2186>





10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS 
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Re: [PERFORM] 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

[Marcos Ortiz]

Regards, Nikolay.
Like Daniel said to you, I encourage to inform all your findings to the 
LKML to

report all these problems.

Only one las t question: Did you tune the postgresql.conf for every 
system? or

Did you use the default configuration ?

Best wishes
On 09/14/2012 04:45 AM, Daniel Farina wrote:

On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky  wrote:

Hi
I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
990X).

CentOS 6.3 x86_64
PostgreSQL 9.2
cpufreq scaling_governor - performance

# /etc/init.d/postgresql initdb
# echo "fsync = off" >> /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql start
# su - postgres
$ psql
# create database pgbench;
# \q

# pgbench -i pgbench && pgbench -c 10 -t 1 pgbench
tps = 4670.635648 (including connections establishing)
tps = 4673.630345 (excluding connections establishing)[/code]

On kernel 3.5.3:
tps = ~5800

1) Host 1 - 15-20% performance drop
AMD Phenom(tm) II X6 1090T Processor
MB: AMD 880G
RAM: 16 Gb DDR3
SSD: PLEXTOR PX-256M3 256Gb

2) Host 2 - 15-20% performance drop
AMD Phenom(tm) II X6 1055T Processor
MB: AMD 990X
RAM: 32 Gb DDR3
SSD: Corsair Performance Pro 128Gb

3) Host 3 - no problems - same performance
Intel E6300
MB: Intel® P43 / ICH10
RAM: 4 Gb DDR3
HDD: SATA 7200 rpm

Kernel config - http://pastebin.com/cFpg5JSJ

Any ideas?

Did you tell LKML? It seems like a kind of change that could be found
using git bisect of Linux, albiet laboriously.



--

Marcos Luis Ortíz Valmaseda
*Data Engineer && Sr. System Administrator at UCI*
about.me/marcosortiz <http://about.me/marcosortiz>
My Blog <http://marcosluis2186.posterous.com>
Tumblr's blog <http://marcosortiz.tumblr.com/>
@marcosluis2186 <http://twitter.com/marcosluis2186>





10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS 
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Re: [PERFORM] problems with large objects dump

[Marcos Ortiz]

On 10/11/2012 05:46 PM, Sergio Gabriel Rodriguez wrote:

Hi,
  I tried with Postgresql 9.2 and the process used to take almost a 
day and a half, was significantly reduced to 6 hours, before failing 
even used to take four hours. My question now is, how long should it 
take the backup for a 200GB database with 80% of large objects?

Regards, Sergio.
That´s depends of several things.



Hp proliant Xeon G5
32 GB RAM
OS SLES 10 + logs --> raid 6
data-->raid 6

Can you share your postgresql.conf here?
Which filesystem are you using for your data directory?
What options are you using to do the backup?



thanks!

On Thu, Sep 20, 2012 at 12:53 PM, Sergio Gabriel Rodriguez 
mailto:sgrodrig...@gmail.com>> wrote:


On Thu, Sep 20, 2012 at 11:35 AM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote:

 You wouldn't happen to be
trying to use a 9.0 or later pg_dump would you?  Exactly what
8.4.x
release is this, anyway?



Tom, thanks for replying, yes, we tried it with postgres postgres
9.1 and 9.2 and the behavior is exactly the same. The production
version is 8.4.9

Greetings,

    sergio.




--

Marcos Luis Ortíz Valmaseda
about.me/marcosortiz <http://about.me/marcosortiz>
@marcosluis2186 <http://twitter.com/marcosluis2186>



10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS 
INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

[Marcos Ortiz]

Regards, Petr.
Tuning PostgreSQL is not just change the postgresql.conf, it includes 
more things like:

- the filesystem that you are using
- the kernel version that you using (particularly in Linux systems)
- the tuning to kernel variables
- the type of discs that you are using (SSDs are very fast, like you saw 
in your iMac system)


On 10/30/2012 02:44 PM, Petr Praus wrote:
I just found one particularly interesting fact: when I perform the 
same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 
and 16GB RAM, I don't experience the slow down.

Specifically:
set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

When I do exactly the same query (the one from my previous post) with 
exactly the same data on the server:

I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

The Mac has SSD so it's understandably faster, but it exhibits a 
behavior I would expect. What am I doing wrong here?


Thanks.

On 30 October 2012 14:08, Petr Praus <mailto:p...@praus.net>> wrote:


Hello,

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core
machine with 16GB of RAM. The server is dedicated to this
database, the disks are local RAID10. Given that the default
postgresql.conf is quite conservative regarding memory settings, I
thought it might be a good idea to allow Postgres to use more
memory. To my surprise, following advice in the performance tuning
guide on Postgres wiki[2] significantly slowed down practically
every query I run but it's more noticeable on the more complex
queries.

I also tried running pgtune[1] which gave the following
recommendation with more parameters tuned, but that didn't change
anything. It suggests shared_buffers of 1/4 of RAM size which
seems to in line with advice elsewhere (and on PG wiki in particular).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

I tried reindexing the whole database after changing the settings
(using REINDEX DATABASE), but that didn't help either. I played
around with shared_buffers and work_mem. Gradually changing them
from the very conservative default values (128k / 1MB) also
gradually decreased performance.

I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit
seems to be that Hash Join is significantly slower. It's not clear
to me why.

To give some specific example, I have the following query. It runs
in ~2100ms on the default configuration and ~3300ms on the
configuration with increased buffer sizes:

select count(*) from contest c
left outer join contestparticipant cp on c.id
<http://c.id>=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
<http://cp.id>
left outer join staffmember sm on cp.id
<http://cp.id>=sm.contestparticipantid
left outer join person p on p.id <http://p.id>=cp.personid
left outer join personinfo pi on pi.id
<http://pi.id>=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

 - Default buffers: http://explain.depesz.com/s/xaHJ
 - Bigger buffers: http://explain.depesz.com/s/Plk

The tables don't have anything special in them

The question is why am I observing decreased performance when I
increase buffer sizes? The machine is definitely not running out
of memory. Allocation if shared memory in OS is (`shmmax` and
`shmall`) is set to very large values, that should not be a
problem. I'm not getting any errors in the Postgres log either.
I'm running autovacuum in the default configuration but I don't
expect that has anything to do with it. All queries were run on
the same machine few seconds apart, just with changed
configuration (and restarted PG).

I also found a blog post [3] which experiments with various
work_mem values that run into similar behavior I'm experiencing
but it doesn't really explain it.

  [1]: http://pgfoundry.org/projects/pgtune/
  [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
  [3]:
http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/

Thanks,
Petr Praus

PS:
I also posted the question here:

http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-down
but a few people suggested




--

Marcos Luis Ortíz Valmaseda
about.me/mar

[PERFORM] Thought you'd find this interesting

[Marcos Luis]

Hi!


I’ve just learned about the water crisis and thought you would be interested to 
check out this story:

https://waterforward.charitywater.org/et/FWIshxIN


Let me know what you think!



Thanks,
Marcos



--
Sent via WaterForward, an initiative of charity: water
WaterForward, 387 Tehama Street, San Francisco, CA 94103, USA.
Click here to unsubscribe: 
https://waterforward.charitywater.org/opt_out?et=FWIshxIN

Re: [PERFORM] Horribly slow hash join

[Marcos Martínez(R)]

I didn't follow the conversation from the begining, bu I imagine that you
could improve
performance using the value (work_units % (2^32) ) instead of work_units.
You could even make an index on this value. Like that, the HASH function
will work well. This is not a good solution, but ...

For example.

create index ind1 on table1 ( work_units % (2^32) );

create index ind1 on table2 ( work_units % (2^32) );

Select * from table1 join table2 on (table1.work_units % (2^32) ) =
(table2.work_units % (2^32) )


- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Jim C. Nasby" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, April 17, 2004 6:08 PM
Subject: Re: [PERFORM] Horribly slow hash join


> [ resending because I fat-fingered the cc: to the list ]
>
> I see the problem: all the entries in your work_units column have the
> low 32 bits equal to zero.
>
> regression=# select distinct work_units % (2^32)::bigint from
Trank_work_overall;
>  ?column?
> --
> 0
> (1 row)
>
> The hash function for int8 only takes the low word into account, so all
> of the entries end up on the same hash chain, resulting in worst-case
> behavior.  This applies to both your hash join and hash aggregate cases.
>
> We could change the hash function, perhaps, but then we'd just have
> different cases where there's a problem ... hashing will always fail on
> *some* set of inputs.  (Also, I have been harboring some notions of
> supporting cross-type hash joins for integer types, which will not work
> unless small int8 values hash the same as int4 etc.)
>
> I guess the real issue is why are you encoding work_units like that?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] SSD + RAID

[Marcos Ortiz Valmaseda]

This is very fast.
On IT Toolbox there are many whitepapers about it.
On the ERP and DataCenter sections specifically.

We need that all tests that we do, we can share it on the
Project Wiki.

Regards

On Nov 13, 2009, at 7:02 AM, Karl Denninger wrote:


Laszlo Nagy wrote:

Hello,

I'm about to buy SSD drive(s) for a database. For decision making, I
used this tech report:

http://techreport.com/articles.x/16255/9
http://techreport.com/articles.x/16255/10

Here are my concerns:

  * I need at least 32GB disk space. So DRAM based SSD is not a real
option. I would have to buy 8x4GB memory, costs a fortune. And
then it would still not have redundancy.
  * I could buy two X25-E drives and have 32GB disk space, and some
redundancy. This would cost about $1600, not counting the RAID
controller. It is on the edge.
  * I could also buy many cheaper MLC SSD drives. They cost about
$140. So even with 10 drives, I'm at $1400. I could put them in
RAID6, have much more disk space (256GB), high redundancy and
POSSIBLY good read/write speed. Of course then I need to buy a
good RAID controller.

My question is about the last option. Are there any good RAID cards
that are optimized (or can be optimized) for SSD drives? Do any of  
you

have experience in using many cheaper SSD drives? Is it a bad idea?

Thank you,

 Laszlo


Note that some RAID controllers (3Ware in particular) refuse to
recognize the MLC drives, in particular, they act as if the OCZ Vertex
series do not exist when connected.

I don't know what they're looking for (perhaps some indication that
actual rotation is happening?) but this is a potential problem  
make

sure your adapter can talk to these things!

BTW I have done some benchmarking with Postgresql against these drives
and they are SMOKING fast.

-- Karl

--
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] random_page_cost = 2.0 on Heroku Postgres

[Marcos Ortiz Valmaseda]

On 08/02/12 21:15, Peter van Hardenberg wrote:

Having read the thread, I don't really see how I could study what a
more principled value would be.

That said, I have access to a very large fleet in which to can collect
data so I'm all ears for suggestions about how to measure and would
gladly share the results with the list.

Peter

On Wed, Feb 8, 2012 at 5:39 PM, Scott Marlowe  wrote:

On Wed, Feb 8, 2012 at 5:50 PM, Josh Berkus  wrote:

On 2/7/12 4:59 PM, Peter van Hardenberg wrote:

Per the thread from last month, I've updated the default
random_page_cost on Heroku Postgres to reduce the expected cost of a
random_page on all new databases.

This is because Heroku uses AWS storage, which has fast seeks but poor
throughput compared to internal disk on a standard system, BTW.

Also judging by the other thread, it might be something to stop closer
to 1.2 to 1.4 or something.

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



You can execute several queries with the three different values provided 
by Scott and Josh.

- SET random_page_cost  = 2.0
First execution of the queries with EXPLAIN ANALYZE
- SET random_page_cost  = 1.4
Second execution of the queries with EXPLAIN ANALYZE
- SET random_page_cost  = 1.2
Second execution of the queries with EXPLAIN ANALYZE

And then, you can compare the pattern behind these queries executions
Regards,

--
Marcos Luis Ortíz Valmaseda
 Sr. Software Engineer (UCI)
 http://marcosluis2186.posterous.com
 http://www.linkedin.com/in/marcosluis2186
 Twitter: @marcosluis2186




Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE 
ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.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] random_page_cost = 2.0 on Heroku Postgres

[Marcos Ortiz Valmaseda]

On 09/02/12 00:09, Greg Sabino Mullane wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160



You can execute several queries with the three different values provided
by Scott and Josh.
- SET random_page_cost  = 2.0
First execution of the queries with EXPLAIN ANALYZE
- SET random_page_cost  = 1.4
Second execution of the queries with EXPLAIN ANALYZE
- SET random_page_cost  = 1.2
Second execution of the queries with EXPLAIN ANALYZE

Well, such a tool would ideally be smarter than that, such that
you would run EXPLAIN and compare to the previous plan and
only run EXPLAIN ANALYZE if the plan changed. One could even
decrement rpc slowly and find out at one points it changes,
which would be more interesting than testing arbitrary numbers.
Would lead to some really sweet graphs as well. :)

Well, the MyYearBook.com´s guys built something seemed called Posuta, I 
don´t know is this project is alive, but we can ask to them 
(michael.glaesem...@myyearbook.com).


http://area51.myyearbook.com
Posuta can be a starting point for it. It uses Ruby and Clojure for core 
functionalities, jQuery/Flot for graphics,


--
Marcos Luis Ortíz Valmaseda
 Sr. Software Engineer (UCI)
 http://marcosluis2186.posterous.com
 http://www.linkedin.com/in/marcosluis2186
 Twitter: @marcosluis2186




Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE 
ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.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] 10K vs 15k rpm for analytics

[Ing. Marcos Ortiz Valmaseda]

Pierre C escribió:
On Tue, 09 Mar 2010 08:00:50 +0100, Greg Smith  
wrote:



Scott Carey wrote:
For high sequential throughput, nothing is as optimized as XFS on 
Linux yet.  It has weaknesses elsewhere however.




When files are extended one page at a time (as postgres does) 
fragmentation can be pretty high on some filesystems (ext3, but NTFS 
is the absolute worst) if several files (indexes + table) grow 
simultaneously. XFS has delayed allocation which really helps.



I'm curious what you feel those weaknesses are.


Handling lots of small files, especially deleting them, is really slow 
on XFS.

Databases don't care about that.

There is also the dark side of delayed allocation : if your 
application is broken, it will manifest itself very painfully. Since 
XFS keeps a lot of unwritten stuff in the buffers, an app that doesn't 
fsync correctly can lose lots of data if you don't have a UPS.


Fortunately, postgres handles fsync like it should be.

A word of advice though : a few years ago, we lost a few terabytes on 
XFS (after that, restoring from backup was quite slow !) because a 
faulty SCSI cable crashed the server, then crashed it again during 
xfsrepair. So if you do xfsrepair on a suspicious system, please image 
the disks first.


And then Which file system do you recommend for the PostgreSQL data 
directory? I was seeying that ZFS brings very cool features for that. 
The problem with ZFS is that this file system is only on Solaris, 
OpenSolaris, FreeBSD and Mac OSX Server, and on Linux systems not  What 
do you think about that?

Regards

--
---- 
-- Ing. Marcos Luís Ortíz Valmaseda   --

-- Twitter: http://twitter.com/@marcosluis2186--
-- FreeBSD Fan/User   --
-- http://www.freebsd.org/es  --
-- Linux User # 418229--
-- Database Architect/Administrator   --
-- PostgreSQL RDBMS   --
-- http://www.postgresql.org  --
-- http://planetpostgresql.org--
-- http://www.postgresql-es.org   --

-- Data WareHouse -- Business Intelligence Apprentice --
-- http://www.tdwi.org--
 
-- Ruby on Rails Fan/Developer--

-- http://rubyonrails.org --


Comunidad Técnica Cubana de PostgreSQL
http://postgresql.uci.cu
http://personas.grm.uci.cu/+marcos

Centro de Tecnologías de Gestión de Datos (DATEC) 
Contacto: 
   Correo: centa...@uci.cu
   Telf: +53 07-837-3737
 +53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu 





--
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] Postgres DB maintainenance - vacuum and reindex

[Ing. Marcos Ortiz Valmaseda]

Meena_Ramkumar escribió:

How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
be made without shutting the server? If so, then what will be performance
degradation percentage?
  

To execute vacuum, you can´t stop the server, is another process of it.
If you are using a recent version of PostgreSQL, you can use autovacuum 
on the server and this process is charged of this or to use VACUUM with 
the right schedule. You should avoid to use VACUUM FULL, because is very 
slow and it requires exclusive locks of the tables that you are 
executing this, and it reduces the table size on the disc but It doesn´t 
reduce the index size, but iit can make indexes larger.


With autovacuum = on, you can avoid to use VACUUM frecuently

The performance degradation depends of the quantity of tables and 
databases that you have on your server.


REINDEX is another task that you can execute periodicly on you server, 
but if you don´t want to affect the production task, the best thing yo 
do is to drop the index and reissue the CREATE INDEX CONCURRENTLY command.


Regards


--
 
-- Ing. Marcos Luís Ortíz Valmaseda   --

-- Twitter: http://twitter.com/@marcosluis2186--
-- FreeBSD Fan/User   --
-- http://www.freebsd.org/es  --
-- Linux User # 418229--
-- Database Architect/Administrator   --
-- PostgreSQL RDBMS   --
-- http://www.postgresql.org  --
-- http://planetpostgresql.org--
-- http://www.postgresql-es.org   --

-- Data WareHouse -- Business Intelligence Apprentice --
-- http://www.tdwi.org--
 
-- Ruby on Rails Fan/Developer--

-- http://rubyonrails.org --


Comunidad Técnica Cubana de PostgreSQL
http://postgresql.uci.cu
http://personas.grm.uci.cu/+marcos

Centro de Tecnologías de Gestión de Datos (DATEC) 
Contacto: 
   Correo: centa...@uci.cu
   Telf: +53 07-837-3737
 +53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu 





--
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] Possible to improve query plan?

[Ing. Marcos Ortiz Valmaseda]

Which is the type of your application? You can see it on the Performance 
Whackamole Presentation from Josh Berkus on the 
PgCon 2009:
- Web application
- Online Transaction Processing (OLTP)
- Data WareHousing (DW)

And based on the type of your application, you can configure the 
postgresql.conf to gain a better performance of your PostgreSQL server.
PostgreSQL postgresql.conf baseline:
 shared_buffers = 25% RAM
 work_mem = 512K[W] 2 MB[O] 128 MB[D]
 - but no more that RAM/no_connections
 maintenance_work_mem = 1/16 RAM
 checkpoint_segments = 8 [W], 16-64 [O], [D]
 wal_buffer = 1 MB [W], 8 MB [O], [D]
 effective_cache_size = 2/3 RAM

Regards
 

Ing. Marcos Luís Ortíz Valmaseda
Linux User # 418229 && PostgreSQL DBA
Centro de Tecnologías Gestión de Datos (DATEC)
http://postgresql.uci.cu
http://www.postgresql.org
http://it.toolbox.com/blogs/sql-apprentice

- Mensaje original -
De: "Jeremy Palmer" 
Para: "Andy Colson" 
CC: pgsql-performance@postgresql.org
Enviados: Lunes, 17 de Enero 2011 0:13:25 GMT -05:00 Región oriental EE. 
UU./Canadá
Asunto: Re: [PERFORM] Possible to improve query plan?

Hi Andy,

Yes important omissions:

Server version: 8.4.6
OS Windows Server 2003 Standard Ed :(
The work mem is 50mb.

I tried setting the work_mem to 500mb, but it didn't make a huge difference in 
query execution time. But then again the OS disk caching is probably taking 
over here.

Ok here's the new plan with work_mem = 50mb:

http://explain.depesz.com/s/xwv

And here another plan with work_mem = 500mb:

http://explain.depesz.com/s/VmO

Thanks,
Jeremy

-Original Message-
From: Andy Colson [mailto:a...@squeakycode.net] 
Sent: Monday, 17 January 2011 5:57 p.m.
To: Jeremy Palmer
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Possible to improve query plan?


Hum.. yeah it looks like it takes no time at all to pull data from the 
individual indexes, and them bitmap them.  I'm not sure what the bitmap heap 
scan is, or why its slow.  Hopefully someone smarter will come along.

Also its weird that explain.depesz.com didnt parse and show your entire plan.  
Hum.. you seem to have ending quotes on some of the lines?

One other though: quicksort  Memory: 23960kB
It needs 20Meg to sort... It could be your sort is swapping to disk.

What sort of PG version is this?
What are you using for work_mem?  (you could try to bump it up a little (its 
possible to set for session only, no need for server restart) and see if that'd 
help.

And sorry, but its my bedtime, good luck though.

-Andy

__

This message contains information, which is confidential and may be subject to 
legal privilege. 
If you are not the intended recipient, you must not peruse, use, disseminate, 
distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 
0800 665 463 or i...@linz.govt.nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any 
attachments, after its transmission from LINZ.

Thank you.
__

-- 
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] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

[Ing. Marcos Ortiz Valmaseda]

Query is :
SELECT distinct m.id,coalesce(m.givenname,''),
   coalesce(m.midname,''),
   m.surname from marinerstates ms,vessels vsl,mariner m 
WHERE  m.id=ms.marinerid and ms.vslid=vsl.id 
ANDms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15'
ANDms.starttime::date <= '2007-01-11'  AND
   m.marinertype='Mariner'  and m.id 
NOT IN (SELECT distinct mold.id
FROM marinerstates msold,
vessels vslold,
mariner mold  
WHERE mold.id=msold.marinerid 
AND  msold.vslid=vslold.id
AND msold.state='Active' 
AND coalesce(msold.endtime,now())::date >= '2006-07-15' 
AND msold.starttime::date <= '2007-01-11' 
AND EXISTS (SELECT 1 
FROM marinerstates msold2 
WHERE msold2.marinerid=msold.marinerid
AND msold2.state='Active' 
AND msold2.id <> msold.id 
AND msold2.starttime= '2006-07-15' and 
ms.starttime::date <= '2007-01-11'  and m.marinertype='Mariner'  and NOT EXISTS 
   (SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner 
mold  where mold.id=msold.marinerid and msold.vslid=vslold.id and 
   msold.state='Active' and coalesce(msold.endtime,now())::date >= '2006-07-15' 
and msold.starttime::date <= '2007-01-11' and 
   exists (select 1 from marinerstates msold2 where 
msold2.marinerid=msold.marinerid and msold2.state='Active' and msold2.id <> 
msold.id and 
  msold2.starttimehttp://postgresql.uci.cu
http://www.postgresql.org
http://it.toolbox.com/blogs/sql-apprentice

-- 
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] Queries becoming slow under heavy load

[Ing. Marcos Ortiz Valmaseda]

When you say that with a lot of concurrent access, queries get very slow, How 
many concurrent connections to your server have you had?
more that max_connections´value?
If you want to have many concurrent connections, you should have consider to 
use a pooling connection system like pgbouncer or pgpool.

Which are the values for:
- work_mem
- shared_buffers
- maintenance_work_mem
- effective_cache_size
- effective_io_concurrency
- server_version

Which are your platform?

Regards
--
Ing. Marcos Luís Ortíz Valmaseda
System Engineer -- Database Administrator

Centro de Tecnologías de Gestión de Datos (DATEC)
Universidad de las Ciencias Informáticas
http://postgresql.uci.cu


-- 
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] High load,

[Ing. Marcos Ortiz Valmaseda]

Another advice is to look the presentation of Alexander Dymo, on the 
RailsConf2009 called: Advanced Performance Optimization of Rails Applications 
available on 
http://en.oreilly.com/rails2009/public/schedule/detail/8615
This talk are focused on Rails and PostgreSQL, based on the development of the 
Acunote ´s Project Management Platform

http://blog.pluron.com


- Mensaje original -
De: "Andy Colson" 
Para: "Michael Kohl" 
CC: pgsql-performance@postgresql.org
Enviados: Jueves, 27 de Enero 2011 12:20:18 GMT -05:00 Región oriental EE. 
UU./Canadá
Asunto: Re: [PERFORM] High load,

On 1/27/2011 9:09 AM, Michael Kohl wrote:
> On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson  wrote:
>> Have you run each of your queries through explain analyze lately?
>
> A code review including checking of queries is on our agenda.
>
>> You are vacuuming/autovacuuming, correct?
>
> Sure :-)
>
> Thank you,
> Michael
>

Oh, also, when the box is really busy, have you watched vmstat to see if 
you start swapping?

-Andy

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

-- 
Ing. Marcos Luís Ortíz Valmaseda
System Engineer -- Database Administrator

Centro de Tecnologías de Gestión de Datos (DATEC)
Universidad de las Ciencias Informáticas
http://postgresql.uci.cu


-- 
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] [GENERAL] Strange performance degradation

[Ing. Marcos Ortiz Valmaseda]

Lorenzo Allegrucci escribió:

Matthew Wakeling wrote:

On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote:

Anyway, how can I get rid those "idle in transaction" processes?
Can I just kill -15 them or is there a less drastic way to do it?


Are you crazy? Sure, if you want to destroy all of the changes made 
to the database in that transaction and thoroughly confuse the client 
application, you can send a TERM signal to a backend, but the 
consequences to your data are on your own head.


I'm not crazy, it was just a question..
Anyway, problem solved in the Django application.


Matthew replied to you of that way because this is not a good manner to 
do this, not fot thr fact that you are crazy.


You can find better ways to do this.

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] RAID card recommendation

[Ing. Marcos Ortiz Valmaseda]

Gurgel, Flavio escribió:

- "Richard Neill"  escreveu:

  

Matthew Wakeling wrote:


We're about to purchase a new server to store some of our old
  
databases, 


and I was wondering if someone could advise me on a RAID card. We
  
want 


to make a 6-drive SATA RAID array out of 2TB drives, and it will be
  
RAID 

5 or 6 because there will be zero write traffic. The priority is 
stuffing as much storage into a small 2U rack as possible, with 
performance less important. We will be running Debian Linux.


People have mentioned Areca as making good RAID controllers. We're 
looking at the "Areca ARC-1220 PCI-Express x8 SATA II" as a
  
possibility. 


Does anyone have an opinion on whether it is a turkey or a star?

Another possibility is a 3-ware card of some description.

  
Do you actually need a RAID card at all? It's just another point of 
failure: the Linux software raid (mdadm) is pretty good.


Also, be very wary of RAID5 for an array that size. It is highly 
probable that, if one disk has failed, then during the recovery
process, 
you may lose a second disk. The unrecoverable error rate on standard 
disks is about 1 in 10^14 bits; your disk array is 10^11 bits in

size...

We got bitten by this

Richard



Linux kernel software RAID is fully supported in Debian Lenny, is quite cheap 
to implement and powerful.
I would avoid SATA disks but it's just me. SAS controllers and disks are 
expensive but worth every penny spent on them.

Prefer RAID 1+0 over RAID 5 not only because of the risk of failure of a second 
disk, but I have 3 cases of performance issues caused by RAID 5.
It's said that performance is not the problem but think twice because a good 
application tends to scale fast to several users.
Of course, keep a good continuous backup strategy of your databases and don't 
trust just the mirroring of disks in a RAID fashion.

Flavio Henrique A. Gurgel
Consultor -- 4Linux
tel. 55-11-2125.4765
fax. 55-11-2125.4777
www.4linux.com.br


  
Do you expose that performance issued caused by RAID 5? Because this is 
one of our solutions here on my country to save the data of our 
PostgreSQL database. Which model do you recommend ? RAID 0,RAID 1, RAID 
5 or RAID 10?


--
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] Queries within a function

[Ing. Marcos Orti­z Valmaseda]

Mridula Mahadevan wrote:


Hi,

 I am running a bunch of queries within a function, creating some temp 
tables and populating them. When the data exceeds say, 100k the 
queries start getting really slow and timeout (30 min). when these are 
run outside of a transaction(in auto commit mode), they run in a few 
seconds. Any ideas on what may be going on and any postgresql.conf 
parameters etc that might help?


Thanks


Do you put here the result of the explain command of the query?
Do you put here the postgresql.conf parameters that you have in your box?

Regards


--

"Para ser realmente grande, hay que estar con la gente, no por encima de ella."
  Montesquieu
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA && DWH -- BI Apprentice

Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
Universidad de las Ciencias Informáticas

Linux User # 418229

-- PostgreSQL --
"TIP 4: No hagas 'kill -9' a postmaster"
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org

-- DWH + BI --
The Data WareHousing Institute
http://www.tdwi.org
http://www.tdwi.org/cbip
-

<>
-- 
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] Multiple data base on same server

[Ing. Marcos Ortiz Valmaseda]

elias ghanem escribió:


Hi,

I’m using postgresql 8.4

I need to install multiple postgresql dbs on one server but I have 
some questions:


-Is there any problems (performance wise or other) if I have 10 to 15 
DBs on the same server?


-Each DB needs 10 tablespaces, so if I create 10 different tablespaces 
for each DB I will have 100 to 150 table space on the same server. So 
can this also cause any problems?


Thanks

It´s depends of the features of the server. If is a good server, for 
example of 16 GB to 32 of RAM, with 8 a 16 processors, with a good SAN 
with RAID -1 for the pg_xlog directory and RAID-10 for the $PG_DATA 
using ZFS if you are using Solaris or FreeBSD and xfs or ext3 using 
Linux , on a Operating Systems of 64 bits, I think that this load can be 
supported.


There are installations of PostgreSQL with more than 400 db, but the 
environment is very distribuided on several servers.
About the tablespaces, It´s very necesary to have 10 tablespaces on each 
database? Normally, you can separate the table or the tables with more 
activity to a rapid disc array (I ´m thinking on a SSD array), other 
tablespace for the indexes if you have many, and for example with 
pl/proxy you could handle the partitions of your data.
There is not necessaty to have 100 or 150 tablespaces on the same 
server. You can separate this on a SAN, you can have two or more main 
PostgreSQL servers and several slaves with the data replicated on any 
case of data corruption on the main servers.
Rebember look the configuration of the performance of the PostgreSQL 
servers: work_mem, shared_buffers, etc

Regards and I hope that comments helps to you.


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

[PERFORM] Any have tested ZFS like PostgreSQL installation filesystem?

[Ing . Marcos Luís Ortíz Valmaseda]

Regards to all the list.
ZFS, the new filesystem developed by the Solaris Development team and 
ported to FreeBSD too, have many advantages that can do that all 
sysadmins are questioned

about if it is a good filesystem to the PostgreSQL installation.
Any of you haved tested this filesystem like PostgreSQL installation fs?
Regards.


--
-
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA 
Centro de Tecnologías de Almacenamiento y Anális de Datos (CENTALAD)

Universidad de las Ciencias Informáticas

Linux User # 418229
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org




--
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] Any have tested ZFS like PostgreSQL installation filesystem?

[Ing . Marcos Luís Ortíz Valmaseda]

Ivan Voras escribió:

Ing . Marcos Luís Ortíz Valmaseda wrote:

Regards to all the list.
ZFS, the new filesystem developed by the Solaris Development team and 
ported to FreeBSD too, have many advantages that can do that all 
sysadmins are questioned

about if it is a good filesystem to the PostgreSQL installation.
Any of you haved tested this filesystem like PostgreSQL installation fs?


It will work but as to if it is a good file system for databases, the 
debate still goes on.


Here are some links about ZFS and databases:

http://blogs.sun.com/paulvandenbogaard/entry/postgresql_on_ufs_versus_zfs
http://blogs.sun.com/paulvandenbogaard/entry/running_postgresql_on_zfs_file 


http://blogs.sun.com/realneel/entry/mysql_innodb_zfs_best_practices
http://dev.mysql.com/tech-resources/articles/mysql-zfs.html
http://blogs.smugmug.com/don/2008/10/13/zfs-mysqlinnodb-compression-update/ 



A separate issue (I think it is not explored enough in the above 
links) is that ZFS writes data in a semi-continuous log, meaning there 
are no in-place modifications of files (every such write is made on a 
different place), which leads to heavy fragmentation. I don't think I 
have seen a study of this particular effect. OTOH, it will only matter 
if the DB usage pattern is sequential reads and lots of updates - and 
even here it might be hidden by internal DB data fragmentation.




OK, thanks for the answers, I ´ll study the efects now. This tests was 
with the FreeBSD-8.0 version?


Regards.


--
-
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA 
Centro de Tecnologías de Almacenamiento y Anális de Datos (CENTALAD)

Universidad de las Ciencias Informáticas

Linux User # 418229
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org




--
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] performance while importing a very large data set in to database

[Ing . Marcos Luís Ortíz Valmaseda]

Ashish Kumar Singh escribió:


Hello Everyone,

 

I have a very bit big database around 15 million in size, and the dump 
file is around 12 GB.


While importing this dump in to database I have noticed that initially 
query response time is very slow but it does improves with time.


Any suggestions to improve performance after dump in imported in to 
database will be highly appreciated!


 

 

 


Regards,

Ashish


My suggestion is:
1- Afterward of the db restore, you can do a vacuum analyze manually on 
your big tables to erase all dead rows

2- Then you can reindex your big tables on any case that you use it.
3- Then apply A CLUSTER command on the right tables that have these indexes.

Regards


--
-
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA 
Centro de Tecnologías de Almacenamiento y Anális de Datos (CENTALAD)

Universidad de las Ciencias Informáticas

Linux User # 418229
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org




--
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] Re: New server to improve performance on our large and busy DB - advice? (v2)

[Ing. Marcos L. Ortiz Valmaseda]

El 15/01/2010 14:43, Ivan Voras escribió:

hi,

You wrote a lot of information here so let's confirm in a nutshell 
what you have and what you are looking for:


* A database that is of small to medium size (5 - 10 GB)?
* Around 10 clients that perform constant write operations to the 
database (UPDATE/INSERT)

* Around 10 clients that occasionally read from the database
* Around 6000 tables in your database
* A problem with tuning it all
* Migration to new hardware and/or OS

Is this all correct?

First thing that is noticeable is that you seem to have way too few 
drives in the server - not because of disk space required but because 
of speed. You didn't say what type of drives you have and you didn't 
say what you would consider desirable performance levels, but off hand 
(because of the "10 clients perform constant writes" part) you will 
probably want at least 2x-4x more drives.


> 1) Which RAID level would you recommend

With only 4 drives, RAID 10 is the only thing usable here.

> 2) Which Windows OS would you recommend? (currently 2008 x64 Server)

Would not recommend Windows OS.

> 3) If we were to port to a *NIX flavour, which would you recommend? 
(which

> support trouble-free PG builds/makes please!)

Practically any. I'm biased for FreeBSD, a nice and supported version 
of Linux will probably be fine.


> 4) Is this the right PG version for our needs?

If you are starting from scratch on a new server, go for the newest 
version you can get - 8.4.2 in this case.


Most importantly, you didn't say what you would consider desirable 
performance. The hardware and the setup you described will work, but 
not necessarily fast enough.


> . So far, we have never seen a situation where a seq scan has improved
> performance, which I would attribute to the size of the tables

... and to the small number of drives you are using.

> . We believe our requirements are exceptional, and we would benefit
> immensely from setting up the PG planner to always favour 
index-oriented decisions


Have you tried decreasing random_page_cost in postgresql.conf? Or 
setting (as a last resort) enable_seqscan = off?



Carlo Stonebanks wrote:
My client just informed me that new hardware is available for our DB 
server.


. Intel Core 2 Quads Quad
. 48 GB RAM
. 4 Disk RAID drive (RAID level TBD)

I have put the ugly details of what we do with our DB below, as well 
as the

postgres.conf settings. But, to summarize: we have a PostgreSQL 8.3.6 DB
with very large tables and the server is always busy serving a constant
stream of single-row UPDATEs and INSERTs from parallel automated 
processes.


There are less than 10 users, as the server is devoted to the KB 
production

system.

My questions:

1) Which RAID level would you recommend
2) Which Windows OS would you recommend? (currently 2008 x64 Server)
3) If we were to port to a *NIX flavour, which would you recommend? 
(which

support trouble-free PG builds/makes please!)
4) Is this the right PG version for our needs?

Thanks,

Carlo

The details of our use:

. The DB hosts is a data warehouse and a knowledgebase (KB) tracking the
professional information of 1.3M individuals.
. The KB tables related to these 130M individuals are naturally also 
large
. The DB is in a perpetual state of serving TCL-scripted Extract, 
Transform

and Load (ETL) processes
. These ETL processes typically run 10 at-a-time (i.e. in parallel)
. We would like to run more, but the server appears to be the bottleneck
. The ETL write processes are 99% single row UPDATEs or INSERTs.
. There are few, if any DELETEs
. The ETL source data are "import tables"
. The import tables are permanently kept in the data warehouse so 
that we

can trace the original source of any information.
. There are 6000+ and counting
. The import tables number from dozens to hundreds of thousands of rows.
They rarely require more than a pkey index.
. Linking the KB to the source import date requires an "audit table" 
of 500M

rows, and counting.
. The size of the audit table makes it very difficult to manage, 
especially

if we need to modify the design.
. Because we query the audit table different ways to audit the ETL 
processes

decisions, almost every column in the audit table is indexed.
. The maximum number of physical users is 10 and these users RARELY 
perform

any kind of write
. By contrast, the 10+ ETL processes are writing constantly
. We find that internal stats drift, for whatever reason, causing row 
seq

scans instead of index scans.
. So far, we have never seen a situation where a seq scan has improved
performance, which I would attribute to the size of the tables
. We believe our requirements are exceptional, and we would benefit
immensely from setting up the PG planner to always favour index-oriented
decisions - which seems to contradict everything that PG advice 
suggests as

best practice.

Current non-default conf settings are:

autovacuum = on
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_thre

Re: [PERFORM] renice on an I/O bound box

[Ing. Marcos L. Ortiz Valmaseda]

El 19/01/2010 13:59, Willy-Bas Loos escribió:

Hi,

I have a query that runs for about 16 hours, it should run at least 
weekly.
There are also clients connecting via a website, we don't want to keep 
them waiting because of long DSS queries.


We use Debian Lenny.
I've noticed that renicing the process really lowers the load (in 
"top"), though i think we are I/O bound. Does that make any sense?


Cheers,

WBL
--
"Patriotism is the conviction that your country is superior to all 
others because you were born in it." -- George Bernard Shaw

¿16 hours?
¿Which the amount of data? of 10  to 30 000 000 of records no?

¿Do you put the code here to see if we can help you on its optimization?

About the question, you can give more information with iostat.
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] Limited Shared Buffer Problem

[Ing . Marcos Luís Ortíz Valmaseda]

Richard Neill escribió:



**Rod MacNeil wrote:

Hi All,

I have a server running CentOS5 with 6gb of memory that will run 
postgres 8.3 exclusively.
I would like to allocate 4gb of the memory to shared buffers for 
postgres.


It might be worth pausing at this point:

The various postgresql tuning guides usually suggest that on a 
dedicated system, you should give postgres about 1/4 of the RAM for 
shared buffers, while telling it that the effective_cache_size = 1/2 RAM.


Postgres will make good use of the OS cache as a file-cache - the 
"effective_cache_size" setting is advisory to postgres that it can 
expect about this much data to be in RAM.


Also, If you are setting up a new system, it's probably worth going 
for 8.4.2. Postgres is relatively easy to build from source.


HTH,

Richard

All these values has to be combined with the others: shared_buffers, 
work_mem,etc.
My recommendation is to go down a little the shmmax and the 
shared_buffers values.

Is very necessary that you have these values so high?

Regards


--
-
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA && DWH -- BI Apprentice

Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
Universidad de las Ciencias Informáticas

Linux User # 418229

-- PostgreSQL --

http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org

-- DWH + BI --

http://www.tdwi.org

---


--
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] [GENERAL] PostgreSQL - case studies

[Ing. Marcos L. Ortiz Valmaseda]

uncer for pooling 
connections,Heartbeat for monitoring and fault detections and CentOS nd 
FreeBSD like OS base.

The pg_xlog directory are in a RAID-1 and the main data in a RAID-10.

Do you have any recommendation?

Note: Any has a MPP querys implementation for PostgreSQL that can be shared?

Regards


--

"Para ser realmente grande, hay que estar con la gente, no por encima de ella."
   Montesquieu
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA&&  DWH -- BI Apprentice

Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
Universidad de las Ciencias Informáticas

Linux User # 418229

-- PostgreSQL --
"TIP 4: No hagas 'kill -9' a postmaster"
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org

-- DWH + BI --
The Data WareHousing Institute
http://www.tdwi.org
http://www.tdwi.org/cbip
-


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