Author Topic: mysql performance  (Read 593 times)

Esperanto

  • Guru
  • ****
  • Posts: 262
    • View Profile
mysql performance
« on: January 19, 2013, 09:08:18 am »
When I select Video or Audio in an orbiter it takes very long for the results to show (more then 10 seconds) and then scrolling through the results is also very slow. Is this normal behavior, am I missing some optimizations, should one manually create indexes or is there something else I can do to improve this?

thx!

posde

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 3045
  • Wastes Life On LinuxMCE Since 2007
    • View Profile
    • My Home
Re: mysql performance
« Reply #1 on: January 19, 2013, 11:02:34 am »
What Orbiter are you using?

And no it is not normal. Most of the time it happens when a) you have a slow hdd, or b) you have a very large media lib.

It used to be normal (2-3 years ago), but changes have been made to speed up the query (around 4-5 fold at least). These days the first display of the list should not take more than 3-5 seconds on a 15k entry media lib. The scrolling from one page to the next should not take more than a second or two (max).

Esperanto

  • Guru
  • ****
  • Posts: 262
    • View Profile
Re: mysql performance
« Reply #2 on: January 19, 2013, 03:18:12 pm »
I run on a hybrid with ui2 (no transparency) on an old amd be-2400 with a SSD and a NAS. I don't think my library is that big. How to check that. Doing a count on the file table gave me:
Code: [Select]
mysql> select count(*) from File;
+----------+
| count(*) |
+----------+
|   296213 |
+----------+


So if that is 296K compared you the 15K you mentioned then it is big. Not much I can do?

I assume those changes made to speed things up would be included automatically when running 'apt-get upgrade' or are manual actions required?

thx!

posde

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 3045
  • Wastes Life On LinuxMCE Since 2007
    • View Profile
    • My Home
Re: mysql performance
« Reply #3 on: January 19, 2013, 03:23:40 pm »
Do you have 200k+ files? If yes, than you lib is really taxing it.

Esperanto

  • Guru
  • ****
  • Posts: 262
    • View Profile
Re: mysql performance
« Reply #4 on: January 19, 2013, 03:32:39 pm »
If what I did is the correct way to count it.

I have however moved a lot of files manually and some on multiple locations. Could it be that LinuxMCE is still storing old locations and that way have duplicate entries? If so what would be the best way to get rid of the stale entries?