from:"mendola"


On 13/02/2012 19:48, Greg Stark wrote:

I don't think we should be looking at either CUDA or OpenCL directly.
We should be looking for a generic library that can target either and
is well maintained and actively developed. Any GPU code we write
ourselves would rapidly be overtaken by changes in the hardware and
innovations in parallel algorithms. If we find a library that provides
a sorting api and adapt our code to use it then we'll get the benefits
of any new hardware feature as the library adds support for them.



I think one option is to make the sort function pluggable with a shared
library/dll. I see several benefits from this:

 - It could be in the interest of the hardware vendor to provide the 
most powerful sort implementation (I'm sure for example that TBB sort 
implementation is faster that pg_sort)


 - It can permit people to play with it without being deep involved 
in pg development and stuffs.


 - It can relieve the postgres core group the choose about the right 
language/tool/implementation to use.


 - Also for people not willing (or not able for the matter) to upgrade
postgres engine to change instead the sort function upon an hardware
upgrade.


Of course if this happens postgres engine has to make some sort of
sanity check (that the function for example actually sorts) before to 
thrust the plugged sort.

The engine can even have multiple sort implementation available and
use the most proficient one (imagine some sorts acts better on
a certain range value or on certain element size).


Regards
Gaetano Mendola


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

Re: [HACKERS] CUDA Sorting


On 13/02/2012 19:48, Greg Stark wrote:

I don't think we should be looking at either CUDA or OpenCL directly.
We should be looking for a generic library that can target either and
is well maintained and actively developed. Any GPU code we write
ourselves would rapidly be overtaken by changes in the hardware and
innovations in parallel algorithms. If we find a library that provides
a sorting api and adapt our code to use it then we'll get the benefits
of any new hardware feature as the library adds support for them.



I think one option is to make the sort function plugable with a shared
library/dll. I see several benefits from this:

 - It could be in the interest of the hardware vendor to provide the 
most powerful sort implementation (I'm sure for example that TBB sort 
implementation is faster that pg_sort)


 - It can permit people to play with it without being deep involved 
in pg development and stuffs.


 - It can relieve the postgres core group the choose about the right 
language/tool/implementation to use.


 - Also for people not willing (or not able for the matter) to upgrade
postgres engine to change instead the sort function upon an hardware
upgrade.


Of course if this happens postgres engine has to make some sort of
sanity check (that the function for example actually sorts) before to 
thrust the plugged sort.

The engine can even have multiple sort implementation available and
use the most proficient one (imagine some sorts acts better on
a certain range value or on certain element size).


Regards
Gaetano Mendola


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

Re: [HACKERS] CUDA Sorting


On 15/02/2012 23:11, Peter Geoghegan wrote:

On 15 February 2012 20:00, Gaetano Mendolamend...@gmail.com  wrote:

On 13/02/2012 19:48, Greg Stark wrote:


I don't think we should be looking at either CUDA or OpenCL directly.
We should be looking for a generic library that can target either and
is well maintained and actively developed. Any GPU code we write
ourselves would rapidly be overtaken by changes in the hardware and
innovations in parallel algorithms. If we find a library that provides
a sorting api and adapt our code to use it then we'll get the benefits
of any new hardware feature as the library adds support for them.



I think one option is to make the sort function pluggable with a shared
library/dll. I see several benefits from this:

  - It could be in the interest of the hardware vendor to provide the most
powerful sort implementation (I'm sure for example that TBB sort
implementation is faster that pg_sort)

  - It can permit people to play with it without being deep involved in pg
development and stuffs.


Sorry, but I find it really hard to believe that the non-availability
of pluggable sorting is what's holding people back here. Some vanguard
needs to go and prove the idea by building a rough prototype before we
can even really comment on what an API should look like. For example,
I am given to understand that GPUs generally sort using radix sort -
resolving the impedance mismatch that prevents someone from using a
non-comparison based sort sure sounds like a lot of work for an
entirely speculative reward.


AFAIK thrust library uses the radix sort if the keys you are sorting are 
POD data comparable with a  operator otherwise it does the

comparison based sort using the operator provided.

http://docs.thrust.googlecode.com/hg/modules.html

I'm not saying that the non-availability of pluggable sort completely
holds people back, I'm saying that it will simplify the process now
and int the future, of course that's my opinion.


Someone who cannot understand tuplesort, which is not all that
complicated, has no business trying to build GPU sorting into
Postgres.


That sounds a bit harsh. I'm one of those indeed, I haven't look in the 
details not having enough time for it. At work we do GPU computing (not

the sort type stuff) and given the fact I'm a Postgres enthusiast I
asked my self: my server is able to sort around 500 milions integer per
seconds, if postgres was able to do that as well it would be very nice.

What I have to say? Sorry for my thoughts.


I had a patch committed a few hours ago that almost included the
capability of assigning an alternative sorting function, but only one
with the exact same signature as my variant of qsort_arg. pg_qsort
isn't used to sort tuples at all, by the way.


Then I did look in the wrong direction. Thank you for point that out.


Threading building blocks is not going to form the basis of any novel
sorting implementation, because comparators in general are not thread
safe, and it isn't available on all the platforms we support, and
because of how longjmp interacts with C++ stack unwinding and so on
and so on. Now, you could introduce some kind of parallelism into
sorting integers and floats, but that's an awful lot of work for a
marginal reward.


The TBB was just example that did come in my mind.
What do you mean with you could introduce some kind of parallelism?
As far as I know any algorithm using the divide and conquer can be
parallelized.

Regards
Gaetano Mendola



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

Re: [HACKERS] CUDA Sorting


On 15/02/2012 23:11, Peter Geoghegan wrote:

On 15 February 2012 20:00, Gaetano Mendolamend...@gmail.com  wrote:

On 13/02/2012 19:48, Greg Stark wrote:


I don't think we should be looking at either CUDA or OpenCL directly.
We should be looking for a generic library that can target either and
is well maintained and actively developed. Any GPU code we write
ourselves would rapidly be overtaken by changes in the hardware and
innovations in parallel algorithms. If we find a library that provides
a sorting api and adapt our code to use it then we'll get the benefits
of any new hardware feature as the library adds support for them.



I think one option is to make the sort function pluggable with a shared
library/dll. I see several benefits from this:

  - It could be in the interest of the hardware vendor to provide the most
powerful sort implementation (I'm sure for example that TBB sort
implementation is faster that pg_sort)

  - It can permit people to play with it without being deep involved in pg
development and stuffs.


Sorry, but I find it really hard to believe that the non-availability
of pluggable sorting is what's holding people back here. Some vanguard
needs to go and prove the idea by building a rough prototype before we
can even really comment on what an API should look like. For example,
I am given to understand that GPUs generally sort using radix sort -
resolving the impedance mismatch that prevents someone from using a
non-comparison based sort sure sounds like a lot of work for an
entirely speculative reward.


AFAIK thrust library uses the radix sort if the keys you are sorting are 
POD data comparable with a  operator otherwise it does the

comparison based sort using the operator provided.

http://docs.thrust.googlecode.com/hg/modules.html

I'm not saying that the non-availability of pluggable sort completely
holds people back, I'm saying that it will simplify the process now
and int the future, of course that's my opinion.


Someone who cannot understand tuplesort, which is not all that
complicated, has no business trying to build GPU sorting into
Postgres.


That sounds a bit harsh. I'm one of those indeed, I haven't look in the 
details not having enough time for it. At work we do GPU computing (not

the sort type stuff) and given the fact I'm a Postgres enthusiast I
asked my self: my server is able to sort around 500 milions integer per
seconds, if postgres was able to do that as well it would be very nice.

What I have to say? Sorry for my thoughts.


I had a patch committed a few hours ago that almost included the
capability of assigning an alternative sorting function, but only one
with the exact same signature as my variant of qsort_arg. pg_qsort
isn't used to sort tuples at all, by the way.


Then I did look in the wrong direction. Thank you for point that out.


Threading building blocks is not going to form the basis of any novel
sorting implementation, because comparators in general are not thread
safe, and it isn't available on all the platforms we support, and
because of how longjmp interacts with C++ stack unwinding and so on
and so on. Now, you could introduce some kind of parallelism into
sorting integers and floats, but that's an awful lot of work for a
marginal reward.


The TBB was just example that did come in my mind.
What do you mean with you could introduce some kind of parallelism?
As far as I know any algorithm using the divide and conquer can be
parallelized.

Regards
Gaetano Mendola



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

Re: [HACKERS] CUDA Sorting

2012-02-14 Thread Gaetano Mendola


On 13/02/2012 08:26, Greg Smith wrote:

On 02/11/2012 08:14 PM, Gaetano Mendola wrote:

The trend is to have server capable of running CUDA providing GPU via
external hardware (PCI Express interface with PCI Express switches),
look for example at PowerEdge C410x PCIe Expansion Chassis from DELL.


The C410X adds 16 PCIe slots to a server, housed inside a separate 3U
enclosure. That's a completely sensible purchase if your goal is to
build a computing cluster, where a lot of work is handed off to a set of
GPUs. I think that's even less likely to be a cost-effective option for
a database server. Adding a single dedicated GPU installed in a server
to accelerate sorting is something that might be justifiable, based on
your benchmarks. This is a much more expensive option than that though.
Details at http://www.dell.com/us/enterprise/p/poweredge-c410x/pd for
anyone who wants to see just how big this external box is.


I did some experimenst timing the sort done with CUDA and the sort
done with pg_qsort:
CUDA pg_qsort
33Milion integers: ~ 900 ms, ~ 6000 ms
1Milion integers: ~ 21 ms, ~ 162 ms
100k integers: ~ 2 ms, ~ 13 ms
CUDA time has already in the copy operations (host-device,
device-host).
As GPU I was using a C2050, and the CPU doing the pg_qsort was a
Intel(R) Xeon(R) CPU X5650 @ 2.67GHz


That's really interesting, and the X5650 is by no means a slow CPU. So
this benchmark is providing a lot of CPU power yet still seeing over a
6X speedup in sort times. It sounds like the PCI Express bus has gotten
fast enough that the time to hand data over and get it back again can
easily be justified for medium to large sized sorts.

It would be helpful to take this patch and confirm whether it scales
when using in parallel. Easiest way to do that would be to use the
pgbench -f feature, which allows running an arbitrary number of some
query at once. Seeing whether this acceleration continued to hold as the
number of clients increases is a useful data point.

Is it possible for you to break down where the time is being spent? For
example, how much of this time is consumed in the GPU itself, compared
to time spent transferring data between CPU and GPU? I'm also curious
where the bottleneck is at with this approach. If it's the speed of the
PCI-E bus for smaller data sets, adding more GPUs may never be
practical. If the bus can handle quite a few of these at once before it
saturates, it might be possible to overload a single GPU. That seems
like it would be really hard to reach for database sorting though; I
can't really defend justify my gut feel for that being true though.


There you go (times are in ms):

Size   H-D SORT D-H TOTAL
64   0.209824 0.479392 0.013856 0.703072
128  0.098144 0.41744  0.01312  0.528704
256  0.096832 0.420352 0.013696 0.53088
512  0.097568 0.3952   0.014464 0.507232
1024 0.09872  0.396608 0.014624 0.509952
2048 0.101344 0.56224  0.016896 0.68048
4096 0.106176 0.562976 0.02016  0.689312
8192 0.116512 0.571264 0.02672  0.714496
163840.136096 0.587584 0.040192 0.763872
327680.179296 0.658112 0.066304 0.903712
655360.212352 0.84816  0.118016 1.178528
131072   0.317056 1.1465   0.22784  1.691396
262144   0.529376 1.82237  0.42512  2.776866
524288   0.724032 2.39834  0.64576  3.768132
1048576  1.11162  3.51978  1.12176  5.75316
2097152  1.95939  5.93434  2.06992  9.96365
4194304  3.76192  10.6011  4.10614  18.46916
8388608  7.16845  19.9245  7.93741  35.03036
16777216 13.8693  38.7413  15.4073  68.0179
33554432 27.3017  75.6418  30.6646  133.6081
67108864 54.2171  151.192  60.327   265.7361

pg_sort

64   0.01
128  0.01
256  0.021000
512  0.128000
1024 0.092000
2048 0.196000
4096 0.415000
8192 0.883000
163841.881000
327683.96
655368.432000
131072  17.951000
262144  37.14
524288  78.32
1048576163.276000
2097152339.118000
4194304693.223000
8388608   1423.142000
16777216  2891.218000
33554432  5910.851000
67108864 11980.93

As you can notice the times with CUDA are lower than the timing I have 
reported on my previous post because the server was doing something else

in mean while, I have repeated those benchmarks with server completely
unused.

And this is the boost as in pg_sort/cuda :

64   0.0142232943
128  0.018914175
256  0.039556962
512  0.2070058671
1024 0.1804091365
2048 0.2880319774
4096 0.6078524674
8192 1.2372357578
163842.4637635625
327684.4106972133
655367.1742037525
131072   10.5090706139
262144   13.3719091955
524288   20.5834084369
1048576  28.2516043357
2097152  33.9618513296
4194304  37.5247168794
8388608  40.5135716561
16777216 42.4743633661
33554432 44.2394809896
67108864 45.1499777411



 I've never seen a PostgreSQL server capable of running CUDA, and I
 don't expect that to change.

That sounds like:

I think there is a world market

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Gaetano Mendola

On Feb 13, 2012 11:39 a.m., Kohei KaiGai kai...@kaigai.gr.jp wrote:

 2012/2/13 Greg Smith g...@2ndquadrant.com:
  On 02/11/2012 08:14 PM, Gaetano Mendola wrote:
 
  The trend is to have server capable of running CUDA providing GPU via
  external hardware (PCI Express interface with PCI Express switches),
look
  for example at PowerEdge C410x PCIe Expansion Chassis from DELL.
 
 
  The C410X adds 16 PCIe slots to a server, housed inside a separate 3U
  enclosure.  That's a completely sensible purchase if your goal is to
build a
  computing cluster, where a lot of work is handed off to a set of GPUs.
 I
  think that's even less likely to be a cost-effective option for a
database
  server.  Adding a single dedicated GPU installed in a server to
accelerate
  sorting is something that might be justifiable, based on your
benchmarks.
   This is a much more expensive option than that though.  Details at
  http://www.dell.com/us/enterprise/p/poweredge-c410x/pd for anyone who
wants
  to see just how big this external box is.
 
 
  I did some experimenst timing the sort done with CUDA and the sort done
  with pg_qsort:
CUDA  pg_qsort
  33Milion integers:   ~ 900 ms,  ~ 6000 ms
  1Milion integers:~  21 ms,  ~  162 ms
  100k integers:   ~   2 ms,  ~   13 ms
  CUDA time has already in the copy operations (host-device,
device-host).
  As GPU I was using a C2050, and the CPU doing the pg_qsort was a
Intel(R)
  Xeon(R) CPU X5650  @ 2.67GHz
 
 
  That's really interesting, and the X5650 is by no means a slow CPU.  So
this
  benchmark is providing a lot of CPU power yet still seeing over a 6X
speedup
  in sort times.  It sounds like the PCI Express bus has gotten fast
enough
  that the time to hand data over and get it back again can easily be
  justified for medium to large sized sorts.
 
  It would be helpful to take this patch and confirm whether it scales
when
  using in parallel.  Easiest way to do that would be to use the pgbench
-f
  feature, which allows running an arbitrary number of some query at once.
   Seeing whether this acceleration continued to hold as the number of
clients
  increases is a useful data point.
 
  Is it possible for you to break down where the time is being spent?  For
  example, how much of this time is consumed in the GPU itself, compared
to
  time spent transferring data between CPU and GPU?  I'm also curious
where
  the bottleneck is at with this approach.  If it's the speed of the
PCI-E bus
  for smaller data sets, adding more GPUs may never be practical.  If the
bus
  can handle quite a few of these at once before it saturates, it might be
  possible to overload a single GPU.  That seems like it would be really
hard
  to reach for database sorting though; I can't really defend justify my
gut
  feel for that being true though.
 
 
   I've never seen a PostgreSQL server capable of running CUDA, and I
   don't expect that to change.
 
  That sounds like:
 
  I think there is a world market for maybe five computers.
  - IBM Chairman Thomas Watson, 1943
 
 
  Yes, and 640K will be enough for everyone, ha ha.  (Having said the
640K
  thing is flat out denied by Gates, BTW, and no one has come up with
proof
  otherwise).
 
  I think you've made an interesting case for this sort of acceleration
now
  being useful for systems doing what's typically considered a data
warehouse
  task.  I regularly see servers waiting for far more than 13M integers to
  sort.  And I am seeing a clear trend toward providing more PCI-E slots
in
  servers now.  Dell's R810 is the most popular single server model my
  customers have deployed in the last year, and it has 5 X8 slots in it.
 It's
  rare all 5 of those are filled.  As long as a dedicated GPU works fine
when
  dropped to X8 speeds, I know a fair number of systems where one of those
  could be added now.
 
  There's another data point in your favor I didn't notice before your
last
  e-mail.  Amazon has a Cluster GPU Quadruple Extra Large node type that
  runs with NVIDIA Tesla hardware.  That means the installed base of
people
  who could consider CUDA is higher than I expected.  To demonstrate how
much
  that costs, to provision a GPU enabled reserved instance from Amazon
for one
  year costs $2410 at Light Utilization, giving a system with 22GB of
RAM
  and 1.69GB of storage.  (I find the reserved prices easier to compare
with
  dedicated hardware than the hourly ones)  That's halfway between the
  High-Memory Double Extra Large Instance (34GB RAM/850GB disk) at $1100
and
  the High-Memory Quadruple Extra Large Instance (64GB RAM/1690GB disk) at
  $2200.  If someone could prove sorting was a bottleneck on their server,
  that isn't an unreasonable option to consider on a cloud-based database
  deployment.
 
  I still think that an approach based on OpenCL is more likely to be
suitable
  for PostgreSQL, which was part of why I gave CUDA low odds here.  The
points
  in favor of OpenCL are:
 
  -Since you last posted

Re: [HACKERS] CUDA Sorting

2012-02-13 Thread Gaetano Mendola

On Feb 13, 2012 7:49 p.m., Greg Stark st...@mit.edu wrote:

 I don't think we should be looking at either CUDA or OpenCL directly.
 We should be looking for a generic library that can target either and
 is well maintained and actively developed. Any GPU code we write
 ourselves would rapidly be overtaken by changes in the hardware and
 innovations in parallel algorithms. If we find a library that provides
 a sorting api and adapt our code to use it then we'll get the benefits
 of any new hardware feature as the library adds support for them.

To sort integer I used the Thrust Nvidia library.

Re: [HACKERS] CUDA Sorting

2012-02-12 Thread Gaetano Mendola

On 12/02/2012 13:13, Oleg Bartunov wrote:

I'm wondering if CUDA will win in geomentry operations, for example,
tesing point @ complex_polygon

I'm not sure if the algorithm you mentioned can be implemented in terms
of vector algebra, blas, etc.

It's plenty of geometry operations implemented in CUDA out there, my
field of CUDA application is not this one so I'm not that much in it.

However I can point you to official NVIDIA npp library that provides
vector algebra algorithms, and some geometry algorithms as well.

http://developer.download.nvidia.com/compute/DevZone/docs/html/CUDALibraries/doc/NPP_Library.pdf

(take a look at around page 620).

Regards
Gaetano Mendola

Oleg
On Sun, 12 Feb 2012, Gaetano Mendola wrote:

On 19/09/2011 16:36, Greg Smith wrote:

On 09/19/2011 10:12 AM, Greg Stark wrote:

With the GPU I'm curious to see how well
it handles multiple processes contending for resources, it might be a
flashy feature that gets lots of attention but might not really be
very useful in practice. But it would be very interesting to see.

The main problem here is that the sort of hardware commonly used for
production database servers doesn't have any serious enough GPU to
support CUDA/OpenCL available. The very clear trend now is that all
systems other than gaming ones ship with motherboard graphics chipsets
more than powerful enough for any task but that. I just checked the 5
most popular configurations of server I see my customers deploy
PostgreSQL onto (a mix of Dell and HP units), and you don't get a
serious GPU from any of them.

Intel's next generation Ivy Bridge chipset, expected for the spring of
2012, is going to add support for OpenCL to the built-in motherboard
GPU. We may eventually see that trickle into the server hardware side of
things too.

The trend is to have server capable of running CUDA providing GPU via
external hardware (PCI Express interface with PCI Express switches),
look for example at PowerEdge C410x PCIe Expansion Chassis from DELL.

I did some experimenst timing the sort done with CUDA and the sort
done with pg_qsort:
CUDA pg_qsort
33Milion integers: ~ 900 ms, ~ 6000 ms
1Milion integers: ~ 21 ms, ~ 162 ms
100k integers: ~ 2 ms, ~ 13 ms

CUDA time has already in the copy operations (host-device,
device-host).

As GPU I was using a C2050, and the CPU doing the pg_qsort was a
Intel(R) Xeon(R) CPU X5650 @ 2.67GHz

Copy operations and kernel runs (the sort for instance) can run in
parallel, so while you are sorting a batch of data, you can copy the
next batch in parallel.

As you can see the boost is not negligible.

Next Nvidia hardware (Keplero family) is PCI Express 3 ready, so
expect in the near future the bottle neck of the
device-host-device copies to have less impact.

I strongly believe there is space to provide modern database engine of
a way to offload sorts to GPU.

I've never seen a PostgreSQL server capable of running CUDA, and I
don't expect that to change.

That sounds like:

I think there is a world market for maybe five computers.
- IBM Chairman Thomas Watson, 1943

Regards
Gaetano Mendola

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [HACKERS] CUDA Sorting

2012-02-11 Thread Gaetano Mendola


On 19/09/2011 21:41, PostgreSQL - Hans-Jürgen Schönig wrote:


On Sep 19, 2011, at 5:16 PM, Tom Lane wrote:


Greg Starkst...@mit.edu  writes:

That said, to help in the case I described you would have to implement
the tapesort algorithm on the GPU as well.


I think the real problem would be that we are seldom sorting just the
key values.  If you have to push the tuples through the GPU too, your
savings are going to go up in smoke pretty quickly …




i would argument along a similar line.
to make GPU code fast it has to be pretty much tailored to do exactly one thing 
- otherwise you have no chance to get anywhere close to card-bandwith.
if you look at two similar GPU codes which seem to do the same thing you 
might easily see that one is 10 times faster than the other - for bloody reason such as 
memory alignment, memory transaction size or whatever.
this opens a bit of a problem: PostgreSQL sorting is so generic and so flexible 
that i would be really surprised if somebody could come up with a solution 
which really comes close to what the GPU can do.
it would definitely be interesting to see a prototype, however.


Thrust Nvidia library provides the same sorting flexibility as postgres 
does.


// generate 32M random numbers on the host
thrust::host_vectorint h_vec(32  20);
thrust::generate(h_vec.begin(), h_vec.end(), rand);

// transfer data to the device
thrust::device_vectorint d_vec = h_vec;

// sort data on the device (846M keys per second on GeForce GTX 480)
thrust::sort(d_vec.begin(), d_vec.end());

// transfer data back to host
thrust::copy(d_vec.begin(), d_vec.end(), h_vec.begin());


as you can see the type to be ordered is template, and
the thrust::sort have also a version in where it takes the comparator to 
use.

So compared with pg_qsort  thrust::sort gives you the same flexibility.

http://docs.thrust.googlecode.com/hg/group__sorting.html

Regards
Gaetano Mendola











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

Re: [HACKERS] CUDA Sorting

2012-02-11 Thread Gaetano Mendola


On 19/09/2011 16:36, Greg Smith wrote:

On 09/19/2011 10:12 AM, Greg Stark wrote:

With the GPU I'm curious to see how well
it handles multiple processes contending for resources, it might be a
flashy feature that gets lots of attention but might not really be
very useful in practice. But it would be very interesting to see.


The main problem here is that the sort of hardware commonly used for
production database servers doesn't have any serious enough GPU to
support CUDA/OpenCL available. The very clear trend now is that all
systems other than gaming ones ship with motherboard graphics chipsets
more than powerful enough for any task but that. I just checked the 5
most popular configurations of server I see my customers deploy
PostgreSQL onto (a mix of Dell and HP units), and you don't get a
serious GPU from any of them.

Intel's next generation Ivy Bridge chipset, expected for the spring of
2012, is going to add support for OpenCL to the built-in motherboard
GPU. We may eventually see that trickle into the server hardware side of
things too.



The trend is to have server capable of running CUDA providing GPU via 
external hardware (PCI Express interface with PCI Express switches), 
look for example at PowerEdge C410x PCIe Expansion Chassis from DELL.


I did some experimenst timing the sort done with CUDA and the sort done 
with pg_qsort:

   CUDA  pg_qsort
33Milion integers:   ~ 900 ms,  ~ 6000 ms
1Milion integers:~  21 ms,  ~  162 ms
100k integers:   ~   2 ms,  ~   13 ms

CUDA time has already in the copy operations (host-device, device-host).

As GPU I was using a C2050, and the CPU doing the pg_qsort was a 
Intel(R) Xeon(R) CPU X5650  @ 2.67GHz


Copy operations and kernel runs (the sort for instance) can run in 
parallel, so while you are sorting a batch of data, you can copy the 
next batch in parallel.


As you can see the boost is not negligible.

Next Nvidia hardware (Keplero family) is PCI Express 3 ready, so expect 
in the near future the bottle neck of the device-host-device copies 
to have less impact.


I strongly believe there is space to provide modern database engine of
a way to offload sorts to GPU.

 I've never seen a PostgreSQL server capable of running CUDA, and I
 don't expect that to change.

That sounds like:

I think there is a world market for maybe five computers.
- IBM Chairman Thomas Watson, 1943

Regards
Gaetano Mendola


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

[HACKERS] bitfield and gcc

2012-02-10 Thread Gaetano Mendola

I wonder if somewhere in Postgres source we are relying on the GCC 
correct behaviour regarding the read-modify-write of bitfield in

structures.

Take a read at this https://lwn.net/Articles/478657/

sorry if this was already mentioned.


Regards
Gaetano Mendola

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

[HACKERS] Base64 decode/encode performance

2008-09-10 Thread Gaetano Mendola

Hi,
I have been experimenting with some base64 encoding/decoding implementation.

I find out that the one at http://code.google.com/p/stringencoders is the best
obtaining a 1.3 speedup vs the postgres one.

Do you think is worth to submit a patch that replaces the postgres base64 
implementation
with this one?


Regards
Gaetano Mendola

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

Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]

2008-06-21 Thread Gaetano Mendola

On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane [EMAIL PROTECTED] wrote:

 Gaetano Mendola [EMAIL PROTECTED] writes:
  we have faced lately dumps not valid, the bug can be replicated using a
 8.2.9 or
  a 8.3.1 server.

  These are the steps to create the database that will generate a not valid
 dump:

 This is a bug in your function: it will not work if the search path
 doesn't contain the public schema.  You'd be best advised to make it
 qualify the reference to t_public explicitly.


Yes, that's the way we are fixing it. Still I have a bitter taste being able
to
create a working database instance that doesn't generate a valid dump.

(Of course you realize that referencing any table at all in an
 immutable function is probably a mortal sin...)


Yes Tom I know, in our case that table is a lookup table, noone update,
delete, insert data in it, so from my point of view it is like I have
declared a
static array inside the function declaration.

-- 
cpp-today.blogspot.com

[HACKERS] Not valid dump [8.2.9, 8.3.1]

2008-06-20 Thread Gaetano Mendola

Hi all,
we have faced lately dumps not valid, the bug can be replicated using a 8.2.9 or
a 8.3.1 server.

These are the steps to create the database that will generate a not valid dump:

---
CREATE TABLE t_public (
a integer
);

CREATE OR REPLACE FUNCTION sp_public ( )
RETURNS INTEGER AS'
BEGIN
   PERFORM * FROM t_public LIMIT 1;
   RETURN 0;
END;
' LANGUAGE 'plpgsql'
IMMUTABLE;

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.table_ref(x integer primary key);
CREATE TABLE my_schema.table_test(x integer references my_schema.table_ref (x));

CREATE INDEX idx ON my_schema.table_test (x) WHERE x = sp_public();


Briefly: in the public schema we have a function that uses a table.
In another schema we have a table with a foreign key to another table, and an 
partial
index that uses the function in the public schema.

The function is immutable because in our case the table being used inside the 
function
is a lookup table (readonly).

When the dump is restored the index idx is created but the foreign key is not.
This is the error we obtain during the restore:

psql:test.dump:143: ERROR:  relation t_public does not exist
CONTEXT:  SQL statement SELECT  * FROM t_public LIMIT 1
PL/pgSQL function sp_public line 2 at perform
SQL statement SELECT fk.x FROM ONLY my_schema.table_test fk LEFT
OUTER JOIN ONLY my_schema.table_ref pk ON (pk.x=fk.x) WHERE pk.x
IS NULL AND (fk.x IS NOT NULL)

Regards






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

Re: [HACKERS] new field content lost

2008-04-18 Thread Gaetano Mendola

Tom Lane wrote:
 Gaetano Mendola [EMAIL PROTECTED] writes:
 since long time I have implemented a materialized view, today I had to add a
 new field and I faced the following (I believe) bug.
 The bug can be replicated on a 8.2.7
 
 Cached plan for the function's UPDATE.  Should work okay in 8.3 ...
 

It does.

Regards
Gaetano Mendola

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

[HACKERS] new field content lost

2008-04-17 Thread Gaetano Mendola

Hi all,
since long time I have implemented a materialized view, today I had to add a
new field and I faced the following (I believe) bug.

The bug can be replicated on a 8.2.7


-- SETUP
create table test (a integer, b integer);
create table test_trigger (a integer);

CREATE OR REPLACE FUNCTION trigger_test()
RETURNS TRIGGER AS'
DECLARE

BEGIN
   update test set b = b*10 where a = NEW.a;

   RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER trigger_test
AFTER INSERT OR UPDATE ON test_trigger DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trigger_test();

insert into test values (1,1),(2,2),(3,3);
insert into test_trigger values (1),(2),(3);


-- FROM CONNECTION A
update test_trigger set a=1 where a=1;

-- FROM CONNECTION B
alter table test add column c integer;
update test set c = 15;
select * from test;

-- FROM CONNECTION A
update test_trigger set a=2 where a=2;

--FROM CONNECTION B
select * from test;

you can see that the value c=15 for a=2 has been nullified



Regards
Gaetano


























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

[HACKERS] /etc/init.d/postgresql status error

2008-04-16 Thread Gaetano Mendola

I have just installed the rpm found at:

http://www.postgresql.org/ftp/binary/v8.2.7/linux/rpms/redhat/rhel-4-i386/

and the status option generates an error:

$ /etc/init.d/postgresql status
pidof: invalid options on command line!

pidof: invalid options on command line!

-p is stopped


I was able to fix it in the following mode:

  status)
#   status -p /var/run/postmaster.${PGPORT}.pid
status postmaster
script_result=$?
;;

Commented the original line and replaced with the one just below.

Regards
Gaetano Mendola


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

[HACKERS] Limit changes query plan

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,
I'm using 8.2.6 and I'm observing a trange behaviour using
offset and limits.

This are the two queries that are puzzling me:

explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, 
pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to = 1500 AND _from = 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 ;
  QUERY PLAN
- 

Limit  (cost=175044.75..175071.04 rows=10518 width=90)
  -  Sort  (cost=175044.75..175071.04 rows=10518 width=90)
Sort Key: c.nctr, c.nctn, c.ncts, c.rvel
-  Hash Join  (cost=25830.72..174342.12 rows=10518 width=90)
  Hash Cond: (c.id = dt.card_id)
  -  Bitmap Heap Scan on t_oa_2_00_card c  (cost=942.36..148457.19 
rows=101872 width=90)
Recheck Cond: (ecp = 18)
-  Bitmap Index Scan on i7_t_oa_2_00_card  
(cost=0.00..916.89 rows=101872 width=0)
  Index Cond: (ecp = 18)
  -  Hash  (cost=22743.45..22743.45 rows=171593 width=8)
-  Bitmap Heap Scan on t_oa_2_00_dt dt  
(cost=2877.26..22743.45 rows=171593 width=8)
  Recheck Cond: (_from = 1550)
  Filter: (_to = 1500)
  -  Bitmap Index Scan on i_oa_2_00_dt_from  
(cost=0.00..2834.36 rows=182546 width=0)
Index Cond: (_from = 1550)


explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, 
pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to = 1500 AND _from = 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 limit 5;
QUERY PLAN
- 

Limit  (cost=0.00..2125.12 rows=5 width=90)
  -  Nested Loop  (cost=0.00..4470402.02 rows=10518 width=90)
-  Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c  
(cost=0.00..3927779.56 rows=101872 width=90)
  Filter: (ecp = 18)
-  Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt  
(cost=0.00..5.31 rows=1 width=8)
  Index Cond: (dt.card_id = c.id)
  Filter: ((_to = 1500) AND (_from = 1550))


using the limit I have an execution time of minutes vs a some seconds.

What am I missing here ?

Regards
Gaetano Mendola



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHov3I7UpzwH2SGd4RApR+AJ0dG/+0MoB3PMD1kRgQt0BisHwQBACgzVwC
BN/SBWrvVxVE9eBLK0C1Pnw=
=9Ucp
-END PGP SIGNATURE-

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

Re: [HACKERS] Limit changes query plan

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Martijn van Oosterhout wrote:
 On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Hi all,
 I'm using 8.2.6 and I'm observing a trange behaviour using
 offset and limits.
 
 Please post EXPLAIN ANALYZE output so we can see what's actually taking
 the time.

The analyze is still running (I launched it 30 mins ago), I'll post it as soon
I have it.

Disabling the nested_loop ( set enable_nestloop = false ) the query with the 
limit
has now the same execution time without the limit.

I don't get why a limit is going to change the query plan and most of all 
decreasing
the performances.


Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHowXA7UpzwH2SGd4RAomqAJ409579Jk7d5FYWf92PjOYDRxWNIQCggg1w
1WJcVmn2g1MASBGh9OtCQ0Q=
=h2Z6
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Limit changes query plan

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gregory Stark wrote:
 Gaetano Mendola [EMAIL PROTECTED] writes:
 
 I don't get why a limit is going to change the query plan and most of all 
 decreasing
 the performances.
 
 Until we see the explain analyze it won't be clear what exactly is going on.
 But in theory a LIMIT can definitely change the plan because the planner knows
 it won't need to generate all the rows to satisfy the LIMIT.
 
 In the plans you gave note that the plan for the unlimited query has a Sort so
 it has to produce all the records every time. The second query produces the
 records in order so if the LIMIT is satisfied quickly then it can save a lot
 of work.
 
 It's evidently guessing wrong about the limit being satisfied early. The
 non-indexed restrictions might be pruning out a lot more records than the
 planner expects. Or possibly the table is just full of dead records.
 

Here the analyze result:


explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, 
pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON 
(dt.card_id = c.id) WHERE ecp=18 AND _to = 1500 AND _from = 1550 ORDER BY 
nctr,nctn,ncts,rvel offset 0 limit 5;

   
QUERY PLAN   
---
Limit  (cost=0.00..2125.12 rows=5 width=90) (actual 
time=3399923.424..3399960.174 rows=5 loops=1)
  -  Nested Loop  (cost=0.00..4470402.02 rows=10518 width=90) (actual 
time=3399923.420..3399960.156 rows=5 loops=1)
-  Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c  
(cost=0.00..3927779.56 rows=101872 width=90) (actual 
time=3399892.632..3399896.773 rows=50 loops=1)
  Filter: (ecp = 18)
-  Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt  
(cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
  Index Cond: (dt.card_id = c.id)
  Filter: ((_to = 1500) AND (_from = 1550))
Total runtime: 3399960.277 ms


explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, 
pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON 
(dt.card_id = c.id) WHERE ecp=18 AND _to = 1500 AND _from = 1550 ORDER BY 
nctr,nctn,ncts,rvel offset 0 ;
  QUERY 
PLAN
-
Limit  (cost=175044.75..175071.04 rows=10518 width=90) (actual 
time=2425.138..2435.633 rows=3298 loops=1)
  -  Sort  (cost=175044.75..175071.04 rows=10518 width=90) (actual 
time=2425.134..2428.812 rows=3298 loops=1)
Sort Key: c.nctr, c.nctn, c.ncts, c.rvel
-  Hash Join  (cost=25830.72..174342.12 rows=10518 width=90) (actual 
time=797.540..2382.900 rows=3298 loops=1)
  Hash Cond: (c.id = dt.card_id)
  -  Bitmap Heap Scan on t_oa_2_00_card c  (cost=942.36..148457.19 
rows=101872 width=90) (actual time=70.212..1507.429 rows=97883 loops=1)
Recheck Cond: (ecp = 18)
-  Bitmap Index Scan on i7_t_oa_2_00_card  
(cost=0.00..916.89 rows=101872 width=0) (actual time=53.340..53.340 rows=97883 
loops=1)
  Index Cond: (ecp = 18)
  -  Hash  (cost=22743.45..22743.45 rows=171593 width=8) (actual 
time=726.597..726.597 rows=89277 loops=1)
-  Bitmap Heap Scan on t_oa_2_00_dt dt  
(cost=2877.26..22743.45 rows=171593 width=8) (actual time=86.181..593.275 
rows=89277 loops=1)
  Recheck Cond: (_from = 1550)
  Filter: (_to = 1500)
  -  Bitmap Index Scan on i_oa_2_00_dt_from  
(cost=0.00..2834.36 rows=182546 width=0) (actual time=80.863..80.863 
rows=201177 loops=1)
Index Cond: (_from = 1550)
Total runtime: 2440.396 ms



Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHoytQ7UpzwH2SGd4RAujPAKDkM53sirwNFa7jH/Q3R2y1/QAcKQCgn9VH
pUSwTkR3c963BoCbNwG+W6Y=
=s7Vr
-END PGP SIGNATURE-

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

Re: [HACKERS] Limit changes query plan

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane wrote:
 Greg Stark [EMAIL PROTECTED] writes:
 -  Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt  (cost=0.00..5.31 
 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
 Index Cond: (dt.card_id = c.id)
 Filter: ((_to = 1500) AND (_from = 1550))
 Total runtime: 3399960.277 ms
 
 Also, are 1500 and 1550 user-supplied parameters or are they part of a small 
 set of possible values? You could consider having a partial index on 
 card_id WHERE _to = 1500 AND _from = 1550. The numbers don't even have 
 to match exactly as long as they include all the records the query needs.
 
 That side of the join isn't where the problem is, though.
 
 If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel
 would probably fix the performance issue very nicely.
 

As always you are right, creating the index  ivan btree (ecp, nctr, nctn, 
ncts, rvel)

that query with the limit responds now in the blink of an eye:


 explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, 
 pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to = 1500 AND _from = 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 limit 5;
  QUERY PLAN
- 
---
 Limit  (cost=0.00..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 
loops=1)
   -  Nested Loop  (cost=0.00..778392.80 rows=10518 width=90) (actual 
time=0.099..0.594 rows=5 loops=1)
 -  Index Scan using ivan on t_oa_2_00_card c  (cost=0.00..235770.34 
rows=101872 width=90) (actual time=0.024..0.134 rows=50 loops=1)
   Index Cond: (ecp = 18)
 -  Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt  
(cost=0.00..5.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=50)
   Index Cond: (dt.card_id = c.id)
   Filter: ((_to = 1500) AND (_from = 1550))
 Total runtime: 0.700 ms
(8 rows)


Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHo1SB7UpzwH2SGd4RAhTeAJ0WL49jjUgCWSrNopV/8L+rbOLaEgCfTDlh
crAHZYxxTYz6VqTDggqW7x0=
=dKey
-END PGP SIGNATURE-

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

[HACKERS] Stats collector on rampage (8.2.3)

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,
it seems that the stats collector on my box is using more CPU than
it did in the past.

This is what I'm observing:

CPU usage for the stat process: 25% flat

$ psql -c select version()
version
- 
---
 PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 
20060404 (Red Hat 3.4.6-3)
(1 row)


$ strace -tt -p 10773
[...]
09:47:37.867655 write(3, 
\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
09:47:37.867738 write(3, 
\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 823) = 823
09:47:37.867820 close(3)= 0
09:47:37.867862 munmap(0xb7ced000, 4096) = 0
09:47:37.867906 rename(global/pgstat.tmp, global/pgstat.stat) = 0
09:47:37.868188 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868245 recv(7, 
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347..., 1000, 0) = 976
09:47:37.868317 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 500}}, 
NULL) = 0
09:47:37.868372 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868428 recv(7, 
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0g\n\0..., 1000, 0) = 976
09:47:37.868501 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868559 recv(7, 
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0i\n\0..., 1000, 0) = 976
09:47:37.868629 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868687 recv(7, 
\1\0\0\0\4\3\0\0\rg\0\0\v\0\0\0\0\0\0\0\0\0\0\0^\n\0\0..., 1000, 0) = 772
09:47:37.868757 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868815 recv(7, 
\1\0\0\0\240\0\0\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0\354..., 1000, 0) = 160
09:47:37.868886 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.868944 recv(7, 
\1\0\0\0\240\0\0\0\rg\0\0\2\0\0\0\1\0\0\0\0\0\0\0\n\0..., 1000, 0) = 160
09:47:37.869012 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.869070 recv(7, 
\1\0\0\0l\1\0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\354\4\0..., 1000, 0) = 364
09:47:37.869141 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.869198 recv(7, 
\1\0\0\0\300\2\0\0\rg\0\0\n\0\0\0\1\0\0\0\0\0\0\0007\n..., 1000, 0) = 704
09:47:37.869267 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.869329 recv(7, 
\1\0\0\0(\1\0\0\rg\0\0\4\0\0\0\1\0\0\0\0\0\0\0/\n\0\0\0..., 1000, 0) = 296
09:47:37.869398 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
09:47:37.869456 recv(7, 
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347..., 1000, 0) = 976
09:47:37.869524 --- SIGALRM (Alarm clock) @ 0 (0) ---
09:47:37.869575 sigreturn() = ? (mask now [])
09:47:37.869659 getppid()   = 10768
09:47:37.869702 open(global/pgstat.tmp, O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE, 
0666) = 3
09:47:37.869775 fstat64(3, {st_mode=S_IFREG|0600, st_size=0, ...}) = 0
09:47:37.869871 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7ced000
09:47:37.869928 write(3, 
\226\274\245\1D\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
09:47:37.870252 write(3, 
;\n\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
[...]

and doing the statistics on the system calls:


$ time strace -c -p 10773
Process 10773 attached - interrupt to quit
Process 10773 detached
% time seconds  usecs/call callserrors syscall
- -- --- --- - - 
 62.704.980721  16307851   write
 25.722.043299 156 13058  3039 poll
  9.610.763046 248  3078   rename
  0.640.050992  17  3079   open
  0.490.038819   4 10019   recv
  0.260.020469   7  3078   munmap
  0.130.010344   3  3078   close
  0.120.009425   3  3079   mmap2
  0.110.008353   3  3079   setitimer
  0.090.007114   2  3079  3039 sigreturn
  0.070.005923   2  3079   fstat64
  0.060.004734   2  3079   getppid
- -- --- --- - - 
100.007.943239358636  6078 total

real0m16.313s
user0m1.428s
sys 0m3.802s


so instead of 32 or such rename it did the rename 3K times.

To solve the problem is it possible to kill that process? (will it be 
respawned?)


Regards
Gaetano Mendola











-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHJZ9I7UpzwH2SGd4RAqwrAJ9vyt5fd1rdEu+uTnef6QpFYVBbhwCePB69
b0jA7Ko85TyEfMqAmVVRy/w=
=ax+l

Re: [HACKERS] Stats collector on rampage (8.2.3)

hubert depesz lubaczewski wrote:
  On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote:
  it seems that the stats collector on my box is using more CPU than
  it did in the past.
 
  it's well known bug, and it was fixed in 8.2.4:
  http://www.postgresql.org/docs/current/interactive/release-8-2-4.html
  ...
  Prevent the statistics collector from writing to disk too frequently
  (Tom)
  ...

I saw that, upgrading the DB at this very moment is not doable, killing
that process will the postmaster respawn another one? BTW I discover that
it was triggered by the time change due the daylight saving.

Regards
Gaetano Mendola

---(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: [HACKERS] Stats collector on rampage (8.2.3)

hubert depesz lubaczewski wrote:
 On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote:
 it seems that the stats collector on my box is using more CPU than
 it did in the past.
 
 it's well known bug, and it was fixed in 8.2.4:
 http://www.postgresql.org/docs/current/interactive/release-8-2-4.html
 ...
 Prevent the statistics collector from writing to disk too frequently
 (Tom)
 ...

I saw that, upgrading the DB at this very moment is not doable, killing
that process will the postmaster respawn another one? BTW I discover that
it was triggered by the time change due the daylight saving.

Regards
Gaetano Mendola

---(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: [HACKERS] Stats collector on rampage (8.2.3)

Andrew Dunstan wrote:
 
 
 Gaetano Mendola wrote:
 hubert depesz lubaczewski wrote:
  
 On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote:
  
 it seems that the stats collector on my box is using more CPU than
 it did in the past.
   
 it's well known bug, and it was fixed in 8.2.4:
 http://www.postgresql.org/docs/current/interactive/release-8-2-4.html
 ...
 Prevent the statistics collector from writing to disk too frequently
 (Tom)
 ...
   

 I saw that, upgrading the DB at this very moment is not doable, killing
 that process will the postmaster respawn another one? BTW I discover that
 it was triggered by the time change due the daylight saving.


   
 
 you do realize that this upgrade wouldn't require a dump/restore, don't
 you? It would be close to instantaneous.

Sure I do, for me it's even easier than that, I use DRBD so I can just shut
down one node upgrade it and then upgrade the other one; however upgrade the
DB means for me touch an entire satellite trasmission platform, I have to
schedule a platform maintenance for that...

Regards
Gaetano Mendola

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

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

Re: [HACKERS] Stats collector on rampage (8.2.3)

Magnus Hagander wrote:
 Gaetano Mendola wrote:
 hubert depesz lubaczewski wrote:
 On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote:
 it seems that the stats collector on my box is using more CPU than
 it did in the past.
 it's well known bug, and it was fixed in 8.2.4:
 http://www.postgresql.org/docs/current/interactive/release-8-2-4.html
 ...
 Prevent the statistics collector from writing to disk too frequently
 (Tom)
 ...
 I saw that, upgrading the DB at this very moment is not doable, killing
 that process will the postmaster respawn another one? BTW I discover that
 it was triggered by the time change due the daylight saving.
 
 IIRC, it will. You need to change postgresql.conf and disable the stats
 collector. If you do that, it won't be started.
 
 Shouldn't be trigged by DST.

The high cpu usage started at that time, may be that is another problem of some
applications stuck on it. I will investigate further.

Gaetano

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

[HACKERS] Lock table in non-volatile functions

2007-03-16 Thread Gaetano Mendola

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,
I'm observing that is not allowed to LOCK a table in a
STABLE/IMMUTABLE function but at same time is allowed
a SELECT FOR UPDATE.

Is that normal?




-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF+lsm7UpzwH2SGd4RAjloAJ4j/AOdJhGMRnvM/TKVpKHPwesAOACeO4mT
OQhSwR1of3xS7HSSvtjGiQc=
=nFFM
-END PGP SIGNATURE-

---(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: [HACKERS] Lock table in non-volatile functions

2007-03-16 Thread Gaetano Mendola


Tom Lane wrote:

Gaetano Mendola [EMAIL PROTECTED] writes:

I'm observing that is not allowed to LOCK a table in a
STABLE/IMMUTABLE function but at same time is allowed
a SELECT FOR UPDATE.


Really?  AFAICS, CommandIsReadOnly() will reject SELECT FOR UPDATE too.


kalman=# select version();
version

 PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 
4.1.1 20070105 (Red Hat 4.1.1-51)

(1 row)

kalman=# CREATE TABLE test( a integer );
CREATE TABLE
kalman=# INSERT INTO test VALUES ( 1 );
INSERT 0 1
kalman=# CREATE OR REPLACE FUNCTION sp_test()
kalman-# RETURNS INTEGER AS $body$
kalman$# DECLARE
kalman$#  my_integer integer;
kalman$#  my_port_set RECORD;
kalman$# BEGIN
kalman$#  FOR my_port_set IN
kalman$#  SELECT a
kalman$#  FROM test
kalman$#  FOR UPDATE
kalman$#  LOOP
kalman$#  my_integer = 0;
kalman$#  END LOOP;
kalman$# RETURN 0;
kalman$# END;
kalman$# $body$ language 'plpgsql'
kalman-# STABLE;
CREATE FUNCTION
kalman=# select sp_test();
 sp_test
-
   0
(1 row)

BTW why forbid the lock in a non volatile function or (if you fix this) 
the SELECT FOR UPDATE ?


Regards
Gaetano Mendola

---(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: [HACKERS] Calculated view fields (8.1 != 8.2)

2007-03-15 Thread Gaetano Mendola

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane wrote:
 Gaetano Mendola [EMAIL PROTECTED] writes:
 I can immagine a case when a lower module exports a view to upper layer 
 stating
 the interface as list of fields:
 
 first_name, last_name, 
 
 with an *hidden* field that is a function call that updates the statistics on
 how many time a given record was selected, then this technique can not be 
 used
 anymore starting with 8.2.x.
 
 You're living in a dream world if you think that works reliably in *any*
 version of Postgres.  But for starters, what is your definition of
 selected --- pulled from the physical table?  Accumulated into an
 aggregate?  Delivered as a recognizable row to the client?  Delivered N
 times to the client due to joining N times to some other table?

Well that was a not good example, I don't have any problem in mark from now
on all my function as stable/immutable (the one I use on views) but still
I believe is source of bad performance evaluate a function on rows discarded and
at same time this break the principle of least surprise.

Regards
Gaetano Mendola

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF9U8R7UpzwH2SGd4RAhoGAKDSpUSQ3lGEdIdFWLwQjxoZXUAS1ACdGtht
TZg9BKScbzGO0MzpHy0Gr80=
=auwk
-END PGP SIGNATURE-

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

Re: [HACKERS] Backend crash in 8.2.3 with plpgsql function

2007-03-15 Thread Gaetano Mendola

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane wrote:
 SM [EMAIL PROTECTED] writes:
 I got a backend crash in Postgresql 8.2.3 with the plpgsql function.
 The following statement in psql causes a signal 11:
 psql=# create function testpl() returns void as 'begin return; 
 end;'language 'plpgsql';
 
 Worksforme ...

For me too.

$ psql
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

kalman=# create function testpl() returns void as 'begin return; end;'language 
'plpgsql';
CREATE FUNCTION
kalman=# select version();
version
- 

 PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 
20070105 (Red Hat 4.1.1-51)
(1 row)




Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF+V2/7UpzwH2SGd4RAk29AJ44FZFMnsFHJV+uOcQZpuD0cGN/YACgjxjY
4lVP/g+/PLs2+RfOFtpBJtE=
=/Vae
-END PGP SIGNATURE-

---(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: [HACKERS] UPDATE using sub selects

2007-03-15 Thread Gaetano Mendola

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

NikhilS wrote:

 I have tried some update-subselect variations and they seem to work. For
 example the case in the src/test/regress/sql/update.sql, which used to
 fail till now, seems to work:
 
 UPDATE update_test SET (a,b) = (select a,b FROM update_test where c =
 'foo')
   WHERE a = 10;

What's the expected result if the tuple from subselect is more than 1?
I expect no update at all in case of void result set, is this the case ?



Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF+Vx47UpzwH2SGd4RAvyVAKCGK5pC9B/lmrNjAFPGXhm5ialwSwCglM2n
DxrxWyvJASX5WSF9B8cAMas=
=AoVF
-END PGP SIGNATURE-

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

Re: [HACKERS] Calculated view fields (8.1 != 8.2)

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gaetano Mendola wrote:
 Tom Lane wrote:
 Gaetano Mendola [EMAIL PROTECTED] writes:
 [ 8.2 evaluates volatile functions in the targetlist of a view ]
 If I mark the function as STABLE or IMMUTABLE then even with version
 8.2 the function is not evaluated. Is this the intended behavior?
 Yes; people complained that we needed to be more careful about the
 number of times volatile functions get evaluated.
 
 I suspect that functions are evaluated also for record discarded due to
 joins. Is that the case?
 
 Like:
 
 SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4;
 
 If ta is a view with some calculated fields are the function on ta
 evaluated only for record matching the filters or in some case (
 like a full scan on ta ) also for the records discarded due to the join?
 

I did a check on a 8.2 and I can confirm my suspects:

kalman=#  create table ta ( a integer, b integer );
CREATE TABLE
kalman=#  create table tb ( b integer, c integer );
CREATE TABLE
kalman=#
kalman=#  CREATE OR REPLACE FUNCTION sp_test ( INTEGER )
kalman-#  RETURNS INTEGER AS'
kalman'#  DECLARE
kalman'# a_idALIAS FOR $1;
kalman'#  BEGIN
kalman'# RAISE NOTICE ''here'';
kalman'#
kalman'# return 3;
kalman'#  END;
kalman'#  ' LANGUAGE 'plpgsql';
CREATE FUNCTION
kalman=#
kalman=#  CREATE OR REPLACE VIEW v_ta AS
kalman-#SELECT
kalman-#   sp_test(a) AS a,
kalman-#   b  AS b
kalman-#FROM
kalman-#   ta c
kalman-#  ;
CREATE VIEW
kalman=#
kalman=#  insert into ta values (2,3);
INSERT 0 1
kalman=#  insert into ta values (3,4);
INSERT 0 1
kalman=#  insert into tb values (4,5);
INSERT 0 1
kalman=#
kalman=#  select * from v_ta join tb using (b) where c = 5;
NOTICE:  here
NOTICE:  here
 b | a | c
- ---+---+---
 4 | 3 | 5
(1 row)


Is really this what we want? I did a migration 8.0.x = 8.2.3 and I had on 
first hour of service up
lot of queries blocked due to this, consider in my case I have on v_ta 
milions of records and usually
that join extracts 1 row. Is there a way to set till I don't check all my huge 
schema to disable this
behaviour?

Regards
Gaetano Mendola



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF8TAc7UpzwH2SGd4RAgajAKCvIxLH9JSBk4gxSbuaq4WE2y7v2wCfbnRa
jWDV3hlEq/Loye6G+E2S9Ew=
=LR5T
-END PGP SIGNATURE-

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

   http://archives.postgresql.org

Re: [HACKERS] Calculated view fields (8.1 != 8.2)

Martijn van Oosterhout wrote:
 On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote:
 Is really this what we want? I did a migration 8.0.x = 8.2.3 and I had on 
 first hour of service up
 lot of queries blocked due to this, consider in my case I have on v_ta 
 milions of records and usually
 that join extracts 1 row. Is there a way to set till I don't check all my 
 huge schema to disable this
 behaviour?
 
 Most people figured it was a improvment. It's configured per function
 now, which wasn't the case before. I dont't think there was ever any
 discussion about having a global switch.

Well it's not an improvement in term of performances but a performance 
degradation in the best case and
in the worst can be devastating:


create table ta ( a integer, b integer );
CREATE TABLE
create table tb ( b integer, c integer );
CREATE TABLE

CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER )
RETURNS INTEGER AS'
DECLARE
   a_idALIAS FOR $1;
BEGIN
   DELETE FROM ta where a = a_id;
   return 0;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION

CREATE OR REPLACE VIEW v_ta AS
  SELECT
 sp_delete_selected_row(a) AS a,
 b AS b
  FROM
 ta
;
CREATE VIEW

insert into ta values (2,3);
INSERT 0 1
insert into ta values (3,4);
INSERT 0 1
insert into tb values (4,5);
INSERT 0 1

select * from v_ta join tb using (b) where c = 5;

 b | a | c
---+---+---
 4 | 0 | 5
(1 row)

select * from ta;
 a | b
---+---
(0 rows)


All rows are gone instead of the only one extracted from that query. IMHO is a 
undesired side effect.
In my case I destroyed my application statistics on how many time a certain row 
was extracted.


Regards
Gaetano Mendola





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

Re: [HACKERS] Calculated view fields (8.1 != 8.2)

Florian G. Pflug wrote:
 Gaetano Mendola wrote:
 Martijn van Oosterhout wrote:
 On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote:
 Is really this what we want? I did a migration 8.0.x = 8.2.3 and I
 had on first hour of service up
 lot of queries blocked due to this, consider in my case I have on
 v_ta milions of records and usually
 that join extracts 1 row. Is there a way to set till I don't check
 all my huge schema to disable this
 behaviour?
 Most people figured it was a improvment. It's configured per function
 now, which wasn't the case before. I dont't think there was ever any
 discussion about having a global switch.

 Well it's not an improvement in term of performances but a performance
 degradation in the best case and
 in the worst can be devastating:

 create table ta ( a integer, b integer );
 CREATE TABLE
 create table tb ( b integer, c integer );
 CREATE TABLE

 CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER )
 RETURNS INTEGER AS'
 DECLARE
a_idALIAS FOR $1;
 BEGIN
DELETE FROM ta where a = a_id;
return 0;
 END;
 ' LANGUAGE 'plpgsql';
 CREATE FUNCTION

 CREATE OR REPLACE VIEW v_ta AS
   SELECT
  sp_delete_selected_row(a) AS a,
  b AS b
   FROM
  ta
 ;
 CREATE VIEW

 insert into ta values (2,3);
 INSERT 0 1
 insert into ta values (3,4);
 INSERT 0 1
 insert into tb values (4,5);
 INSERT 0 1

 select * from v_ta join tb using (b) where c = 5;

  b | a | c
 ---+---+---
  4 | 0 | 5
 (1 row)

 select * from ta;
  a | b
 ---+---
 (0 rows)


 All rows are gone instead of the only one extracted from that query.
 IMHO is a undesired side effect.
 In my case I destroyed my application statistics on how many time a
 certain row was extracted.
 
 This is insane. Whoever creates a view like that on a production system
 should *immediatly* be carried away from his keyboard, to prevent
 further damage. Imagine someone using View Data on  this view in
 pgadmin.. I don't wanna be near him when he clicks Refresh, and
 suddenly all data is gone...
 
 Maybe calling volatile functions in selects and views should be
 forbidden entirely, except for volatile functions in the top-level
 select clause,
 to support things like select ..., nextval('seq') from 
 
 But it's probably not worth the effort - there will always be creative
 ways to shoot yourself into your foot.

I full agree with this, that was just an extreme example of an hidden undesired
call. In my framework I don't have by coding rule any function with side 
effects
applied at view fields, however I have some functions not marked correctly as
STABLE ( mea culpa ) that degraded the performances until I realized what was 
going
on; I'm in the opinion that is not sane call a function not marked as 
stable/immutable
for discarded column (I can in some way accept this ) and most of all on 
discarded
rows.

Regards
Gaetano Mendola
















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

Re: [HACKERS] Calculated view fields (8.1 != 8.2)