Running with Ruby

Tag: PostgreSQL

Synology DSM 6.2 Photo Station 6 – Failed to Load Data Fix

I’m a quite happy Synology user. For the past years I’ve been using it mostly to backup my things, so I didn’t pay much of an attention to the fact, that the Photo Station software would get slower and slower up to the point when I would end up with “Failed to load data” message each time I would access it.

Some articles suggested that you have to drop and re-create the media indexing database to fix it. However, this won’t help you in the long run. Your Photo Station 6 database will become bloated once again after a while.

The reason, why Photo Station 6 gets slower and slower, is the fact that Synology, for any crazy reason disabled the PostgreSQL AutoVacuum functionality. Vacuuming is suppose to keep your database in a good state

How to fix that once and for all? You need to enable the PostgreSQL AUTOVACUUM and for an immediate effect, you should also run the vacuuming manually.

SSH into your server and then:

sudo su
cd /volume1/@database/pgsql
vim postgresql.conf

Within the postgresql.conf file replace (or add if they don’t not exist) following settings:

wal_buffers =128MB
autovacuum = on
checkpoint_segments = 10

save, exit the file and reboot.

If you want to run vacuuming manually, log in into the PostgreSQL console:

psql -U postgres

List available databases:

postgres=# \l
                                       List of databases
    Name     |           Owner            | Encoding  | Collate | Ctype |   Access privileges   
-------------+----------------------------+-----------+---------+-------+-----------------------
 mediaserver | MediaIndex                 | SQL_ASCII | C       | C     | 
 ong         | SynologyApplicationService | SQL_ASCII | C       | C     | 
 photo       | PhotoStation               | SQL_ASCII | C       | C     | 
 postgres    | postgres                   | SQL_ASCII | C       | C     | 
 synosnmp    | postgres                   | SQL_ASCII | C       | C     | 
 template0   | postgres                   | SQL_ASCII | C       | C     | =c/postgres          +
             |                            |           |         |       | postgres=CTc/postgres
 template1   | postgres                   | SQL_ASCII | C       | C     | postgres=CTc/postgres+
             |                            |           |         |       | =c/postgres

Connect to the photo DB:

postgres=# \c photo;
You are now connected to database "photo" as user "postgres".

Check the tables size by running the following query:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 5;
       relation       | total_size 
----------------------+------------
 public.photo_image   | 1097 MB
 public.photo_log     | 5912 kB
 public.video_convert | 936 kB
 public.photo_share   | 928 kB
 public.video         | 864 kB
(5 rows)

and the potential vacuuming candidate should be obvious by now:

photo=# vacuum full public.photo_image;
VACUUM

Re-run the size checking query again just to see 93% size reduction of the images table:

       relation       | total_size 
----------------------+------------
 public.photo_image   | 72 MB
 public.photo_log     | 5912 kB
 public.video_convert | 936 kB
 public.photo_share   | 928 kB
 public.video         | 864 kB
(5 rows)

After that, everything will work blazing fast!

Speeding up RSpec and Cucumber on the CI server with PostgreSQL fsync flag and parallel execution

After we’ve added RSpec and Cucumber (with PhantomJS) to our CI build process, it got really, really slow. Due to the application character, after each scenario (for Cucumber) we truncate and restore the whole database. 45 minutes for a single build is definitely not what we aimed to get. So, how to speed up tests execution?

First we thought, that we could run RSpec and Cucumber stuff in parallel (using parallel tests gem). We’ve got a much better machine on AWS to make sure that a single process has a single core to use. Unfortunately everything got… slower. We’ve decided to pinpoint a single RSpec spec and a single Cucumber scenario that would be representative and figure out what the hell. What we’ve discovered at the beginning, is that all the specs were running faster on the Ruby level. It all got significantly slower because of the database. Our tests were heavy in terms of DB communication and as I said before, due to it’s character, it will probably stay that way.

So, what were our options?

  • We could get a much better hardware for our testing DBs. Bigger, faster, with SSD, however it would definitely make things more expensive
  • We could compromise data consistency. Since it is a testing cluster – in case of a system failure / crash /shutdown, we can just drop all the databases and repopulate them again

We’ve decided to try out the second approach and use fsync PostgreSQL flag to tweak this database a little bit.

What is fsync (quote from PostgreSQL documentation)?

If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

While turning off fsync is often a performance benefit, this can result in unrecoverable data corruption in the event of a power failure or system crash. Thus it is only advisable to turn off fsync if you can easily recreate your entire database from external data.

Examples of safe circumstances for turning off fsync include the initial loading of a new database cluster from a backup file, using a database cluster for processing a batch of data after which the database will be thrown away and recreated, or for a read-only database clone which gets recreated frequently and is not used for failover. High quality hardware alone is not a sufficient justification for turning off fsync.

Results were astonishing! Since we’re no longer as much dependent on our HDDs performance for each operation, the database layer does not slow us down that much.

Zrzut ekranu z 2016-03-31 12:16:09

Zrzut ekranu z 2016-03-31 12:44:35

Zrzut ekranu z 2016-03-31 12:20:48

Overall thanks to this tweak and parallel execution, we’ve managed to get down from 45 minutes for a whole build, down to 12 minutes. That is 75% faster than before and this build time is acceptable for us.

Research done by: Adam Gwozdowski

Copyright © 2019 Running with Ruby

Theme by Anders NorenUp ↑