Daminion Server Performance

[exterminador]

Hi,

 

I wanted to share some findings about how to improve Daminion's performance (when using Daminion Server).

 

I was getting really slow response times when opening Daminion and with thumbnail displaying. At first I thought it was because of the ammount of files I have imported to Daminion (more than 800K).

 

I found on the PosgtreSQL logs some queries that were taking a lot of time to process (pretty much every query). And did some testing and research about how to tweak PostgreSQL in order to improve performance.

 

Hoping it helps, I wanted to share my results, for testing I used the pgAdmin tool that comes with PostgreSQL, the query I tested with:

 

select distinct m.ID, m.Name, m.MediaFormatUi, m.Deleted, m.HashCode, m.ID_ImportSession, m.ID_File, b_a01.CreationDatetime, m.ID_User as ID_User, m.ID_SystemFlag as ID_SystemFlag, m.ID_ColorLabel as ID_ColorLabel, m.ID_Rating as ID_Rating, image.ImageOrientation as ImageOrientation
from MediaItems m 
	left join subject b_a01 on m.ID = b_a01.ID_MediaItem
	left join image image on m.ID = image.ID_MediaItem
where not m.Deleted
 and 
	exists (select 1 from Folder_File where ID_Value = ((1635)) and m.ID = ID_MediaItem)    
order by b_a01.CreationDatetime Desc

 

1st time run (168578 ms)

2nd time run (33908 ms)

3rd time run (164535 ms)

After reindexing the database (199046 ms)

After reindexing the database (184532 ms)

After vacuum (a Postgre command for reducing physical space used by the database) (340882 ms)

After moving the postgre database files to a different drive (from a SSD drive to a 5400 RPM hard drive):

1st run (41690 ms)

2nd run (28904 ms)

3nd run (30050 ms)

NOTE: Yes, this results impressed me as well :smile: , the SSD drive is supposed to be a LOT faster than a 5400 RPM drive, but SSD drive's performance gets degraded over the time, mine is two years old, so probably is not so fast now.

 

The results were better, but 30 seconds for a query that is run along with other 30 queries (for example when opening Daminion) wasn't getting any better...

 

So, what actually worked (and this was actually a pretty easy one), was to change the following parameters in the postgresql.conf file:

 

shared_buffers = 512MB (default is 32MB)

work_mem = 128MB (default is 1MB)

effective_cache_size = 1280MB (default is 128MB)

 

After changing this settings, the same query was done in 979 ms, less than a second. That is something reasonable, I guess it can still be improved with more tweaking.

Probably not everyone is going to need that higher values, it mostly depends on the size of your database as it is directly proportional to the size of the indexes, and the number of users you will have on your database (for this is just me :))

 

And also, with this results, thumbnail display is faster as well (I will test again by moving the thumbnails to a different drive). I guess it is because Daminion is not busy waiting for a number of queries to return its results.

 

 

Hope it helps!

 

Added by admin:

Postgres *.conf files are located in the PostgreSQL database (data) directory. You can find this location via the Admin Panel

[Murat]

Much appreciated your tests and sharing the results, and the advice about improving the performance of the PostgreSQL. We didn't begin optimizing of Daminion's performance yet, but we'll taken into account your postgresql.conf adjustments.

[Russell]

Murat,

 

Is it optimized now, or do these suggestions from Exterminador still apply?

 

Thanks,

 

Russell

[Murat]

PostgreSQL settings that were suggested by Exterminador are applied when installing the current Daminion version

[WilfriedB]

shared_buffers = 512MB (default is 32MB)

work_mem = 128MB (default is 1MB)

effective_cache_size = 1280MB (default is 128MB)

I wonder whether the above the recommendations are still valid today (five years after the above post). I doubled each of the three values and have the feeling- no serious measurement though - some response times did improve with Daminion 6.0 (1819).

 

Any other experiences?