Jump to content


Photo

MB3\Mysql fast for albums and movies, slow for artists and persons

mysql performance music

  • Please log in to reply
3 replies to this topic

#1 ginjaninja OFFLINE  

ginjaninja

    Advanced Member

  • Members
  • 1646 posts
  • Local time: 09:00 AM
  • Locationuk

Posted 06 July 2014 - 07:24 AM

Version 3.0.5289.18702

there seems to be a significant performance bottleneck with persons and artists views in the dashboard, and its not related to cpu or disk subsystem performance.

perhaps the database is the constraint along with the query.

(roku and android clients dont seem affected but then their views have more spurious entries)

 

all my content is stored locally on 5400rpm hdds

my.application /ibn is stored on SSD (across 2 partitions of same ssd)

all my views are 500 large (500 for movies, albums, persons and artists)

q6600, 4x3.2ghzcpu

6Gb ram

win7sp1

 

if i click (in dashboard) (views configured 500 large)

home - movies - movies....the 1st 500 movies are shown <1second

home - movies - persons....it take 20seconds to show persons..

likewise

albums <1s

artist or album artists >20 seconds.

 

It is 100% reproducible for me and make no difference how many times the query is run (ie same after all images are cached etc

the cpu and hdds are not breaking a sweat when viewed in perfmon.

 

2014-07-06 11:45:56.0530 Debug - HttpServer: HTTP GET http://localhost:809...12f4447c75b0cf2
2014-07-06 11:46:18.9273 Debug - HttpServer: HTTP Response 200 to [::1]:55920. Response time: 22874.3084 ms
 
 
FWIW the time for artists query grows somewhat  linearly as the the number of objects increases, but even with 20 objects it is slow
2014-07-06 12:11:55.0781 Debug - HttpServer: HTTP GET http://localhost:809...12f4447c75b0cf2
2014-07-06 12:11:56.3852 Debug - HttpServer: HTTP Response 200 to [::1]:56748. Response time: 1307.0747 ms
 
 
400 albums <.2 sec
2014-07-06 12:15:55.5739 Debug - HttpServer: HTTP GET http://localhost:809...779f3&Limit=400
2014-07-06 12:15:55.7699 Debug - HttpServer: HTTP Response 200 to [::1]:56753. Response time: 196.0112 ms
 
whole log

Edited by ginjaninja, 06 July 2014 - 07:27 AM.


#2 ebr OFFLINE  

ebr

    Chief Bottle Washer

  • Administrators
  • 48092 posts
  • Local time: 04:00 AM

Posted 06 July 2014 - 03:32 PM

A while back, we made a pretty big sacrifice in performance accessing people in order to get our memory footprint down.  I'm not sure we've had time to go back and see if there is any fine-tuning that can be done to bring some of the performance back but people are a very large set of distributed data to try and retrieve against right now.



#3 ginjaninja OFFLINE  

ginjaninja

    Advanced Member

  • Members
  • 1646 posts
  • Local time: 09:00 AM
  • Locationuk

Posted 07 July 2014 - 03:52 AM

thanks for feedback ebr.

 

is it odd (does it concur with your root cause analysis) that

500 episodes of 24,000 returns quickly...500 people of 23000 returns slowly.

500 songs of 60,000 returns quickly...500 album artists of 650 returns slowly.

 

I also notice viewing 200 album artists under reports interface is significantly quicker (by a factor of 100) than 200 album artists under music.

 

I also notice that the two interfaces/queries do not return the same data set...the reports query returns the 'old' dataset with non album artists, eg. soundtracks and other organisational folders which are not album artists.

might this explain why roku and android clients are not slow at showing album artists, they use the old query.



#4 ebr OFFLINE  

ebr

    Chief Bottle Washer

  • Administrators
  • 48092 posts
  • Local time: 04:00 AM

Posted 07 July 2014 - 11:31 AM

Yes, that makes some sense because we hold the entire library structure in memory.  So actual items like albums or movies are very quick to find.  Attribute-based groupings like artists or actors require a bunch of other operations now.  But, when time permits, I'm confident Luke can fine-tune it.







Also tagged with one or more of these keywords: mysql, performance, music

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users