What Time did MySQL Start?

Time MySQL Server Started

From a mysql client just execute the following to see the date and time the server was started.

select now() - interval (variable_value/60/60/24) day -- \

from information_schema.global_status -- \

where variable_name='Uptime';

From the shell, this will provide the same information.

mysql -uroot -p -A -s  -e"select now() - interval (information_schema.global_status.VARIABLE_VALUE/60/60/24) day -- \"from information_schema.global_status where variable_name='Uptime';"

Sometime you may run into having to restart the mysqld under pressure. And it will not come up.  

One cause is the my.cnf file is modified some time after startup with a bad setting. You could use the results from the commands above and compare that to

stat /etc/my.cnf

to see if any changes were made to my.cnf that might prevent the server from re-starting.

A little-mentioned feature on the interwebs regarding MySQL is the InnoDB monitors.

It has been around for a long time.

http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html

You have to create varying tables to enable the monitoring.  You only want to do this when your system can withstand a performance degradation.

When you are trying to improve performance or want a look under the hood, the monitors are valuable.

Monitors provide rich information about InnoDB such as File I/O, Locks, and Cardinality of indexes.  They can help to provide a shape of the overall activity on your InnoDB tables.

The amount of information can be overwhelming when you are in a scenario where performance is degrading rapidly and you are on-call to remedy the degradation of service.

The most common issue is lock contention where more than 2 clients attempt to update and read the same row.  This is sometimes unavoidable, like having a conversation where more than two people are involved.  The natural remedy is to have lock waits timeout at half the time the user expects a result (1.5 seconds), which is configurable in my.cnf (or my.ini on windows).  Then the application must retry the transaction if it faults.  Unfortunately retry-code is not part of many of the standard database libraries and must be incorporated by the developers.  In short time, the frameworks could handle for this case.

You can also replay the binary log on a server with these monitors enabled to dig deeper into a past performance degradation.

The monitors are powerful diagnostics built-in to MySQL.

MySQL Connector/J: Failover and Blacklists

MySQL Connector/J provides loadbalancing and failover support.  While testing failover I wanted to know what was happening under the hood. Specifically I wanted to know when a host was blacklisted and which host.

If you set loadBalanceStrategy to random in your connection properties, which has been the recommended strategy over round-robin, you can modify the RandomBalanceStrategy.java file to log when a host is blacklisted.

Using guidance found on Todd Farmer's blog (http://mysqlblog.fivefarmers.com/) and a bit of twiddling it wasn't difficult.

In my setup, I have jboss-as-7.1.1. running on my local windows machine with my test app I developed using one of the JBoss quickstarts.

The connector (connector-java-5.1.21) is deployed and I have the source on C:\mysql-connector-java-5.1.21.

In RandomBalanceStrategy.java, right after the class declaration, I added the second line shown below.

public class RandomBalanceStrategy implements BalanceStrategy {
    private static Logger logger = Logger.getLogger("cyfd.connectorj.plugins");

Next, I added the second line shown below.

proxy.addToGlobalBlacklist( hostPortSpec );
logger.log(Level.SEVERE, "RandomBalanceStrategy-"+"Blacklisted host: " + 
   hostPortSpec + " att: " + attempts + " ret:" + numRetries  );

Save the file and compile it: (from windows cmd prompt at C:\mysql-connector-java-5.1.21\src\com\mysql\jdbc>; and, this is all one line)

javac -verbose -classpath "C:\jboss-as-7.1.1.Final\modules\com\mysql\main\
mysql-connector-java-5.1.24-bin.jar" -sourcepath . RandomBalanceStrategy.java

With jboss shutdown, update the jar file with the new class (again, this is all one line but executed from under the src folder which is C:\mysql-connector-java-5.1.21\src on my machine):

jar uf C:\jboss-as-7.1.1.Final\modules\com\mysql\main\mysql-connector-java-
5.1.24-bin.jar com/mysql/jdbc/RandomBalanceStrategy.class

Start jboss and when a host is blacklisted, you'll get a message similar to this.
08:25:11,005 SEVERE [cyfd.connectorj.plugins] (http--127.0.0.1-8080-80) RandomBalanceStrategy Blacklisted host: mysqlA:3306 att: 0 ret:100

So far the results of failover and load balance testing work perfectly.   As far as I can tell, a web client will get an error page--sometimes--but they can refresh the page and all transactions happen on the other server.

More testing to do....