Daminion Forums: Daminion Server Performance - Daminion Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

User is offline Exterminador 

  • Bronze Member
  • PipPipPipPipPipPipPip
  • Group: Members
  • Posts: 164
  • Joined: 23-July 11

Posted 05 June 2013 - 09:04 PM (#1)

Daminion Server Performance


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
2


User is offline Murat Korkmazov 

  • Daminion Team
  • Group: Administrators
  • Posts: 3961
  • Joined: 03-May 11

Posted 06 June 2013 - 02:18 PM (#2)

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.
Daminion Team
Twitter | LinkedIn | Our Blog


User is offline Russell 

  • Bronze Member
  • PipPipPipPipPipPipPip
  • Group: Members
  • Posts: 168
  • Joined: 10-November 12

Posted 15 August 2015 - 02:51 AM (#3)

Murat,

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

Thanks,

Russell
0


User is offline Murat Korkmazov 

  • Daminion Team
  • Group: Administrators
  • Posts: 3961
  • Joined: 03-May 11

Posted 17 August 2015 - 02:18 PM (#4)

PostgreSQL settings that were suggested by Exterminador are applied when installing the current Daminion version
Daminion Team
Twitter | LinkedIn | Our Blog


Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users