Search Postgresql Archives

RFC : best way to distrubute IO from queries (low end server)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Final specs for the server is just an ordinary desktop fitted w/ 3
7200rpm 500GB drives & 1 7200 80GB drive / 1 GB ram / 2G processor
(single core)

number of records will be between 3 to 30 million rows.

Currently the process is 

1. pull from mssql
2. \copy into PG temp table
3. insert into final table.

current tables are distributed via tablespaces. (current test server is
my laptop w/ 2 5400rpm drives hda & hdc.)  and I'm already seeing the
strain of the concurrent select/delete/insert/update and the additional
"client" pull.

So, I wanted to ask the list for advice on how to tread w/ regard to the
server.

Scenario 1.

1. temp table in it's own tablespace
2. final tables in it's own tablespace
3. pgxlog in the OS tablespace

scenario 2
1. temp table in it's own tablespace
2. final tables in it's own tablespace (Read Only Copy)
3. final tables in it's own tablespace (Read write Copy)
4. pgxlog in the OS tablespace

the idea of read and read/write copy is obtained from some
presentation/article I read whereby, all updates of new data is inserted
into the read/write copy up until 1 point (say lunch time / 5pm etc)
whereby the read-write copy[3] will be "renamed" and made into a
read-only copy and the previous  read-only copy[2] will be made into a
read-write copy.

The only thing I can't wrap my head around is how to keep these 2 copies
in sync. eg: when everything is being updated to [3] and users are
querying [2] and then at the switch over instance, how will the new data
be inputted to the read only copy? because if I switch back and forth
like this, there's bound to be gaps in the data.

Any comments appreciated.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux