Re: getting the most of out multi-core systems for repeated complex SELECT statements

Поиск
Список
Период
Сортировка
От Andy Colson
Тема Re: getting the most of out multi-core systems for repeated complex SELECT statements
Дата
Msg-id 4D4B70B1.2080809@squeakycode.net
обсуждение исходный текст
Ответ на Re: getting the most of out multi-core systems for repeated complex SELECT statements  (Greg Smith <greg@2ndquadrant.com>)
Ответы Re: getting the most of out multi-core systems for repeated complex SELECT statements  (Greg Smith <greg@2ndquadrant.com>)
Список pgsql-performance
On 02/03/2011 04:56 PM, Greg Smith wrote:
> Scott Marlowe wrote:
>> On Thu, Feb 3, 2011 at 8:57 AM,<gnuoytr@rcn.com>  wrote:
>>
>>> Time for my pet meme to wiggle out of its hole (next to Phil's, and a day later).  For PG to prosper in the future,
ithas to embrace the multi-core/processor/SSD machine at the query level.  It has to.  And 
>>>
>>
>> I'm pretty sure multi-core query processing is in the TODO list.  Not
>> sure anyone's working on it tho.  Writing a big check might help.
>>
>
> Work on the exciting parts people are interested in is blocked behind completely mundane tasks like coordinating how
themultiple sessions are going to end up with a consistent view of the database. See "Export snapshots to other
sessions"at http://wiki.postgresql.org/wiki/ClusterFeatures for details on that one. 
>
> Parallel query works well for accelerating CPU-bound operations that are executing in RAM. The reality here is that
whilethe feature sounds important, these situations don't actually show up that often. There are exactly zero clients I
dealwith regularly who would be helped out by this. The ones running web applications whose workloads do fit into
memoryare more concerned about supporting large numbers of users, not optimizing things for a single one. And the ones
whohave so much data that single users running large reports would seemingly benefit from this are usually disk-bound
instead.
>
> The same sort of situation exists with SSDs. Take out the potential users whose data can fit in RAM instead, take out
thosewho can't possibly get an SSD big enough to hold all their stuff anyway, and what's left in the middle is not very
manypeople. In a database context I still haven't found anything better to do with a SSD than to put mid-sized indexes
onthem, ones a bit too large for RAM but not so big that only regular hard drives can hold them. 
>
> I would rather strongly disagree with the suggestion that embracing either of these fancy but not really as
functionalas they appear at first approaches is critical to PostgreSQL's future. They're specialized techniques useful
toonly a limited number of people. 
>
> --
> Greg Smith   2ndQuadrant USgreg@2ndQuadrant.com    Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Supportwww.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance":http://www.2ndQuadrant.com/books
>

4 cores is cheap and popular now, 6 in a bit, 8 next year, 16/24 cores in 5 years.  You can do 16 cores now, but its a
bitexpensive.  I figure hundreds of cores will be expensive in 5 years, but possible, and available. 

Cpu's wont get faster, but HD's and SSD's will.  To have one database connection, which runs one query, run fast, it's
goingto need multi-core support. 

That's not to say we need "parallel query's".  Or we need multiple backends to work on one query.  We need one backend,
workingon one query, using mostly the same architecture, to just use more than one core. 

You'll notice I used _mostly_ and _just_, and have no knowledge of PG internals, so I fully expect to be wrong.

My point is, there must be levels of threading, yes?  If a backend has data to sort, has it collected, nothing locked,
whatwould it hurt to use multi-core sorting? 

-- OR --

Threading (and multicore), to me, always mean queues.  What if new type's of backend's were created that did "simple"
things,that normal backends could distribute work to, then go off and do other things, and come back to collect the
results.

I thought I read a paper someplace that said shared cache (L1/L2/etc) multicore cpu's would start getting really slow
at16/32 cores, and that message passing was the way forward past that.  If PG started aiming for 128 core support right
now,it should use some kinda message passing with queues thing, yes? 

-Andy

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: [HACKERS] Slow count(*) again...
Следующее
От: Greg Smith
Дата:
Сообщение: Re: [HACKERS] Slow count(*) again...