Solved SQL performance
#1
Hey Folks, 

This is a general inquiry for all you database user's.

I have just built a brand new server, specs below.

AMD 5600G
Gigabyte B550 mobo
32gb ram
500gb nvme "OS"
1tb nvme "DB, kodi"
I have 16, 16tb hdds for media storage. running through a SAS system.
I am running windows 10 pro, with mysql "5.7.36" running.

I have 3 pc's and 8 khadas vim3 pro android boxes. All are running 19.3.

This is the first time I put all the machines and all the media into sql. Now mind you the DB is working fine, but it is SLOW, well only Slow on the movie collection screen.

My collection is north of 10k movies, and just a handfull of shows. When I click into the tv shows, its instant, but if i click into the movies depending on the machine, HEDT 7-10 secs, Vim 3pro android 25-30 secs for the movies to finally populate. No issues playing or anything like that.

The network is all gigabyte even to the vim's, and the DB and OS are all on very fast NVME so the read time is minimal. I have the thumbnails, media sources, sources shared on the server, because the VIM's only have 32gb of onboard storage and all the media artwork is pushing 18gb which just bogs the vims down. I also did this so all the artwork is synced if I make any changes.

I have looked into maximizing performance on the DB, but nothing seems to be making a significant dent in those times. I did notice that when i select the movies, it is pulling data from the mechanical drives which was curious since I did not think it would until you select and or play said media.

Is this common for a DB system? when I had only say 500 titles input, it was lightning fast but got slower and slower as more and more drives got put in.

Ideas for making this smoother?

Thx all!
Reply
#2
(2022-07-03, 22:13)TemporalBurn Wrote: Hey Folks, 

This is a general inquiry for all you database user's.

I have just built a brand new server, specs below.

AMD 5600G
Gigabyte B550 mobo
32gb ram
500gb nvme "OS"
1tb nvme "DB, kodi"
I have 16, 16tb hdds for media storage. running through a SAS system.
I am running windows 10 pro, with mysql "5.7.36" running.

I have 3 pc's and 8 khadas vim3 pro android boxes. All are running 19.3.

This is the first time I put all the machines and all the media into sql. Now mind you the DB is working fine, but it is SLOW, well only Slow on the movie collection screen.

My collection is north of 10k movies, and just a handfull of shows. When I click into the tv shows, its instant, but if i click into the movies depending on the machine, HEDT 7-10 secs, Vim 3pro android 25-30 secs for the movies to finally populate. No issues playing or anything like that.

The network is all gigabyte even to the vim's, and the DB and OS are all on very fast NVME so the read time is minimal. I have the thumbnails, media sources, sources shared on the server, because the VIM's only have 32gb of onboard storage and all the media artwork is pushing 18gb which just bogs the vims down. I also did this so all the artwork is synced if I make any changes.

I have looked into maximizing performance on the DB, but nothing seems to be making a significant dent in those times. I did notice that when i select the movies, it is pulling data from the mechanical drives which was curious since I did not think it would until you select and or play said media.

Is this common for a DB system? when I had only say 500 titles input, it was lightning fast but got slower and slower as more and more drives got put in.

Ideas for making this smoother?

Thx all!

I can't speak directly to your SQL approach performance but there are other sharing approaches you might want to consider.  I have north of 17K videos , 10K audio files and over 100K pieces of artwork with 300TB+ of storage.  Here's a post of my setup and library browsing is lightning fast.  If you are running a dedicated Windows server with many Kodi or non-Kodi clients the solution I use is ideal.  The solution I use has statistics so you can see how fast playlists populate and the speed of browsing your library.  Here's a link to give you some examples.  If you want to try this solution you can run it in parallel to test.  Here's the support thread for the Kodi addon which is a key piece of the solution for Kodi clients.

Just something to consider.  Happy to answer questions.

Jeff
Running Kodi with the Mezzmo Kodi addon.  The easy way to share your media. | Service.autostop addon author
Reply
#3
Jeff, thx for the reply. Sent you a PM.
Reply
#4
Thread marked solved.
Got a Kodi problem? Provide a full Debug log (wiki) | Usefull pages: First time user (wiki) | Troubleshooting (wiki) | Free content (wiki) | Forum rules (wiki) | VPN policy (wiki)
Reply



Logout Mark Read Team Forum Stats Members Help
SQL performance0
This forum uses Lukasz Tkacz MyBB addons.