PDA

View Full Version : Getting better performance out of MySQL



Raptus
2007-02-03, 17:04
Hello there,

as in the default configuration the DB wasn't handling my 80K+ songs well and noticing much disk activity during queries I looked into how to increase buffering.

The settings below (to be found in my.cnf) really made SlimServer fly. Changed variables:

key_buffer_size = 32M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M

Added variables:
innodb_buffer_pool_size=64M

Probably some of those are overkill, so I'm going to test the impact of each one to determine a more efficient value set. Right now I'm under the impression that the innodb buffer makes for the biggest performance gain by far (and task manager shows the memory really being used).

Suggestions are welcome :)

PS.: you need to restart the mysqld.exe for changes to take effect.

egd
2007-02-03, 19:39
I made some of the mods you did, albeit not as extreme, but I did add innodb_buffer_pool_size=64M. My collection is about half the size of yours (properly tagged though :P) and I've never had performance issues under Linux, but Windows is an altogether different story. The tweaks definitely sped up the Windows based slimserver, even on a small local total rescan of 860 tracks.

I'm in the process of backing up my NAS to a 2.25TB hardware raid so I've left the Linux instance untouched for the moment (the ReadyNAS NV doesn't exactly come with a lot of grunt). When that's done I'll rescan the NAS from Windows and Linux and report the results if you're interested.

cparker
2007-02-04, 03:30
Whenever I make the changes and save them, if I restart Slimserver it overwrites them again!??

How can I stop it doing this?

My method of approach is;
Download the GUI admin tool from here;
http://dev.mysql.com/downloads/gui-tools/5.0.html

Connect to; 127.0.0.1 Port 9092

Slimserver Service is stopped and SlimserverMySQL is running

Make the changes to the startup parameters

Save them, but then restart Slimserver service and they get overwritten again back to the old values!

Any ideas?

Raptus
2007-02-04, 03:33
Strange. I also used the MySQL Administrator and it worked just fine.

erland
2007-02-04, 03:33
Whenever I make the changes and save them, if I restart Slimserver it overwrites them again!??

How can I stop it doing this?You need to make the changes in:
MySQL/my.tt

cparker
2007-02-04, 03:44
You need to make the changes in:
MySQL/my.tt

Yep working now, excellent thanks! :)

Raptus
2007-02-04, 03:48
From http://forums.slimdevices.com/showpost.php?p=130572&postcount=2 I got that my.tt is a template for my.cnf, so I take my.cnf is regenerated each time you start SlimServer. Correct?

cparker
2007-02-04, 03:55
From http://forums.slimdevices.com/showpost.php?p=130572&postcount=2 I got that my.tt is a template for my.cnf, so I take my.cnf is regenerated each time you start SlimServer. Correct?

err...

Well it would appear that my.tt overwrites the my.cnf
my.cnf is here
C:\Program Files\SlimServer\server\Cache
my.tt is here
C:\Program Files\SlimServer\server\MySQL

So I would also assume that indeed the my.tt overwrites the my.cnf, maybe just on the Windows version??

Cheers

ModelCitizen
2007-02-05, 01:08
I'm very interested in optimising MySQL for Windows use and would very much appreciate it if anyone who does any testing with the settings would report back here, stating their library size and the settings they found worked best for them. My library consist of *only* 18,000 (flac) songs but I've occasionally wondered if the performace of MySQL on Windows might be improved and if the SlimDevices developers may have overlooked that fact that the optimum MySQL settings for Windows and **nix may vary, especially where large music libraries are concerned.
MC

peter
2007-02-05, 01:47
ModelCitizen wrote:
> I'm very interested in optimising MySQL for Windows use and would very
> much appreciate it if anyone who does any testing with the settings
> would report back here, stating their library size and the settings
> they found worked best for them. My library consist of *only* 18,000
> (flac) songs but I've occasionally wondered if the performace of MySQL
> on Windows might be improved and if the SlimDevices developers may have
> overlooked that fact that the optimum MySQL settings for Windows and
> **nix may vary, especially where large music libraries are concerned.
>


Just so you know, 18.000 records is *nothing* for MySQL. If this is a
problem with tuning somebody's done something very wrong indeed. I
figure the cause of the slowness you complain about must lie somewhere
else in the code. It's not Perl in itself either because I routinely use
Perl scripts with MySQL that manage much larger datasets and it's all
lightning fast (unless you make some silly mistake with indexes or
something).

This problem must be caused by something else in the programming.

Regards,
Peter

Raptus
2007-02-05, 02:36
Just so you know, 18.000 records is *nothing* for MySQL.
Yes that is true. As long as you don't memory starve it. Otherwise you'll put the HD speed in the equation...

My DB data file is about 240MB, yet SlimServer comes with MySQL configured to the default innodb_buffer_pool_size, an that is 16MB. Guess what happens...

From the MySQL reference manual:

innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.
So setting it to 64MB seems reasonable for larger collections on a machine with 1GB RAM.

Regards.

peter
2007-02-05, 03:29
Raptus wrote:
> Peter;177773 Wrote:
>
>> Just so you know, 18.000 records is *nothing* for MySQL.
>>
> Yes that is true. As long as you don't memory starve it. Otherwise
> you'll put the HD speed in the equation...
>
> My DB data file is about 240MB, yet SlimServer comes with MySQL
> configured to the default innodb_buffer_pool_size, an that is 16MB.
> Guess what happens...
>
> From the MySQL reference manual:
>
>> innodb_buffer_pool_size
>>
>> The size in bytes of the memory buffer InnoDB uses to cache data and
>> indexes of its tables. The larger you set this value, the less disk I/O
>> is needed to access data in tables. On a dedicated database server, you
>> may set this to up to 80% of the machine physical memory size. However,
>> do not set it too large because competition for physical memory might
>> cause paging in the operating system.
>>
> So setting it to 64MB seems reasonable for larger collections on a
> machine with 1GB RAM.
>

It's debatable if slimserver should be using InnoDb tables at all. AFAIK
MyIsam is faster because it has less overhead. Yes, InnoDb is better for
transactions and maintaining db integrity, but who needs that on a music
server? It' s not that hard to do a full rescan.

I still have a hard time believing that this is a MySQL issue, even
InnoDb should be plenty fast enough.

I just turned on SQL debug messages and added a 'Music Folder' folder
'Classical' to an empty playlist. I see slimserver doing a SELECT
command for every file in that folder (1500 in total):

SELECT [stuff] FROM tracks me WHERE ( url = ? ):
'file:///peter/shared/music/Classical/Beethoven%20-%20Piano%20Sonatas%20-%20Claudio%20Arrau/Beethoven%20-%20Piano%20Sonatas%20-%20Disc%2003/10%20Sonata%2008%20in%20C%20Op.13%20-%20%27Pathetique%27%20-%20III%20-%203-Rondo%20allegro.mp3'

This is a very inefficient of course. You would expect it to do a single
wildcard select in this case:

SELECT [stuff] FROM tracks me WHERE ( url like? ):
'file:///peter/shared/music/Classical/%'

It's not surprising at all that this is a slow operation.

Then slimserver does 1500 single inserts, like this:

INSERT INTO playlist_track (playlist, position, track) VALUES (?, ?, ?):
'351', '354', '8399'

Surely there's a more efficient way thinkable

Check out this link for multiple inserts:
http://www.petefreitag.com/item/379.cfm

Or use an INSERT...SELECT to pump the tracks into the playlist directly
(or via a intermediate temporary table):

http://mysqld.active-venture.com/INSERT_SELECT.html

This is what happens when I add this dir (--d_playlist):

2007-02-05 11:18:40.8503 scanPathOrURL: Finding valid files in:
/peter/shared/music/Classical
2007-02-05 11:18:40.8552 About to look for files in
/peter/shared/music/Classical
2007-02-05 11:18:40.8595 For files with extensions in:
[(?i-xsm:\.(?:ape|wma|asf|wax|asx|lnk|wpl|shn|wav|wave| mp3|mp2|m3u|pls|xspf|flc|flac|m4a|mov|m4b|mp4|cue| mp+|mpc|ogg|aif|aiff)$)]
2007-02-05 11:18:41.3799 Found 1498 files in /peter/shared/music/Classical

2007-02-05 11:19:31.4622 Reshuffling, current song index: -1, preserve
song? no
2007-02-05 11:19:31.5587 Playlist: Jumping to song index: 0
2007-02-05 11:19:31.5593 new playlistindex: 0

Adding 1500 files takes 50 seconds. This is very very slow, and I'm
totally convinced this could be speeded up by at least an order of
magnitude by simple optimization in the way slimserver handles the
database. Basically, the SQL engine should be allowed to do more of the
work.

Regards,
Peter

mherger
2007-02-05, 03:46
> Adding 1500 files takes 50 seconds. This is very very slow, and I'm
> totally convinced this could be speeded up by at least an order of
> magnitude by simple optimization in the way slimserver handles the
> database. Basically, the SQL engine should be allowed to do more of the
> work.

Adding/scanning files is much more than just inserting a few records:
folders are scanned (disk i/o), file types are checked, tags verfied
and/or guessed etc. I doubt MySQL could do that.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

peter
2007-02-05, 03:57
Michael Herger wrote:
>> Adding 1500 files takes 50 seconds. This is very very slow, and I'm
>> totally convinced this could be speeded up by at least an order of
>> magnitude by simple optimization in the way slimserver handles the
>> database. Basically, the SQL engine should be allowed to do more of the
>> work.
>>
>
> Adding/scanning files is much more than just inserting a few records:
> folders are scanned (disk i/o), file types are checked, tags verfied
> and/or guessed etc. I doubt MySQL could do that.
>

No, it couldn't. But surely this kind of operation should only be done
while adding diskfiles to the track database? Not while adding (already
indexed) tracks to a playlist? Or is that the real problem here? If I
want to 'play' my 'Classical' folder tree, does slimserver do a full
rescan for that tree? I don't think it should be doing that, because it
makes creating a playlist very slow if you use the 'Music Folder' way of
browsing. I understand from this forum that I'm not the only one who
uses slimserver this way.

Regards,
Peter

mherger
2007-02-05, 04:35
> Not while adding (already indexed) tracks to a playlist?

Oops... didn't read that log file that well :-/.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

bergek
2007-02-05, 04:40
Then slimserver does 1500 single inserts, like this:

INSERT INTO playlist_track (playlist, position, track) VALUES (?, ?, ?):
'351', '354', '8399'

Surely there's a more efficient way thinkable

Check out this link for multiple inserts:
http://www.petefreitag.com/item/379.cfm


Multiple inserts to InnoDB tables on an out-of-the-box MySQL Server is very bad. The default setting for MySQL is to auto-commit after every insert. Typically you might see a 100:1 performance drop compared to MyISAM tables.

Also, the suggestion by Peter to do multi-inserts is correct and leads to a linear increase in performance up to at least one thousand records.

peter
2007-02-05, 04:53
bergek wrote:
> Peter;177790 Wrote:
>
>> Then slimserver does 1500 single inserts, like this:
>>
>> INSERT INTO playlist_track (playlist, position, track) VALUES (?, ?,
>> ?):
>> '351', '354', '8399'
>>
>> Surely there's a more efficient way thinkable
>>
>> Check out this link for multiple inserts:
>> http://www.petefreitag.com/item/379.cfm
>>
>>
>
> Multiple inserts to InnoDB tables on an out-of-the-box MySQL Server is
> very bad. The default setting for MySQL is to auto-commit after every
> insert. Typically you might see a 100:1 performance drop compared to
> MyISAM tables.
>
I think they figured that out (from scanner.pl):

# Take the db out of autocommit mode - this makes for a much
faster scan.
Slim::Schema->storage->dbh->{'AutoCommit'} = 0;

> Also, the suggestion by Peter to do multi-inserts is correct and leads
> to a linear increase in performance up to at least one thousand
> records.
>
>

Sounds good. :)

Regards,
Peter

peter
2007-02-05, 04:54
Michael Herger wrote:
>> Not while adding (already indexed) tracks to a playlist?
>>
>
> Oops... didn't read that log file that well :-/.
>

I think you put me on the right track though, that's probably what's
happening, it's doing a full scan of the subtree and only then adds them
to the playlist. It's a nice feature to be able to add files to a folder
and then playing them by clicking on the folder name under 'Music
Folder', but this could work a lot more efficiently if it just looked
for new files. ( a 'find Classical/ -type f' takes 0.2 seconds on my
system.

So this is what should happen IMHO:

- Read the existing tracks under Classical/ from the tracks db
(SELECT..FROM tracks where url like Classical/%' )
- Read the folder tree contents recursively (should be possible in under
a second in my case)
- Check to see if there are any new files that should be added to the
tracks table.
- Add the new files to the tracks table (churn, churn) and delete files
that have disappeared from the tracks table.
- Generate the playlist.

The files on the disk don't change much, so this should speed up
playlist generation hugely in 99% of all cases by eliminating the churning.

Regards,
Peter

bgriffis
2007-02-06, 06:55
This is a great thread. I've been having SlimServer performance issues on Windows for quite some time now. I'm going to try out making those changes to the amount of RAM it uses. I think it would be even better if SlimServer would optimize the commands that it uses for the MySQL to be more efficient. Has anyone file a bug report (enhancement request) on this? I'd love to see this get into the nightlies ASAP!

Brad

bgriffis
2007-02-06, 07:12
FYI, I made the changes to my.tt and restarted slimserver. Unfortunately that hasn't solved my problems. The web interface seems to choke if I add say a dozen albums to it and then try to navigate. For example I tried clicking on page 2 of 3 and the web interface is pretty much completely stalled now. :(

It seems like right now I can only queue up a couple albums without making things choke. Also, the web interface is painfully slow and I end up using the player instead. I'm really getting sick of this damn thing. I've been thinking about getting a second PC and running Linux. Maybe I'd have better results there.

Anyway, thanks for the discussion on this. I hope the optimizations you suggested for MySQL work their way into SlimServer.

Brad

egd
2007-02-06, 22:13
I'm really getting sick of this damn thing. I've been thinking about getting a second PC and running Linux. Maybe I'd have better results there.


Absolutely. slimserver is an absolute dog for me on two separate w2k3 boxes, one of which is a fresh base install with not even an AV or firewall in its way. So slow in fact, it is almost unusable. Slimserver running on the same boxes under Linux and looking at the same library located on a NAS is incredibly quick by comparison. I posted earlier on rescan times in Linux vs Windows and the differences are vast - in some cases I can have Linux reperform the rescan around 10x in the same time it takes the windows instance to complete a single rescan.

Have you considered dual-booting rather than getting another PC?

ModelCitizen
2007-02-07, 01:25
Have you considered dual-booting rather than getting another PC?
Wouldn't that make it impossible to use the Squeezebox whilst using Windows?
MC

tommypeters
2007-02-07, 05:02
Absolutely. slimserver is an absolute dog for me on two separate w2k3 boxes, one of which is a fresh base install with not even an AV or firewall in its way. So slow in fact, it is almost unusable. Slimserver running on the same boxes under Linux and looking at the same library located on a NAS is incredibly quick by comparison. I posted earlier on rescan times in Linux vs Windows and the differences are vast - in some cases I can have Linux reperform the rescan around 10x in the same time it takes the windows instance to complete a single rescan.
Any suggestions for a Linux distribution? I'm setting up a box with two primary uses: Slimserver & "Internet PC" for my wife. So she basically needs a browser.

I worked with Unix for many years, but it was a while ago - so it's nothing wrong with a distribution that's easy to install and maintain. But also flexible and possible to configure - I thought about FreeNAS with SlimNAS, but I think FreeNAS is a bit too rigid...

egd
2007-02-07, 05:03
Wouldn't that make it impossible to use the Squeezebox whilst using Windows?

I guess it would mean having to run Linux to use the SB3 without the performance issues. I can see why it might be a problem for someone who wants to retain a windows desktop for reasons other than slimserver. Dual boot may however be a good way to discover that transitioning to Linux is actually pretty easy.

peter
2007-02-07, 05:18
egd wrote:
> ModelCitizen;178484 Wrote:
>
>> Wouldn't that make it impossible to use the Squeezebox whilst using
>> Windows?
>>
>
> I guess it would mean having to run Linux to use the SB3 without the
> performance issues. I can see why it might be a problem for someone
> who wants to retain a windows desktop for reasons other than
> slimserver. Dual boot may however be a good way to discover that
> transitioning to Linux is actually pretty easy.
>

There are performance issues in Linux as well. My example of 50 seconds
to start playing a subfolders containing 1500 tracks was measured on Linux.

If you want to play with Linux, try the bootable SlimCD first.

Regards,
Peter

tommypeters
2007-02-07, 07:02
Yes, good idea. It would also include the browser my wife need, and it can be installed on HDD if it works as expected.

Schindler
2007-02-08, 04:58
I am not able to add "innodb_buffer_pool_size=64M" to "my.tt". After restarting all, I see a wrong value if I use the admin-tool.

Does anybody can send me the my.tt file with "innodb_buffer_pool_size=64M"?

Thanks
Christian

Tarn
2007-03-03, 12:11
I just made the same changes to my.tt (in Windows under Program Files\SlimServer\Server\MySQL):

key_buffer_size = 32M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M

Added variables:
innodb_buffer_pool_size = 64M

I don't know how to run the MySQL admin tool so I don't know if the innodb_buffer_pool_size worked or not. However I did put spaces around the "=" having run into other parsers that would barf on an equal sign without spaces.

Results: Browsing by Genre, Artist, etc. is AT LEAST three times as fast. A number of actions (pulling up the 100 albums in folk genre) are at least 10x faster.

This is for a small (230 album, soon to be 400) flac collection (properly tagged) which really should have been easy for SlimServer to handle.

I had thought the speed issues I was seeing were related to using the browser control of Slim Server, but it looks like most of the problems were with MySQL and/or with how SlimServer is using MySQL.

Looks like at a minimum new versions of SlimServer should write out a different my.tt then they currently are, and the current SQL queries, joins, etc. should be examined for how expensive they are.

I assume somebody has entered a bug on this?

jfo
2007-03-03, 20:59
You need to make the changes in:
MySQL/my.tt

How does one do this?? I can't seem to find this in the admin tool/\.

Thanks,
Jim

ModelCitizen
2007-03-04, 01:18
It'd be very useful to have input from a SlimDevices developer regrading these changes being made to the MySQL settings. Erland... as the person who may well be closest to the SlimDevices developers (due to your wonderful plugin development) have you any idea which developer would be closest to the MySQL implementation?
I have a feeling it might be Andy.. but am not at all sure.

MC

Ron Olsen
2007-03-04, 02:46
How does one do this?? I can't seem to find this in the admin tool.

Go to your SlimServer installation folder, and edit MySQL/my.tt with your favorite text editor.

jfo
2007-03-04, 10:46
Thanks. I tried this before in Notepad and simply didn't scroll far enough to find the settings! Another simple question...I see a rectangular box symbol at the end of each setting statement. Is it necessary to add this if I include the innodb variable? If so, what is it??

Thanks. Jim

jfo
2007-03-04, 11:24
Thanks. I tried this before in Notepad and simply didn't scroll far enough to find the settings! Another simple question...I see a rectangular box symbol at the end of each setting statement. Is it necessary to add this if I include the innodb variable? If so, what is it??

Thanks. Jim

I think I have this figured out. I changed the Config Service path in the MySQL tool to my.tt from my.cnf and it is holding the changes. I didn't actually have a my.cnf file in the Cache directory...only .bak files which have the same info as the my.tt file. Is this normal? Everything seems to function OK.

Jim

Tarn
2007-03-04, 11:49
JFO, the my.tt file was written out with only carriage returns, not carriage return / line feed combinations as notepad requires. You can load it in WordPad which will handle that and write it out with the CR/LF combos so notepad can then open it.

wolo
2007-04-01, 15:27
I know this has been stated in other threads, but I wanted to mention it again:

If you are running an on-access virus scanner, try adding "*.MYD" and "*.MYI" to the exclusion list.

Adding "C:\Program Files\SlimServer\" and its sub-directories had no effect on my machine, so I just assumed I was dealing with poor MySQL performance. Then I decided to monitor my Virus Scan scanner while I hit Refresh on my New Music link. That's when I noticed the MYD and MYI files in C:\Windows\Temp\.

Excluding those extensions from my on-access scanner has resulted in a snappy SlimServer web interface! Life is much better now.

-Wolo

ceejay
2007-04-03, 02:21
I can't honestly say this is a major issue for me, its just a little irritating, and I'm left with the feeling that it could be better if only I could find the magic button to push.

I'm running on Windows XP Pro SP2, SS 6.5.1, 2.8GHz P4, 1GB RAM, internal disc. 727 albums with 8219 songs by 1510 artists.

I've tried some of the MySQL parameter tweaks mentioned earlier - they seem to have increased the amount of memory allocated, as expected, but no obvious impact on performance.

My Virus checker is Norton 2006: I switched it from scanning all files to "Smart scanning" (ie not including the .MY* types) and that made no visible difference.

The benchmark I'm using is how long it takes to respond to the "Browse Albums" click through the Web UI: typically 12 seconds, can be as much as 15. During this time CPU goes to 100%.

I'd certainly appreciate some authoritative comments on how the database might be tunable for better performance, if that really is the bottleneck.

TIA
Ceejay

mherger
2007-04-03, 03:59
> The benchmark I'm using is how long it takes to respond to the "Browse
> Albums" click through the Web UI: typically 12 seconds, can be as much
> as 15. During this time CPU goes to 100%.

IMHO the bottleneck here isn't the database, but the web part, template
parsing etc. No way to improve performance by tuning the database.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

ceejay
2007-04-03, 04:22
> The benchmark I'm using is how long it takes to respond to the "Browse
> Albums" click through the Web UI: typically 12 seconds, can be as much
> as 15. During this time CPU goes to 100%.

IMHO the bottleneck here isn't the database, but the web part, template
parsing etc. No way to improve performance by tuning the database.



Thanks - I had a feeling that might be the case (based on the observation that the player UI is generally a lot quicker).

Is there anything going on to change the web part to be faster?

Or is the best bet for slick PC performance going to be a CLI-based tool (Moose, Slimremote, ......) ?

Ceejay

mherger
2007-04-03, 06:27
> Is there anything going on to change the web part to be faster?

AFAICT the developers are aware of the issue.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

studley
2007-04-12, 20:09
is it key_buffer or key_buffer_size?

JJZolx
2007-04-13, 12:26
is it key_buffer or key_buffer_size?

It's key_buffer_size, but (I asked the question on a MySQL list) this option only affects the performance with MySAM tables. Since SlimServer uses InnoDB tables, this setting should have no affect on SlimServer performance.

Actually, unless you have a huge library, I don't expect any changes to made the MySQL parameters to have much affect on performance. The quantities of data, index sizes, etc. that SlimServer works with are quite small and I imagine that there's seldom more than two or three processes accessing the SlimServer database on most networks.

I'd be willing to bet that a _much_ larger gain in performance could be had by switching to MyISAM tables and maybe getting rid of all the text/blob fields. I've updated my database to get rid of the text and blobs, changing them to varchar, but it didn't make a noticeable difference. I tried changing the InnoDB tables to MyISAM, but it's possible for only a couple of tables - the rest will throw constraint violations when you try to alter the tables. It might be possible with an empty database, so I might try that next.

JJZolx
2007-05-06, 18:09
I'd be willing to bet that a _much_ larger gain in performance could be had by switching to MyISAM tables and maybe getting rid of all the text/blob fields.

I've since had a chance to try this theory out...

I'm running SlimServer 6.5.2 with purely MyISAM tables and it doesn't feel particularly faster. I haven't run any timing tests, but I'm gauging it on the speed of the browse Albums pages (with artist display enabled), as this is by far the most annoyingly slow part of the Slim interface for me. The SlimServer programming, however, where the generation of a single page can easily run to more than 500 queries, is hopelessly beyond any possible MySQL server optimization.

On a slower system, though, the differences using MyISAM might more pronounced. I don't have a way to test it, though.

The way I was able to use MyISAM tables was to simply edit the schema files that SlimServer uses, then I emptied the database of all tables and let SlimServer build it again from scratch.

I never really understood the use of blob and text columns in the database, so in the schema I also replaced these with varchar types instead. In MySQL 5.0.3 and later, varchar columns can be up to 65,535 characters long, so there should be no need for text columns. The cover and thumb columns of the tracks table need to remain blobs, though. There seems to be a bug converting file paths encoded in the server's codepage to and from UTF, so I presume that the use of a binary data columns was a quick and dirty way of fixing character set issues. But it's far from optimal, considering the use of blobs for things as common as track and album titles.

agentsmith
2007-05-09, 18:04
I tried the above recommendations and it improved speed greaty in most cases. For instance searching on my 17000+ songs database less than a second now Vs 5-10 seconds.

However, browsing into artists and albums remain excruciatingly slow. Curiously I took the advise of ont of the poster and disable on access virus scanning and presto the speed came back right away.

However, I am also running SS my corporate laptop, and the McAfee virus scanner is locked with a password.

Does anyone know how to stop McAfee temporarily when it is secured? I cannot even stop the tasks from WIndows Task Manager.

Balthazar_B
2007-05-10, 05:20
However, I am also running SS my corporate laptop, and the McAfee virus scanner is locked with a password.

Does anyone know how to stop McAfee temporarily when it is secured? I cannot even stop the tasks from WIndows Task Manager.

Sounds like your corporate IT folks are doing the job they're supposed to. Hope they're getting properly compensated for it :).

Wirrunna
2008-03-25, 17:31
I know this has been stated in other threads, but I wanted to mention it again:

If you are running an on-access virus scanner, try adding "*.MYD" and "*.MYI" to the exclusion list.

Adding "C:\Program Files\SlimServer\" and its sub-directories had no effect on my machine, so I just assumed I was dealing with poor MySQL performance. Then I decided to monitor my Virus Scan scanner while I hit Refresh on my New Music link. That's when I noticed the MYD and MYI files in C:\Windows\Temp\.

Excluding those extensions from my on-access scanner has resulted in a snappy SlimServer web interface! Life is much better now.

-Wolo

Good post - worked for me !
Full clear and rescan of 20,000 tracks down from 1 hour 5 minutes to around 45 minutes.
Is there any way of specifying where the MYD and MYI files are built ?


SC 7, SB3, WinXP