Tag: mysql

Posted on

How to additionally clean-up Kodi MySQL database from unwanted entries

If you are like me using Kodi (XBMC) library to manage all your multimedia files you are probably used to updating and cleaning your library after adding or deleting new files.

My library at the moment consists of 15121 files total, but after examining it in PHPMyAdmin I have noticed that files table contains also some entries that point to non-existing idPath from path table and other files that are pointing to a path that is non-local (HTTP or plugin instead of regular system path). Continue reading “How to additionally clean-up Kodi MySQL database from unwanted entries”

Posted on

How to get recommended key_buffer_size in MySQL

Execute following command in mysql shell by connecting with your username and password:

mysql> SELECT CONCAT(ROUND(KBS/POWER(1024,
 IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
 SUBSTR(' KMG',IF(PowerOf1024<0,0,
 IF(PowerOf1024>3,0,PowerOf1024))+1,1))
 recommended_key_buffer_size FROM
 (SELECT LEAST(POWER(2,32),KBS1) KBS
 FROM (SELECT SUM(index_length) KBS1
 FROM information_schema.tables
 WHERE engine='MyISAM' AND
 table_schema NOT IN ('information_schema','mysql')) AA ) A,
 (SELECT 3 PowerOf1024) B;

Response will be something like this:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 1G |
+-----------------------------+
1 row in set (0.08 sec)

When I used this calculation for my Raspberry Pi 2 running MySQL result was 0G, so you need to modify the query to show either MB or KB values by using:

For key_buffer_size recommended size in MB:

SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;

Result:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 1M |
+-----------------------------+
1 row in set (0.44 sec)

For key_buffer_size recommended size in KB:

SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 1 PowerOf1024) B;

and result is:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 23K |
+-----------------------------+
1 row in set (0.44 sec)
Posted on

MySQL purge old binary logs

If mysql-bin.xxxyyy logs are eating up your partition space. This is the right trick for you:

# login to mysql
mysql -u root -p
# create latest log mysql-bin.0000xy
flush logs;
# purge all logs before mysql-bin.0000xy
purge binary logs to 'mysql-bin.0000xy';