Re: [GENERAL] SSL ques

2010-12-12 Thread Dmitriy Igrishin 
Hey,

Sounds like you does not have SSL library installed.
Try to install, for example, OpenSSL first and then try
to build you software.

2010/12/12 zab08 

> when I install sysbench-0.4.8,  after the make command:
>
>
> /usr/bin/ld: warning: libssl.so.6, needed by
> /opt/PostgreSQL/9.0/lib/libpq.so, not found (try using -rpath or
> -rpath-link)
> /usr/bin/ld: warning: libcrypto.so.6, needed by
> /opt/PostgreSQL/9.0/lib/libpq.so, not found (try using -rpath or
> -rpath-link)
> /opt/PostgreSQL/9.0/lib/libpq.so: undefined reference to `TLSv1_method'
> /opt/PostgreSQL/9.0/lib/libpq.so: undefined reference to `SSL_set_ex_data'
> /opt/PostgreSQL/9.0/lib/libpq.so: undefined reference to `SSL_connect'
> ..
>
> I install PostgreSQL by enterDB's not from source code?
>
> how to solve this problem?
>
>
> --
> 网易163/126邮箱百分百兼容iphone 
> ipad邮件收发
>



-- 
// Dmitriy.

Re: [GENERAL] SSL ques

2010-12-12 Thread zab08 
thank you, I install postgres from source, and use --with-openssl.

[GENERAL] range intervals in window function frames

2010-12-12 Thread Daniel Popowich 

Hello all!

I need to do moving averages over time series data and was hoping
window functions could solve the problem for me, but it doesn't look
like 8.4 or even 9.0 implementations are quite there, yet.

Currently, if I have this table:

  create table sample (
  tstimestamp,
  value integer
  );
  create index sample_ts on sample (ts);

and say I want a moving average of value over a fixed interval of five
minutes (note that this could mean varying numbers of records in each
"frame"), then I can do this:

  select *, avg_over_interval(ts, interval '5 min') from sample order by ts;
  
Where avg_over_interval() is defined like this:

  create or replace function avg_over_interval(timestamp, interval)
  returns numeric as $$
 select avg(value) from sample where (($1-$2) <= ts) and (ts <= $1);
  $$ language sql;

What I would LIKE to do is this:

  select *, avg(ts) over(order by ts range (interval '5 min') preceding)
 from sample order by ts;

Which is way cleaner and, I assume, more efficient.

Questions:

  1) Is there active work on window functions with frames over
 interval ranges?

  2) If not, how can I help with that?

  3) Until the functionality is in 9.x, can I make what I'm doing more
 efficient?  Is there a better way to do this without window
 functions?  (I tried to come up with a subquery in my
 select-list, but couldn't come up with legal syntax to reference
 the static value of ts for the current row in the subselect.)

Thanks all for you help.

Dan

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

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-12-12 Thread Tom Lane 
Tomas Vondra  writes:
> Dne 17.11.2010 06:58, Tom Lane napsal(a):
>>> BTW I think the default estimate used to be 1000, so it was changed in
>>> one of the 8.x releases? Can you point me to the docs? I've even tried
>>> to find that in the sources, but unsuccessfully.
>> 
>> It's DEFAULT_RANGE_INEQ_SEL, and AFAIR it hasn't changed in quite a while.
>> But I wouldn't be surprised if the behavior of this example changed when
>> we boosted the default statistics target.

> I've been thinking about this and I think it might be improved. If I
> understand the logic corretly, it says 'use half of the histogram bin
> size'. But the value

> #define DEFAULT_RANGE_INEQ_SEL 0.005

> says it's always 0.5%, which is not not true if STATISTICS TARGET is not
> 100. This could actually yield 10x more precise estimates when the
> STATISTICS TARGET is set to 1000.

Huh?  The default estimates are completely unrelated to the size of the
histogram, and certainly unrelated to the default size of the
histogram.  We use those estimates when we don't have relevant stats.
It's pure wishful thinking to suppose that changing the statistics
target would have any impact on what the estimate ought to be in such
a case.

I believe the actual reasoning for setting the default estimates that
are under 1% is that we wanted to encourage indexscan choices in such
cases.  Once it's small enough for that, making it even smaller doesn't
really help --- and that does risk making bad join choices.  You don't
want the thing coming up with one-row estimates unless there's real
evidence for such an estimate.

regards, tom lane

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

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-12-12 Thread tv 
> Tomas Vondra  writes:
>> I've been thinking about this and I think it might be improved. If I
>> understand the logic corretly, it says 'use half of the histogram bin
>> size'. But the value
>
>> #define DEFAULT_RANGE_INEQ_SEL 0.005
>
>> says it's always 0.5%, which is not not true if STATISTICS TARGET is not
>> 100. This could actually yield 10x more precise estimates when the
>> STATISTICS TARGET is set to 1000.
>
> Huh?  The default estimates are completely unrelated to the size of the
> histogram, and certainly unrelated to the default size of the
> histogram.  We use those estimates when we don't have relevant stats.
> It's pure wishful thinking to suppose that changing the statistics
> target would have any impact on what the estimate ought to be in such
> a case.

Ooops, sorry for the crazy gibberish I've posted earlier. I thought those
default estimates work a somehow different and haven't checked that in the
code. The proposed 'optimization' obviously does not make any sense.

regards
Tomas


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

Re: [GENERAL] Performance tuning in Pgsql

2010-12-12 Thread Adarsh Sharma 

Scott Marlowe wrote:

Please keep the list cc'd as there are others who might be able to
help or could use this thread for help.

On Fri, Dec 10, 2010 at 2:53 AM, Adarsh Sharma  wrote:
  

Scott Marlowe wrote:


On Fri, Dec 10, 2010 at 12:53 AM, Adarsh Sharma
 wrote:

  

Dear all,

I am researched a lot about Performance tuning in Pgsql.

I found that we have to change shared_buffer parameter and
effective_cache_size parameter.
I changed shared_buffer to 2 GB but I can't able to locate
effective_cache_size parameter in postgresql.conf file.



Odd, it's there in mine.

So, what OS are you using, what pg version, etc.

First place to look for performance tuning is the pg wiki entry on just
that:

http://wiki.postgresql.org/wiki/Performance_Optimization


  

Also i want to change my WAL directory to seperate directory. Same I
couldn,t locate pg_xlog or how to change it.



OK, so the way I do this, is to locate my data directory.  On a stock
Ubuntu machine that would be /var/lib/postgresql/8.4/main .  In that
dir is a directory called pg_xlog, what we're looking for.  So, as
root, I'd do:

cd /var/lib/postgresql/8.4/main
/etc/init.d/postgresql-8.4 stop
mkdir /myothervolume/pg_xlog
chown postgres.postgres /myothervolume/pg_xlog
chmod 700 /myothervolume/pg_xlog
cp -rf pg_xlog/* /myothervolume/pg_xlog
mv pg_xlog pg_xlog_old
ln -s /myothervolume/pg_xlog pg_xlog
/etc/init.d/postgresql-8.4 start

  

Thanks Scott , Very Nicely Explained.

I followed your commands. I copied pg_xlog directory into /hdd-1/pg_xlog and
chmod 700 to it. Also i make a link into /root/ directory.

But when I start the server , I got the exception in startup.log which is
FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
245, near token "/"
FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
245, near token "/"
FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
247, near token "/"
FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
247, near token "/"
FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
247, near token "/"

My postgresql.conf Line 247 is :

#log_directory =/hrd2-1/pg_xlog # directory where log files are
written,
  # can be absolute or relative to
PGDATA

I check it with # and without # but it doesn't work.


But when I renamed pg_xlog_old to pg_xlog , Server starts.



That doesn't make a lot of sense.  The way I move pg_xlog doesn't
involve that line really but kind of bypasses it.  Got a complete
example of all the steps you took?

  

Does i need to change something in Postgresql.conf file?




Possibly.  It's one of the two ways of moving pg_xlog.  More complete
step by step example of what you tried will help.
  
Sorry Sir, but I simply followed your steps. I think those are 
sufficient. But my server didn't start after these changes. Here are my 
steps :


cd /hrd2-p/postgres_data
/etc/init.d/postgresql-8.4 stop
mkdir -p /opt/pg_xlog
chown -R  postgres.postgres /opt/pg_xlog
chmod 700 /opt/pg_xlog
cp -rf pg_xlog/* /opt/pg_xlog
mv pg_xlog pg_xlog_old
ln -s /opt/pg_xlog pg_xlog
/etc/init.d/postgresql-8.4 start

Starting PostgreSQL 8.4:
waiting for server to 
start...could 
not start server
PostgreSQL 8.4 did not start in a timely fashion, please see 
/hrd2-p/postgres_data/pg_log/startup.log for details


uima-server:/hrd2-p/postgres_data # vim 
/hrd2-p/postgres_data/pg_log/startup.log


At this time this log is empty. Also I didn/t make any changes in 
postgresql.conf


I'm using Linux ( Linux uima-server 2.6.16.46-0.12-smp #1 SMP Thu May 17 
14:00:09 UTC 2007 x86_64 x86_64 x86_64 GNU/Linux )

and postgres ( Postgres 8.4 )


Thanks & Regards

Adarsh Sharma



















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

[GENERAL] Urgent -- High memory usage on PostgreSQL server

2010-12-12 Thread savio rodriges 
Hello,

We are facing very HIGH memory utilization on postgreSQL server and need help.

Below are details of PostgreSQL server,

===
MemTotal:  8165696 kB
CpuTotal:  8
===
/etc/sysctl.conf

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
===
top - 21:43:35 up 55 days,  8:07,  4 users,  load average: 0.05, 0.25, 0.17
Tasks: 257 total,   1 running, 256 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8165696k total,  7943160k used,   222536k free,   282044k buffers
Swap:  8385920k total,   112828k used,  8273092k free,  4793732k cached
===
-bash-3.2$ free -m
 total   used   free sharedbuffers cached
Mem:  7974   7756217  0275   4681
-/+ buffers/cache:   2799   5174
Swap: 8189110   8079

===
-bash-3.2$ cat /proc/meminfo
MemTotal:  8165696 kB
MemFree:222576 kB
Buffers:282136 kB
Cached:4793748 kB
SwapCached:  21144 kB
Active:3508524 kB
Inactive:  4004532 kB
HighTotal:   0 kB
HighFree:0 kB
LowTotal:  8165696 kB
LowFree:222576 kB
SwapTotal: 8385920 kB
SwapFree:  8273092 kB
Dirty: 548 kB
Writeback: 368 kB
AnonPages: 2415992 kB
Mapped: 730104 kB
Slab:   332440 kB
PageTables:  58632 kB
NFS_Unstable:0 kB
Bounce:  0 kB
CommitLimit:  12468768 kB
Committed_AS:  4774104 kB
VmallocTotal: 34359738367 kB
VmallocUsed:267392 kB
VmallocChunk: 34359470967 kB
HugePages_Total: 0
HugePages_Free:  0
HugePages_Rsvd:  0
Hugepagesize: 2048 kB
===
processor   : Total 8
vendor_id   : GenuineIntel
cpu family  : 6
model   : 26
model name  : Intel(R) Xeon(R) CPU   E5504  @ 2.00GHz
stepping: 5
cpu MHz : 2000.118
cache size  : 4096 KB
physical id : 1
siblings: 4
core id : 3
cpu cores   : 4
apicid  : 22
fpu : yes
fpu_exception   : yes
cpuid level : 11
wp  : yes
flags   : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov 
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm 
constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 
sse4_2 popcnt lahf_lm
bogomips: 4000.10
clflush size: 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: [8]
===
-bash-3.2$ ipcs

-- Shared Memory Segments 
keyshmid  owner  perms  bytes  nattch status
0x0052e2c1 35454978   postgres  6001142923264 28

-- Semaphore Arrays 
keysemid  owner  perms  nsems
0x0052e2c1 37650432   postgres  60017
0x0052e2c2 37683201   postgres  60017
0x0052e2c3 37715970   postgres  60017
0x0052e2c4 37748739   postgres  60017
0x0052e2c5 37781508   postgres  60017
0x0052e2c6 37814277   postgres  60017
0x0052e2c7 37847046   postgres  60017

-- Message Queues 
keymsqid  owner  perms  used-bytes   messages
===
-bash-3.2$ ipcs -l

-- Shared Memory Limits 
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

-- Semaphore Limits 
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

-- Messages: Limits 
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
===
/var/lib/pgsql/data/postgresql.conf
---
(Non-default values are as follows)

maintenance_work_mem = 480MB
checkpoint_completion_target = 0.7
work_mem = 36MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 1024MB
log_duration = on
log_min_duration_statement = 1
effective_cache_size=2048MB
===

Any idea where is the issue and what needs to be changed ?

Thank

Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server

2010-12-12 Thread Allan Kamau 
On Mon, Dec 13, 2010 at 8:49 AM, savio rodriges  wrote:
> Hello,
>
> We are facing very HIGH memory utilization on postgreSQL server and need help.
>
> Below are details of PostgreSQL server,
>
> ===
> MemTotal:      8165696 kB
> CpuTotal:      8
> ===
> /etc/sysctl.conf
> 
> # Controls the maximum shared segment size, in bytes
> kernel.shmmax = 68719476736
>
> # Controls the maximum number of shared memory segments, in pages
> kernel.shmall = 4294967296
> ===
> top - 21:43:35 up 55 days,  8:07,  4 users,  load average: 0.05, 0.25, 0.17
> Tasks: 257 total,   1 running, 256 sleeping,   0 stopped,   0 zombie
> Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Mem:   8165696k total,  7943160k used,   222536k free,   282044k buffers
> Swap:  8385920k total,   112828k used,  8273092k free,  4793732k cached
> ===
> -bash-3.2$ free -m
>             total       used       free     shared    buffers     cached
> Mem:          7974       7756        217          0        275       4681
> -/+ buffers/cache:       2799       5174
> Swap:         8189        110       8079
>
> ===
> -bash-3.2$ cat /proc/meminfo
> MemTotal:      8165696 kB
> MemFree:        222576 kB
> Buffers:        282136 kB
> Cached:        4793748 kB
> SwapCached:      21144 kB
> Active:        3508524 kB
> Inactive:      4004532 kB
> HighTotal:           0 kB
> HighFree:            0 kB
> LowTotal:      8165696 kB
> LowFree:        222576 kB
> SwapTotal:     8385920 kB
> SwapFree:      8273092 kB
> Dirty:             548 kB
> Writeback:         368 kB
> AnonPages:     2415992 kB
> Mapped:         730104 kB
> Slab:           332440 kB
> PageTables:      58632 kB
> NFS_Unstable:        0 kB
> Bounce:              0 kB
> CommitLimit:  12468768 kB
> Committed_AS:  4774104 kB
> VmallocTotal: 34359738367 kB
> VmallocUsed:    267392 kB
> VmallocChunk: 34359470967 kB
> HugePages_Total:     0
> HugePages_Free:      0
> HugePages_Rsvd:      0
> Hugepagesize:     2048 kB
> ===
> processor       : Total 8
> vendor_id       : GenuineIntel
> cpu family      : 6
> model           : 26
> model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz
> stepping        : 5
> cpu MHz         : 2000.118
> cache size      : 4096 KB
> physical id     : 1
> siblings        : 4
> core id         : 3
> cpu cores       : 4
> apicid          : 22
> fpu             : yes
> fpu_exception   : yes
> cpuid level     : 11
> wp              : yes
> flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca 
> cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp 
> lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr 
> sse4_1 sse4_2 popcnt lahf_lm
> bogomips        : 4000.10
> clflush size    : 64
> cache_alignment : 64
> address sizes   : 40 bits physical, 48 bits virtual
> power management: [8]
> ===
> -bash-3.2$ ipcs
>
> -- Shared Memory Segments 
> key        shmid      owner      perms      bytes      nattch     status
> 0x0052e2c1 35454978   postgres  600        1142923264 28
>
> -- Semaphore Arrays 
> key        semid      owner      perms      nsems
> 0x0052e2c1 37650432   postgres  600        17
> 0x0052e2c2 37683201   postgres  600        17
> 0x0052e2c3 37715970   postgres  600        17
> 0x0052e2c4 37748739   postgres  600        17
> 0x0052e2c5 37781508   postgres  600        17
> 0x0052e2c6 37814277   postgres  600        17
> 0x0052e2c7 37847046   postgres  600        17
>
> -- Message Queues 
> key        msqid      owner      perms      used-bytes   messages
> ===
> -bash-3.2$ ipcs -l
>
> -- Shared Memory Limits 
> max number of segments = 4096
> max seg size (kbytes) = 67108864
> max total shared memory (kbytes) = 17179869184
> min seg size (bytes) = 1
>
> -- Semaphore Limits 
> max number of arrays = 128
> max semaphores per array = 250
> max semaphores system wide = 32000
> max ops per semop call = 32
> semaphore max value = 32767
>
> -- Messages: Limits 
> max queues system wide = 16
> max size of message (bytes) = 65536
> default max size of queue (bytes) = 65536
> ===
> /var/lib/pgsql/data/postgresql.conf
> ---
> (Non-default values are as follows)
>
> maintenance_work_mem = 480MB
> checkpoint_completion_targe

Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server

2010-12-12 Thread Paul McGarry 
On Mon, Dec 13, 2010 at 4:49 PM, savio rodriges  wrote:

> Hello,
>
> We are facing very HIGH memory utilization on postgreSQL server and need
> help.
>
> Mem:   8165696k total,  7943160k used,   222536k free,   282044k buffers
> Swap:  8385920k total,   112828k used,  8273092k free,  4793732k cached
>

What makes you say memory usage is high?

You have ~8gb of memory.
Less than 3gb of that is being used by applications and the OS is using the
"spare" 5gb for caching and buffers.

Paul

Re: [GENERAL] Urgent -- High memory usage on PostgreSQL server

2010-12-12 Thread Jan Kesten 
Hello Savio,

> top - 21:43:35 up 55 days,  8:07,  4 users,  load average: 0.05,
> 0.25, 0.17 Tasks: 257 total,   1 running, 256 sleeping,   0 stopped,
> 0 zombie Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,
> 0.0%hi,  0.0%si,  0.0%st Mem:   8165696k total,  7943160k used,
> 222536k free,   282044k buffers Swap:  8385920k total,   112828k
> used,  8273092k free,  4793732k cached

this looks kind of normal behaviour. Your system uses almost your entire
ram right, but if you look at buffers and cached  in your top output
about 5 GB of your 8GB are used there. Only the remaining 3GB are used
by postgres or other applications.

Every "new" linux I know will use non-used ram for caching your
filesystem data for example to achieve 100% memory usage. This is "a
good thing" (TM) as postgres will run better if there is caching on os
level and the instance knows about this:

> effective_cache_size =  — This value tells PostgreSQL's
> optimizer how much memory PostgreSQL has available for caching data
> and helps in determing whether or not it use an index or not. The
> larger the value increases the likely hood of using an index. This
> should be set to the amount of memory allocated to shared_buffers
> plus the amount of OS cache available. Often this is more than 50% of
> the total system memory.

So don't worry - everything is fine. You should pay attention that your
system doesn't start swapping.

Cheers,
Jan

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