Tag: optimization

Posted on

High Performance USB stick formatting with ext4

To format my USB sticks that I use following set of commands:

Assuming my USB stick is recognized as sdc, and has a primary partition created this is the set of commands that I use:

# create ext4 filesystem without journal
 mkfs.ext4 -O ^has_journal /dev/sdc1

# give your newly formatted partition a name
 e2label /dev/sdc1 storage

# Use data mode for filesystem as writeback for better performance
 tune2fs -o journal_data_writeback /dev/sdc1 

# Disable reserved space on my 57GB drive it wastes 5% which is 3GB
 tune2fs -m 0 /dev/sdc1
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)