------=_Part_6821_22776651.1215535119416
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Well, what does a revolution like this require of Postgres? That is the
question.
I have looked at the I/O drive, and it could increase our DB throughput
significantly over a RAID array.
Ideally, I would put a few key tables and the WAL, etc. I'd also want all
the sort or hash overflow from work_mem to go to this device. Some of our
tables / indexes are heavily written to for short periods of time then
more
infrequently later -- these are partitioned by date. I would put the
fresh
ones on such a device then move them to the hard drives later.
Ideally, we would then need a few changes in Postgres to take full
advantage
of this:
#1 Per-Tablespace optimizer tuning parameters. Arguably, this is already
needed. The tablespaces on such a solid state device would have random
and
sequential access at equal (low) cost. Any one-size-fits-all set of
optimizer variables is bound to cause performance issues when two
tablespaces have dramatically different performance profiles.
#2 Optimally, work_mem could be shrunk, and the optimizer would have to
not
preferentially sort - group_aggregate whenever it suspected that work_mem
was too large for a hash_agg. A disk based hash_agg will pretty much win
every time with such a device over a sort (in memory or not) once the
number
of rows to aggregate goes above a moderate threshold of a couple hundred
thousand or so.
In fact, I have several examples with 8.3.3 and a standard RAID array
where
a hash_agg that spilled to disk (poor or -- purposely distorted statistics
cause this) was a lot faster than the sort that the optimizer wants to do
instead. Whatever mechanism is calculating the cost of doing sorts or
hashes on disk will need to be tunable per tablespace.
I suppose both of the above may be one task -- I don't know enough about
the
Postgres internals.
#3 Being able to move tables / indexes from one tablespace to another as
efficiently as possible.
There are probably other enhancements that will help such a setup. These
were the first that came to mind.
On Tue, Jul 8, 2008 at 2:49 AM, Markus Wanner <markus@[EMAIL PROTECTED]
> wrote:
> Hi,
>
> Jonah H. Harris wrote:
>
>> I'm not sure how those cards work, but my guess is that the CPU will
>> go 100% busy (with a near-zero I/O wait) on any sizable workload. In
>> this case, the current pgbench configuration being used is quite small
>> and probably won't resemble this.
>>
>
> I'm not sure how they work either, but why should they require more CPU
> cycles than any other PCIe SAS controller?
>
> I think they are doing a clever step by directly attaching the NAND
chips
> to PCIe, instead of piping all the data through SAS or (S)ATA (and then
> through PCIe as well). And if the controller chip on the card isn't
> absolutely bogus, that certainly has the potential to reduce latency and
> improve throughput - compared to other SSDs.
>
> Or am I missing something?
>
> Regards
>
> Markus
>
>
>
> --
> Sent via pgsql-performance mailing list
(pgsql-performance@[EMAIL PROTECTED]
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
------=_Part_6821_22776651.1215535119416
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Well, what does a revolution like this require of Postgres?
That is the question.<br><br>I have looked at the I/O drive, and it could
increase our DB throughput significantly over a RAID
array.<br><br>Ideally, I would put a few key tables and the WAL,
etc. I'd also want all the sort or hash overflow from work_mem
to go to this device. Some of our tables / indexes are heavily
written to for short periods of time then more infrequently later -- these
are partitioned by date. I would put the fresh ones on such a device
then move them to the hard drives later.<br>
<br>Ideally, we would then need a few changes in Postgres to take full
advantage of this:<br><br>#1 Per-Tablespace optimizer tuning
parameters. Arguably, this is already needed. The tablespaces
on such a solid state device would have random and sequential access at
equal (low) cost. Any one-size-fits-all set of optimizer
variables is bound to cause performance issues when two tablespaces have
dramatically different performance profiles.<br>
#2 Optimally, work_mem could be shrunk, and the optimizer would have
to not preferentially sort - group_aggregate whenever it suspected that
work_mem was too large for a hash_agg. A disk based hash_agg will
pretty much win every time with such a device over a sort (in memory or
not) once the number of rows to aggregate goes above a moderate threshold
of a couple hundred thousand or so.<br>
In fact, I have several examples with 8.3.3 and a standard RAID array
where a hash_agg that spilled to disk (poor or -- purposely distorted
statistics cause this) was a lot faster than the sort that the optimizer
wants to do instead. Whatever mechanism is calculating the
cost of doing sorts or hashes on disk will need to be tunable per
tablespace.<br>
<br>I suppose both of the above may be one task -- I don't know enough
about the Postgres internals.<br><br>#3 Being able to move tables /
indexes from one tablespace to another as efficiently as
possible.<br><br>There are probably other enhancements that will help such
a setup. These were the first that came to mind.<br>
<br><div class="gmail_quote">On Tue, Jul 8, 2008 at 2:49 AM, Markus Wanner
<markus@[EMAIL PROTECTED]
wrote:<br><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.8ex; padding-left: 1ex;">
Hi,<div class="Ih2E3d"><br>
<br>
Jonah H. Harris wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I'm not sure how those cards work, but my guess is that the CPU
will<br>
go 100% busy (with a near-zero I/O wait) on any sizable workload.
In<br>
this case, the current pgbench configuration being used is quite small<br>
and probably won't resemble this.<br>
</blockquote>
<br></div>
I'm not sure how they work either, but why should they require more
CPU cycles than any other PCIe SAS controller?<br>
<br>
I think they are doing a clever step by directly attaching the NAND chips
to PCIe, instead of piping all the data through SAS or (S)ATA (and then
through PCIe as well). And if the controller chip on the card isn't
absolutely bogus, that certainly has the potential to reduce latency and
improve throughput - compared to other SSDs.<br>
<br>
Or am I missing something?<br>
<br>
Regards<br><font color="#888888">
<br>
Markus</font><div><div></div><div class="Wj3C7c"><br>
<br>
<br>
-- <br>
Sent via pgsql-performance mailing list (<a
href="mailto:pgsql-performance@[EMAIL PROTECTED]
"
target="_blank">pgsql-performance@[EMAIL PROTECTED]
>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-performance"
target="_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</div></div></blockquote></div><br>
------=_Part_6821_22776651.1215535119416--
|
