2014-08-21, 23:58
Short version: With large music collections and the setup below, the Party mode play list is not kept at 10 items, it starts at 10 items and gradual reduces to zero items with no new items being added.
Setup
NAS: QNAP TS-210 with MySQL 5.1.36-log
Renderer: Raspberry Pi, OpenElec, XBMC Gotham 13.1, Library using smb to the NAS, advancedsettings.xml configured to point at the MySQL on the NAS
Controller: Yatse on Android tablet
Also occurs with XBMC Gotham 13.1 on Windows 8, when running there, this error is displayed in the GUI
“Party mode aborted. Could not get songs from database.”
Doing a web search for that leads to a number of people having the same problem, but no fixes.
Looking in the xbmc log file gives events of this form
ERROR: SQL: Undefined MySQL error: Code (126)
Query: select * from songview WHERE songview.idSong not in (4845,10282,8524,4890,2351,391,7574,1557,2113,9691) ORDER BY RAND() LIMIT 1
Running the SQL in phpMyAdmin (on the NAS), gives
#126 - Incorrect key file for table '/tmp/#sql_1c88_0.MYI'; try to repair it
Doing a web search for #126 mysql, leads to
temp table failed due to lack of /tmp free space
http://stackoverflow.com/questions/19003...-for-table
running this in phpMyAdmin
SHOW VARIABLES LIKE 'tmpdir';
gives
tmpdir=/tmp
so I SSH onto my NAS
df -h
tmpfs 31.7M /tmp
i.e. /tmp is small and is a ram disk
Looking at the SQL from the log file it looks like it could be optimised, I think the whole view excluding the "not in ()" items is being copied to /tmp, which is failing as it's too much data.
If only the idSong column was considered, then the temporary table will be much smaller and quicker to copy.
My first instinct would be to use a nested query, which I couldn't get to work, because of
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
But I did get these statements to work, also those two statements run quicker than the original as less data needs to be copied to /tmp.
Request:
Please update the get next random track SQL statements for the Party modes, to be similar to the above.
Investigations into workarounds
To get me working while the code is hopefully being updated, I did the following to put the MySQL tmpdir onto the HDD instead of the ramdisk, makes the party mode work at the cost of being a bit slower for any queries that need to use tmpdir.
different commands will be needed for other MySQL implementations, the gist of it being reconfigure the MySQL tmpdir to be in a location that has enough free space to cope with the temporary tables, and the restart MySQL.
Doing some analysis of the SQL queries using the profiling mode of phpMyAdmin
-- select * will be copying the whole of the view to tmpdir, i.e. slower
select * from MyMusic46.songview WHERE idSong not in (1,2,3,4,5,6,7,8,9,0) ORDER BY RAND() LIMIT 1
tmpdir on HDD: 4.4304s 72% Copying to Tmp table, 27% sorting
tmpdir on RamDisk: fail #126 error, i.e. not enough free space
-- just consider the idsong column
select idsong from MyMusic46.songview where idsong not in (1,4,6,7,8,9) order by rand() limit 1
tmpdir on HDD: 1.0880s 88% Copying to Tmp table, 11% sorting
tmpdir on RamDisk: 0.9988s
-- Then get all of the row, given the idSong found above
select * from MyMusic46.songview where idsong in (@songID)
0.0082s for both as tmpdir is not used
Doing some timings of using party mode music from Yatse
tmpdir location, music starts, add another song to the list
RamDisk, 71s, fail
HDD, 71s, 4s
I haven't investigated why it takes 71 seconds to start playing, could be something about using XBMC on a Raspberry Pi, or possibly more SQL queries that need optimisation.
The 4 seconds to add the next 10'th track to the party play list, matches up to the time that the unoptimised SQL takes to run.
Setup
NAS: QNAP TS-210 with MySQL 5.1.36-log
Renderer: Raspberry Pi, OpenElec, XBMC Gotham 13.1, Library using smb to the NAS, advancedsettings.xml configured to point at the MySQL on the NAS
Controller: Yatse on Android tablet
Also occurs with XBMC Gotham 13.1 on Windows 8, when running there, this error is displayed in the GUI
“Party mode aborted. Could not get songs from database.”
Doing a web search for that leads to a number of people having the same problem, but no fixes.
Looking in the xbmc log file gives events of this form
ERROR: SQL: Undefined MySQL error: Code (126)
Query: select * from songview WHERE songview.idSong not in (4845,10282,8524,4890,2351,391,7574,1557,2113,9691) ORDER BY RAND() LIMIT 1
Running the SQL in phpMyAdmin (on the NAS), gives
#126 - Incorrect key file for table '/tmp/#sql_1c88_0.MYI'; try to repair it
Doing a web search for #126 mysql, leads to
temp table failed due to lack of /tmp free space
http://stackoverflow.com/questions/19003...-for-table
running this in phpMyAdmin
SHOW VARIABLES LIKE 'tmpdir';
gives
tmpdir=/tmp
so I SSH onto my NAS
df -h
tmpfs 31.7M /tmp
i.e. /tmp is small and is a ram disk
Looking at the SQL from the log file it looks like it could be optimised, I think the whole view excluding the "not in ()" items is being copied to /tmp, which is failing as it's too much data.
If only the idSong column was considered, then the temporary table will be much smaller and quicker to copy.
My first instinct would be to use a nested query, which I couldn't get to work, because of
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
But I did get these statements to work, also those two statements run quicker than the original as less data needs to be copied to /tmp.
Code:
-- Get the ID only of a song that is not in the current 10 party list
Set @songID=(select idsong from MyMusic46.songview where idsong not in (0,1,2,3,4,5,6,7,8,9) order by rand() limit 1);
-- get the whole row for the song
select * from MyMusic46.songview where idsong in (@songID)
Request:
Please update the get next random track SQL statements for the Party modes, to be similar to the above.
Investigations into workarounds
To get me working while the code is hopefully being updated, I did the following to put the MySQL tmpdir onto the HDD instead of the ramdisk, makes the party mode work at the cost of being a bit slower for any queries that need to use tmpdir.
Code:
mkdir /share/MD0_DATA/mysqltmp
vi /etc/config/my.cnf
tmpdir = /share/MD0_DATA/mysqltmp
save and quit
/etc/init.d/mysqld.sh restart
different commands will be needed for other MySQL implementations, the gist of it being reconfigure the MySQL tmpdir to be in a location that has enough free space to cope with the temporary tables, and the restart MySQL.
Doing some analysis of the SQL queries using the profiling mode of phpMyAdmin
-- select * will be copying the whole of the view to tmpdir, i.e. slower
select * from MyMusic46.songview WHERE idSong not in (1,2,3,4,5,6,7,8,9,0) ORDER BY RAND() LIMIT 1
tmpdir on HDD: 4.4304s 72% Copying to Tmp table, 27% sorting
tmpdir on RamDisk: fail #126 error, i.e. not enough free space
-- just consider the idsong column
select idsong from MyMusic46.songview where idsong not in (1,4,6,7,8,9) order by rand() limit 1
tmpdir on HDD: 1.0880s 88% Copying to Tmp table, 11% sorting
tmpdir on RamDisk: 0.9988s
-- Then get all of the row, given the idSong found above
select * from MyMusic46.songview where idsong in (@songID)
0.0082s for both as tmpdir is not used
Doing some timings of using party mode music from Yatse
tmpdir location, music starts, add another song to the list
RamDisk, 71s, fail
HDD, 71s, 4s
I haven't investigated why it takes 71 seconds to start playing, could be something about using XBMC on a Raspberry Pi, or possibly more SQL queries that need optimisation.
The 4 seconds to add the next 10'th track to the party play list, matches up to the time that the unoptimised SQL takes to run.