The Wonderful World of Linux A mostly dead cpanel/linux blog

Switching Over To InnoDB From MyIsam

InnoDB can be a complete pain to manage, troubleshoot and fix…but this is all wiped out by its impressive performance improvements as documented all over the place. Now simply switching to InnoDB on your tables should increase your performance but you can go even further by optimizing them to take advantage of some of the core features of InnoDB. Either way, switching should be your first step, lets get started!

Switching 1 Table

If your running a forum or other software that traffics a majority of its data out of 1 table, it may make sense just to change that 1 table rather than change the entire databse. As such, the following command should suite you just fine. Log into your mysql console and run the following:

ALTER TABLE table_name ENGINE=InnoDB;

Simple as that! You should of course always make backups before running this just in case. To verify the InnoDB tables in the system, log back into your mysql and run this:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Switching 1 Database

Of course switching just 1 table wouldnt be enough, lets bring it up to 1 complete database. Having a table under InnoDB should yield some solid improvements, having an entire DB is even better. We have two options, a bash script and a mysqldump.

###Bash Script

TABLE=db_name

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema IN ('${TABLE}') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

This will then output the appropriate mysql commands to alter all tables defined in the TABLE variable. The reason why this outputs it to a sql file rather than just runs the command is so that you have the ability to manually review it and ensure nothin wonky happened. To apply this, run the following:

mysql < /root/ConvertMyISAMToInnoDB.sql

###MySQL Dump

Im just providing this so you have multiple options. Here is a way that you can do the same affect but you change everything over by dumping. This is useful if you want to duplicate the dump first for testing:

mysqldump [database]  | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/' > [innodb-database.sql]

Changing Everything

You could change all of your tables over by doing a dump or multiple dumps and sedding out the content similar to the above, I just dont see a use in it. Here’s a shell script that performs similar functions to the above only it selects all myisam tables except those in information_schema, mysql or performance_schema:

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

Then verify and apply it with the following:

mysql < /root/ConvertMyISAMToInnoDB.sql

And that does it! Enjoy the wonderful performance improvements that is InnoDB.

Using Strace to Identify Slow Website

There are quite a few times where troubleshooting slow page load times can well…be troublesome. Now this doesnt work every time and unless you are very well tuned to all the system calls of strace (If so, why are you reading this?), it may not even be useful. However, this command has completely solved my issue on a number of occasions:

strace -t -f -o strace.txt /usr/bin/php index.php

If you open strace .txt, on the left hand side you will see a time field. If you open strace.txt file using less or your favorite file editor, you will notice 3+ columns:

12521 13:59:33 open("/opt/curlssl//lib64/libstdc++.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)

The first column is the PID of the actual PHP command, the second is the time at which it ran and the third and fourth are the system calls and potential errors such as a file not being found in this case. If you see page just spin and spin and spin, check the time and see if there is one command that is slowing down everything. I made a little test to demonstrate this. Take a look at the following PHP script:

<?php
$fp = fsockopen("www.example.com", 8080, $errno, $errstr, 30);
if (!$fp) {
    echo "$errstr ($errno)<br />\n";
} else {
    $out = "GET / HTTP/1.1\r\n";
    $out .= "Host: www.example.com\r\n";
    $out .= "Connection: Close\r\n\r\n";
    fwrite($fp, $out);
    while (!feof($fp)) {
        echo fgets($fp, 128);
    }
    fclose($fp);
}
?>

This script will try and hit http://example.com and make sure it can connect to port 8080. Now this port is closed so it will fail and timeout. The timeout is whats going to get us here. The script cant complete and move past this time unless the script times out or the remote server send us something. Lets take a look at the strace:

24281 16:05:50 fcntl(3, F_GETFL)        = 0x2 (flags O_RDWR)
24281 16:05:50 fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
24281 16:05:50 connect(3, {sa_family=AF_INET, sin_port=htons(8080), sin_addr=inet_addr("192.0.43.10")}, 16) = -1 EINPROGRESS (Operation now in progress)
24281 16:05:50 poll([{fd=3, events=POLLIN|POLLOUT|POLLERR|POLLHUP}], 1, 30000) = 0 (Timeout)
24281 16:06:20 fcntl(3, F_SETFL, O_RDWR) = 0
24281 16:06:20 close(3)                 = 0

If you look soley in thesecond column, you will see a big jump in time of 30 seconds for this one command. Taking a closer look at the commands themselves you can see sin_addr=inet_addr(“192.0.43.10”) and the port sin_port=htons(8080) seem to be the likely culprit. From there you can find out if the port is open in your firewall or theires and if all else fails using a clever hack to work around it!

Record Your SSH Session

If you’re troubleshooting an issue on your server and you suck at taking documention, you can simply pipe the SSH output into a file for review later:

ssh [email protected] | tee -a /path/to/file

What is really cool about this method (As opposed to just saving the history) is that it will also save the result of the command.

Last login: Wed Jan 30 10:19:44 2013
[email protected] [~]# echo "Hello"
Hello
[email protected] [~]# logout

You can also automate this. Create a file in your ~/bin/ directory called sshlog or similar and input the following code:

ssh [email protected] | tee -a /path/to/file

Set the execution bits and you can now run sshlog [email protected] and any other flags you would normally run with SSH and it will be appended to your file (as defined by the -a flag in tee). If you want this to always run when you kick of ssh, simply create an alias:

alias ssh='sshlog'

Call to undefined method mysqli_result::fetch_all()

If you have ever experienced this error and you are positive that mysqli is installed, the issue is probably because you need mysqlnd. Checkout the following code snippet:

<?php
	$conn = new mysqli($servername, $username, $password, $database, $port);
	$r = $conn->query("SELECT * FROM `dummy`", MYSQLI_STORE_RESULT);
	$arr = FALSE;
	if($r !== FALSE)
	{
		echo "<br />Vardump:<br/>";
		var_dump($r);
		echo "<br /><br />Checking if methods exist:<br/>";		
		echo "<br />fetch_assoc() method exists? ";
		var_dump(method_exists($r, 'fetch_assoc'));
		echo "<br />fetch_all() method exists? ";
		var_dump(method_exists($r, 'fetch_all'));		
		echo "<br />";
		$arr = $r->fetch_all(MYSQLI_ASSOC);
		$r->free();
	}
	print_r($arr);

When running this, you may encounter the following error:

fetch_assoc() method exists? bool(true)
fetch_all() method exists? bool(false)

The fetch_all() method within mysqli actually relies on mysqlnd being installed at compile time. If you are using CentOS, atomicorp has rpm’s within their repo that should install this:

http://www6.atomicorp.com/channels/atomic/centos/6/i386/RPMS/

Debian can be found here:

http://packages.debian.org/sid/php5-mysqlnd

If you are running cPanel, add this:

--with-mysqli=mysqlnd

To this:

/var/cpanel/easy/apache/rawopts/all_php5

And then run easyapache:

/scripts/easyapache --build

Now everytime you need to recompile, that flag will always be tacked on. Enjoy!

A Simple PHP Mail Script

I receive a lot of questions and concerns from developers stating that the phpmail function is not correctly working. The problem is that 9 times out of 10 it really is installed and working and the issue usally comes from a coding error or user error (thats the thing between the keyboard and the chair). One of my core philosophies when troubleshooting issues is to break it down in its most basic form. So to verify whether or not its actually working, just create blank php page and insert the following:

<?php
$to = "[email protected]";
$subject = "Email works!";
$message = "Huzzah!";
$from = "root";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
echo "Mail Sent.";
?>

This is as basic as it gets. Really good for verifying whether or not the issue lies with your server, or you! Just run it with the following:

php mail.php

If all goes well you should now be receiving an email directly to your inbox. If not, then I would start by looking at your php.ini and seeing if it is disabled within the functions. You will also want to ensure that you actually have a valid MTA such as sendmail or exim and that it is indeed working and accepting connections through localhost (Or as defined by your php.ini). The mail function is built into the PHP core so there really isnt any extra enabling that needs to be happen here. If you are still having trouble, I would reccomend hopping on the IRC or Forums for your respective distro!