Splitting INNODB files to separate .ibd files

Please note: most of you are probably visiting this post for the answer to whether you can use multiple disks with innoDB, the answer is, even though you can not move idb files around like when you move MYI MYD files, you can move an IDB file while the DB engine is not running, then create a symlink where the Innodb expected to find the original file, then start mysql again, so the short answer is that innodb does work with symlinks just like myisam.

The second answer is, you can not use more than one disk on the same table (even though you can for the same database by putting every table on a separate disk).

As you would probably know, innodb, by default stores all data into its central, single file data dictionary (on debian, /var/lib/mysql/ibdata1), you can ask innoDB to put every table’s data in a separate file (tablename.ibd) by simply adding the directive innodb_file_per_table anywhere under [mysqld] section.

Databases already in single file will stay there, the directive only affects the creation of new databases, InnoDB does not mind mixed mode regarding ibd files, dome tables in a single database can be in the shared file, and some can have their own files. files created while the directive is in effect will get separate files, you can then remove the directive from your my.cnf and the engine will start putting new databases back into the shared file.

Mind you, unlike ISAM tables, you can not move these files around, in MyISAM you can simply copy the 3 files that are any table, the .frm, .MYI and .MYD and you have your table elsewhere, in INNODB, this is not the case as much is still stored in INNODB’s ibdata1 , also, innodb files are connected to a certain database with internal strings, Yet, you can copy the whole data directory and have it work, remember, log files are also needed and must be kept.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

The main disadvantage of splitting data files per table is disk space, files in INNODB never get smaller, when in 1 file, other tables can use that space of deleted rows in another table, when split apart, you can only use the occupied space again if you add rows to the same table that had deleted rows.

The advantages are

1- You can utilize multiple disks for both space and performance (By using symlinks)
2- You can backup certain tables and leave others to add flexibility to your backup plan

Extracting Audio from youtube files without loss of quality

To not get you confused with terminology, let’s create our own with the simple words we know.

For those who have been here before and want the lowdown to remind them of the commands, just scroll to the bottom of this post.

this bit of theory i am starting with is not needed for you to extract your audio, but simply to introduce you to what we are doing

The video file has 2 sub files, one for video and one for audio, and they are synchronized together so that the people’s lips movement in the video appear to be speaking what the audio file is playing.

If we do not want to lose any of the audio’s quality by decoding then re-encoding again, we will want to extract the “Inner audio file” without modifying it, and to put it into a separate file (container).

Separating the audio from the video is easy with a free tool called ffmpeg, and here are the exact instructions

1- Make sure ffmpeg is installed on your computer and is added to the system path, if not see the article (installing ffmpeg)
2- Download the youtube or any other video file to c:downloadvideo, assuming we have 2 tutorial files one is tutorial.flv and the other is tutorial.mp4, both were downloaded from youtube.com (if you don’t know how to download a youtube video, see this article).
3- open the command line (Command prompt can be opened from your start menu, look in accessories).
4- Enter the following command into your command prompt to change active directory to where you have your files, in our example we enter the following into the command prompt

cd c:downloadvideo

5-Then, for my first file, i will execute the command

ffmpeg -i tutorial.flv

You should now see, on your command prompt window, what sub files (streams) are inside your container file (the file you downloaded from youtube or anywhere else).

--------------------------------------------------------------
  Duration: 00:02:06.59, start: 0.000000, bitrate: 64 kb/s
    Stream #0.0: Video: flv, yuv420p, 320x240, 29.97 tbr, 1k tbn, 1k tbc
    Stream #0.1: Audio: mp3, 22050 Hz, mono, s16, 64 kb/s
--------------------------------------------------------------

if you don’t see the word mp3 like this example, don’t panic, just move on to step 7.

In our first example file, the audio stream turned out to be an MP3 stream as you can see below, if that was not the case and we had a different format (As i will explain next), we would have had different choices in extracting, but for this example, we have MP3, meaning we do not need to re-encode anything, just copy the stream from the container we downloaded into a new container that will be created

6- Extract the MP3 file without re-encoding and while keeping full quality like in the video

ffmpeg.exe -i tutorial.flv -acodec copy tutorial.mp3

But for some reason, copeying the OGG ogg vorbis from a webm file without reencoding did not work untill i added the -vn switch

ffmpeg -i Bir_G_zellik_Yap_Murat_Dalk_l.webm -vn -acodec copy test1.ogg

So, now i have the file tutorial.mp3 that simply has the same clarity as my video file. the -acodec copy parameter told ffmpeg to just copy into new file, and not to re-encode

7- If it does not say mp3 anywhere in your results, you have a different audio format. that we will deal with now.

so let us deal with our second file tutorial.mp4 that turned out not to have MP3 in it, but rather AAC.

So executing the command

ffmpeg.exe -i tutorial.mp4

Returned the result

--------------------------------------------------------------
Duration: 00:05:02.44, start: 0.000000, bitrate: 281 kb/s
  Stream #0.0(und): Audio: aac, 44100 Hz, mono, s16
  Stream #0.1(und): Video: h264, yuv420p, 320x240 [PAR 1:1 DAR 4:3], 25 tbr, 25 tbn, 50 tbc
--------------------------------------------------------------

In this case, we do NOT have the option that will allow us to get an MP3 without re-encoding, we can re-encode it into MP3 as i will show you in a bit, or we can extract and use an AAC audio file.

An AAC file is not at all bad, sometimes it is better than an MP3. Why ? The AAC file is a newer format and it will still play on many devices, on most computers, relatively new IPODs, Most modern mobile phones and many other MP3 players.

AAC file streams are mostly put into m4a containers, but can also be .m4b, .m4p, .m4v, .m4r, .3gp, .mp4, .aac. (OF YOUR CHOICE, the most common is m4a, but some older mobiles use 3GP)

So, let us first try to extract the AAC stream without re-encoding (next step we will re-encode to MP3 for those who want an MP3)

ffmpeg.exe -i tutorial.mp4 -acodec copy tutorial.m4a

Now, the device you want to use does not support AAC files, so what we can do is ask ffmpeg to convert it to MP3 for us

ffmpeg -i tutorial.mp4 tutorial.mp

—————————————————–

The Lowdown

1- Find out what streams exist

ffmpeg -i tutorial.mp4

2- Copy MP3 stream without re-encoding

ffmpeg.exe -i tutorial.mp4 -acodec copy tutorial.mp3

3- Copy AAC stream without re-encoding

ffmpeg.exe -i tutorial.mp4 -acodec copy tutorial.m4a

4- Re-encode audio to MP3 file fromat

ffmpeg -i tutorial.mp4 tutorial.mp3

 

To encode any file into a DVD compatible file (Best when used with DVD players that have a USB input or with USB TV)

ffmpeg -i 9.mp4 -threads 2 -filter:v "scale='if(gt(a,720/480),720,-1)':'if(gt(a,720/480),-1,480)',pad=w=720:h=480:x=(ow-iw)/2:y=(oh-ih)/2" -target pal-dvd 9.mpg

Sometimes, you might want to replace pal-dvd with ntsc-dvd

Speed testing an internet connection

Well, there are a few ways to check the upload / Download speed of an internet connection, one way is speedtest.net which uses flash to download a file, and upload a file, both to a server close to you

On systems where we do not have a browser or do not have a browser that supports flash, one can download a file (With wget  on Linux for example), the quest would be this

You will need a file that is hosted on a network that you know for fact is faster than your own internet connection, for me, i have been using this one very successfully

cachefly.net 100mb.test

So, on a LINUX system, entering

wget http://cachefly.cachefly.net/100mb.test

On a casual 2.4Mb (That’s Mega Bit not Byte) , it should result in something like this

---------------------------------------------------------------------------------------
--2012-04-19 11:41:09--  http://cachefly.cachefly.net/100mb.test
Resolving cachefly.cachefly.net... 140.99.93.175
Connecting to cachefly.cachefly.net|140.99.93.175|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 104857600 (100M) [application/octet-stream]
Saving to: `100mb.test'

 6% [=>                                     ] 6,897,290    284K/s  eta 5m 41s
---------------------------------------------------------------------------------------

While on a much faster connection i have somewhere else (theoretical 100Mb), the results are like this

--2012-04-19 08:44:20--  http://cachefly.cachefly.net/100mb.test
Resolving cachefly.cachefly.net... 140.99.93.175
Connecting to cachefly.cachefly.net|140.99.93.175|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 104857600 (100M) [application/octet-stream]
Saving to: `100mb.test'

100%[======================================>] 104,857,600 41.2M/s   in 2.4s

2012-04-19 08:44:22 (41.2 MB/s) - `100mb.test' saved [104857600/104857600]

There are also other factors in internet connection speed that i will get to soon, for example, latency, and efficient routing.

things that i will get to when i have the time.