[chishm]

Hi all,

I finally got a shared library going using MySQL running on my router (ASUS RT-N16) under OpenWRT. I thought I'd share some tips on how I did it and how to make it faster.

First install OpenWRT on the router. How you do it will be router specific.
Next, modify the MySQL package to add InnoDB and query cache support:

1. Set up an OpenWRT build root by following the instructions on the OpenWRT wiki. Configure it for your specific device, and do a full make.
   
2. Modify the mysql Makefile. For Barrier Breaker it is located in openwrt/feeds/oldpackages/libs/mysql/Makefile. These instructions are based on a post by glococo on the OpenWRT forums
   
   1. Add the line "--with-innodb \" (no quotes) just before "--with-server \"
      
   2. Change the line "--without-query-cache \" to "--with-query-cache \"
      
3. Re-run make.
   
4. Copy the generated mysql-server_5.1.73-1_brcm47xx.ipk file to your router
   
5. Run "opkg update" on the router
   
6. Modify /tmp/opkg-lists/barrier_breaker to change the md5sum and sha256 hash for mysql-server_5.1.73-1_brcm47xx.ipk to match those of the newly generated file.
   
7. Run "opkg install mysql-server_5.1.73-1_brcm47xx.ipk"
   
8. Add the following lines to /etc/my.cnf under the [mysqld] heading (sizes can be adjusted depending on your device's RAM):
   
   Code:

   query_cache_type        = 1
query_cache_size        = 4M
innodb
innodb_file_per_table   = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 4M
innodb_log_buffer_size  = 512K
default-storage-engine  = innodb
9. Also change the datadir and tmpdir options to point to whatever storage you have attached to the router. The database will be created here.
   

Finally, start mysqld (/etc/init.d/mysql start) and follow the usual instructions for adding it to XBMC.

What is the point of all this? I had really slow database accesses (took 30 seconds to show the Music Artist list) before. I first followed the instructions from Ahuch to use InnoDB instead of MyISAM, and although it sped up library updates it didn't fix the read issue. Adding the query cache means MySQL has a canned response for when XBMC asks for some database info. Unfortunately it's not perfect, as I still experience slow queries when first opening a part of XBMC I haven't used before (e.g. opening a new TV show's episode list), but it's a lot better than before.

Hopefully someone will find this useful, and I look forward to any more speed tips.

[onebillion]

Hi,
I've tried doing this (recompiling with the query_cache). It works if I manually set the query_cache globals in mysql.
However, query_cache_size defaults to 0 when I start mysql, even though I've got a value in /etc/my.cnf

Are you seeing this - and any other ideas?

[chishm]

I can't say I'm seeing that. Are you sure it's reading the config file? You could try setting an invalid variable in the config file, restarting MySQLd, and checking if mysqladmin complains.