I think it would help immensely if you provided details such as table_structure, indexes the actual UPDATE query and the reason all rows of the table must be updated.
On Mon, Jun 29, 2015 at 1:15 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, Jun 29, 2015 at 6:02 AM, ben.play <benjamin.co...@playrion.com> > wrote: > >> Hi guys, >> >> We have a PG database with more than 400 GB of data. >> At this moment, a cron runs each ten minutes and updates about 10 000 >> lines >> with complex algorithms in PHP. >> >> Each time the cron runs, the website is almost down because some queries >> have to make an update on the FULL table... >> > > Why is it updating the full table of 400GB if it only changes 10,000 lines? > > If most of the rows are being updated degenerately (they update the column > to have the same value it already has) then just add a where clause to > filter out those degenerate updates, unless the degenerate update is needed > for locking purposes, which is rare. > > > Therefore, I'm asking if it's possible to duplicate my main database on a >> slave server in order to run these cron on this second server... then, >> replicate these changes on the main database (master). >> > > Sounds like you are trying to use a bulldozer to change a lightbulb. > > Improving queries (including the effect running some queries has on the > entire system) starts with "EXPLAIN (ANALYZE, BUFFERS)", not with > multimaster replication. > > Cheers, > > Jeff > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
