Optimizing to use more memory?

[gorman]

Logitech Media Server Version: 7.7.1 - r33750

I wonder... I have selected the Database Memory Config option for 1GB+ but I remember reading that that setting could do more with more RAM.

I have 4GB installed and would like to know if there is some config file one could modify to get better performance. When mySQL was used I used:

key_buffer_size       = 16M
sort_buffer_size      = 2M
join_buffer_size      = 2M
read_buffer_size      = 512K
read_rnd_buffer_size  = 2M

innodb_buffer_pool_size = 40M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

But I don't know if there is something equivalent with SQLite.

[bobkoure]

I don't think there's much in the way of tuning available.
As far as I know, there's notifying SQLite that there's more memory in the system (which, I'd expect, is used to set heap, scratch, and pagecache to larger values - and so make it more likely for you to get an in-memory 'hit'). I sometimes wish they'd made these values set-able, say, in the ini file. They'd be able to offload figuring out tuning to some end users, and could roll their findings back into the next version.

I also had a bunch of extra memory, thought maybe I could get some speed benefit out of it, so I setup a ramdrive, and put the cache there. My experience is that things are a *bit* faster, particularly with the web UI, but IMO, that's more from the album covers being in memory than from SQLite's db file being in memory (the db's pretty small and b/b+ trees just don't hit the disk much, particularly with most/all of it already in memory).
If I'd known how much improvement a ram drive would have brought, I probably wouldn't have bothered.
(oh - atom 525, 4G mem, win 2003, server 7.62, ~75K tracks)
If you're curious, SQLite doc is all online. Here's what can be set: http://www.sqlite.org/c3ref/c_config_getmalloc.html

[Mnyb]

you can tweak the SQLiteHelper.pm file if you run the server on linux/OSX or using perl on windows instead of the compiled version.

i haveno idea what you acomplish there is these lines of codes considering himem for SQLite.

127	        # Wweak some memory-related pragmas if dbhighmem is enabled
128	        if ( $prefs->get('dbhighmem') ) {
129	                # Default cache_size is 2000 pages, a page is normally 1K but may be different
130	                # depending on the OS/filesystem.  So default is usually 2MB.
131	                # Highmem we will try 20M
132	                push @{$sql}, 'PRAGMA cache_size = 20000';
133	                
134	                # Default temp_store is to create disk files to save memory
135	                # Highmem we'll let it use memory
136	                push @{$sql}, 'PRAGMA temp_store = MEMORY';
137	        }
138	        
139	        # We create this even if main::STATISTICS is not false so that the SQL always works
140	        # Track Persistent data is in another file
141	        my $persistentdb = $class->_dbFile('persist.db');
142	
........ Snip

The cache size pragma is the interesting part .
Is there any metrics how many pages are needed for a certain dB size to exploit the computer fully.

The 20M setting may be as good as it gets

Maybe other pragmas could be inserted here , to ?

Sugestion anyone, that knows SQLite and perl

The problem with manually editing pm files is that you have to do it every upgrade ?

[Mnyb]

Maybe an opurtunity for a " really high mem" plugin .

That makes the puter LMS b*tch and hog as much resources as the user wish in some settings

This is quite the oposite of what logitech tries to do , they want an ligth background service afaik.

[sander]

I run a pretty old version (7.3.4) so I can't say for sure, but doesn't 7.6+ have some sort of use more memory for database under the settings already, or was the for the msql versions only?

[Mnyb]

I run a pretty old version (7.3.4) so I can't say for sure, but doesn't 7.6+ have some sort of use more memory for database under the settings already, or was the for the msql versions only?

Yes it has but the OP want even more than that

"PRAGMA cache_size = 20000" is what the himem setting in 7.7.1 gives you it's 10 times more than the default

[bobkoure]

...The cache size pragma is the interesting part...

Well, and 'temp store in memory', which I missed, BTW.

Have you looked through SQLiteHelper.pm to see what size pages are being created? Not sure it'd be there, but where else? From my reading, there's a definite benefit for the page size to be the same as the disk page size.
For windows, NTFS page size is 4K. The SQLite default page size is 1K. Page size is created when the tables are created, so there'd be a SQL statement "PRAGMA page_size=something" if it was set to anything other then the default (and PRAGMA page_size=4096 means it's optimized for windows; I think linux page/cluster size is 1K, so default is already OK there).
I'd guess the server developers got it right, but even if not, creating new tables with 4KB (or whatever) pages, copying over, and then replacing the old with the new can be done outside the server (python, perl, tcl, whatever you like). I'd also guess that the tables are created during a "delete everything and rescan" but otherwise are simply modified, so maybe build something to just create a db and new tables, then let the server scan everything.
Also, there's some benefit to having the contents ordered by key, so something like
create table temp as select * from old;
delete from old;
insert into old select * from temp order by key;
drop table temp;
(SQL could easily be wrong, just an illustration, don't beat me up, etc. ...) I'd recreate the indexes as well, just to make sure.
This, too, could be done outside of the server, and, for windows, at least, you could do
PRAGMA page_size=4096;
create table temp as select * from foo;
drop table foo;
create table foo;
insert into foo select * from temp order by key;
drop table temp;
(again, very untested SQL)

Hm... thanks for the nudge, I hadn't thought this through previously.

[gorman]

Yes it has but the OP want even more than that

"PRAGMA cache_size = 20000" is what the himem setting in 7.7.1 gives you it's 10 times more than the default

I mainly have lag with the New Music menu entry. It requires 20-30 seconds before reacting...

[pski]

I mainly have lag with the New Music menu entry. It requires 20-30 seconds before reacting...

I'm still using 7.5.5 (VB) but have you tried changing the server priority?

What do you have your "New music limit" set to?

P

[MrSinatra]

andyg had said there was a thing or two in sqlite that setting would adjust.