Identifying MySQL SSL communication using ngrep

Prior to MySQL 5.7 client communications with a MySQL instance were unencrypted by default. This plaintext capability allowed for various tools including pt-query-digest to analyze TCP/IP traffic. Starting with MySQL 5.7 SSL is enabled by default and will be used in client communications if SSL is detected on the server.

We can detect and confirm this easily with the ngrep command.

Default client connection traffic (5.6)

On a MySQL 5.6 server we monitor the default MySQL port traffic.

mysql56$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306
interface: eth1 (192.168.42.0/255.255.255.0)
filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6)))
...

We connect to this server using TCP/IP.

host$ mysql -uexternal -p -h192.168.42.16
mysql56> select 'unencrypted';

We can observe the communication to and from the server (in this example 192.168.42.16) is plaintext.

...
#
T 192.168.42.1:47634 -> 192.168.42.16:3306 [AP]      select 'unencrypted'
#
T 192.168.42.16:3306 -> 192.168.42.1:47634 [AP]      !    def    unencrypted  ! !                       unencrypted
#

SSL System Variables (5.6 default)

A default 5.6 installation does not have SSL enabled as verified by system variables.

mysql56 >SHOW  VARIABLES  LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.02 sec)

Default client connection traffic (5.7)

Running the same example client connection with MySQL 5.7 you will observe that communications to and from the server (in this example 192.168.42.17) are not in plaintext.

mysql57$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306
interface: eth1 (192.168.42.0/255.255.255.0)
filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6)))

host$ mysql -uexternal -p -h192.168.42.17
mysql57> select 'encrypted';


T 192.168.42.1:36781 -> 192.168.42.17:3306 [AP]     @    F   l   d iVr  H   b ^    s t Z      ( 2d   " ?  |   )
#
T 192.168.42.17:3306 -> 192.168.42.1:36781 [AP]     p%  s`   3u5!%P]   v=  r # x   E   a y  '!    )Z    8   Js  z.  \t   (r H@     0 2 5k\    <   M  @)E& b q|q@    h

SSL System Variables (5.7 default)

A new MySQL 5.7 installation will have SSL enabled by default as seen in the MySQL variables.

mysql57 > SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

-no-ssl Client connection traffic (5.7)

If you want to emulate the unencrypted nature of MySQL 5.6 within any SSL enabled MySQL version (e.g. MySQL 5.7) you specify the --ssl option for mysql client connections. In MySQL 5.7 this option is also deprecated and --ssl-mode=disabled should be used>.

host$ > mysql -uexternal -p -h192.168.42.17 --ssl=0

host >select '-ssl=0 unencrypted';

T 192.168.42.1:36785 -> 192.168.42.17:3306 [AP]      select '-ssl=0 unencrypted'
#
T 192.168.42.17:3306 -> 192.168.42.1:36785 [AP]      '    def    -ssl=0 unencrypted  ! 3              -ssl=0 unencrypted

References

https://wiki.christophchamp.com/index.php?title=Ngrep
http://infoheap.com/ngrep-quick-start-guide/
Encrypted Connections - MySQL 5.7 Reference Manual
Implementing MySQL Security Features - Tutorial at Percona Live Europe 2017.

Understanding the Oslo Libraries

Underpinning all of the OpenStack projects including Nova, Cinder, Keystone, Glance, Horizon, Heat, Trove, Murano and others is a set of core common libraries that provide a consistent, highly tested and compatible feature set. The Oslo project is a collection of over 30 libraries that are designed to reduce the technical debt of code duplication across projects and provide for a greater quality code path due to the frequency of use in OpenStack projects.

These libraries provide a variety of different features from the more commonly used functionality found in projects including configuration, logging, caching, messaging and database management to more specific features like deprecation management, handling plugins as well as frameworks for command line programs and state machines. The Oslo Python libraries are designed to be Python 2.7 and Python 3.4 compatible, leading the way in migration towards Python 3.

The first stable Oslo library oslo.config was included in the Grizzly release. Now over 30 libraries comprise the Oslo project. These libraries fall into a number of broad categories.

1. Stable OpenStack specific libraries

These libraries, using the olso. prefix are generally well described the library name.

  • oslo.cache
  • oslo.concurrency
  • oslo.context
  • oslo.config
  • oslo.db
  • oslo.i18n
  • oslo.log
  • oslo.messaging
  • oslo.middleware
  • oslo.policy
  • oslo.privsep
  • oslo.reports
  • oslo.serialization
  • oslo.service
  • oslo.utils
  • oslo.versionedobjects
  • oslo.vmware

2. Python libraries that can easily operate with other projects

In addition to the oslo namespace libraries, Oslo has a number of generically named libraries that are not OpenStack specific. The goal is that these libraries can be utilized outside of OpenStack by any Python project. These include:

  • automaton – a framework for building state machines.
  • cliff – a framework for building command line programs.
  • debtcollector – a collection of python patterns that help you collect your technical debt in a non-destructive manner (following deprecation patterns and strategies and so-on).
  • futurist – a collection of async functionality and additions from the future.
  • osprofiler – an OpenStack cross-project profiling library.
  • hacking – a library that provides a set of tools for enforcing coding style guidelines.
  • pbr – (or Python Build Reasonableness) is a add-on library that helps provide (and enforce) a set of sensible default setuptools behaviours.
  • pyCADF – a python implementation of the DMTF Cloud Audit (CADF) data model.
  • stevedore – a library for managing plugins for Python applications.
  • taskflow – a library that helps create applications that handle state/failures… in a reasonable manner.
  • tooz – a library that aims at centralizing the most common distributed primitives like group membership protocol, lock service and leader election

3. Convenience libraries

There are also several libraries that are used during the creation of, or support of OpenStack libraries.

The first was oslo-incubator where as the name suggests, initial libraries were incubated. As this code matured it was refactored into standard libraries. Projects have either graduated, been incorporated elsewhere or been deprecated. While the Oslo Incubator has been removed of libraries in Mitaka, one of the goals of the Newton cycle is to see the adoption of Oslo libraries in all projects. We will be providing a series of blogs to detail the walkthrough and reviews of existing projects for reference.

Other libraries include:

  • oslosphinx is a sphinx add-on library that provides theme and extension support for generating documentation with Sphinx. The Developer Documentation, Release Notes, a number of the OpenStack manuals including the Configuration Reference and now the Nova API Reference rely on this library.

  • oslotest is a helper library that provides base classes and fixtures for creating unit and functional tests.
  • oslo-cookiecutter is a project that creates a skeleton Oslo library from a set of templates.

4. Proposed or deprecated libraries

Some libraries fall outside of these categories, such as oslo.rootwrap. This was a mature library for handling fine filtering of shell commands to run as root. This is now deprecated in favor of oslo.privsep which is a mechanism for running selected python code with elevated privileges.

pylockfile is a legacy (and adopted) inter-process lock management library that was never used within OpenStack.

The oslo.version is an example of a proposed library at present to help in using python metadata to determine versioning.

The Oslo team is also evaluating what other common code may be suitable for an Oslo library.

The meaning behind the Oslo Name

Each OpenStack project has some reason behind the name. Oslo is in reference to the Oslo Peace Accords and “bringing peace” to the OpenStack project.

Oslo is also the capital of Norway, and in Norway you can find Moose. The moose is our project mascot.

Writing and testing unit tests in OpenStack

The following outlines an approach of identifying and improving unit tests in an OpenStack project.

Obtain the source code

You can obtain a copy of current source code for an OpenStack project at http://git.openstack.org. Active projects are categorized into openstack, openstack-dev, openstack-infra and stackforge.

NOTE: While you can find OpenStack projects on GitHub, these are just a mirror of the source repositories.

In this example I am going to use the Magnum project.

$ git clone git://git.openstack.org/openstack/magnum 
$ cd magnum

Run the current tests

The first step should be to run the current tests to verify the current code. This is to become familiar with the habit, especially if you may have made modifications and are returning to looking at your code. This will also create a virtual environment, which you will want to use later to test your changes.

$ tox -e py27

Should this fail, you may want to ensure all OpenStack developer dependencies are inplace on your OS.

Identify unit tests to work on

You can use the code coverage of unit tests to determine possible places to start adding to existing unit tests. The following command will produce a HTML report in the /cover directory of your project.

$ tox -e cover

This output will look similar to this example coverage output for Magnum. You can also produce a text based version with:

$ coverage report -m 

I will use this text version as a later verification.

Working on a specific unit test

Drilling down on any individual test file you will get an indication of code that does not have unit test coverage. For example in magnum/common/utils:

Once you have found a place to work with and you have identified the corresponding unit test file in the magnum/tests/unit sub-directory, in this example I am working on on magnum/tests/unit/common/test_utils.py, you will want to run this individual unit test in the virtual environment you previously created.

$ source .tox/py27/bin/activate
$ testr run test_utils -- -f

You can now start working on making your changes in whatever editor you wish. You may want to also work interactively in python initially to test and verify classes and methods especially if you are unfamiliar with how the code functions. For example, using the identical import found in test_utils.py for the test coverage I started with these simple checks.

(py27)$ python
Python 2.7.6 (default, Mar 22 2014, 22:59:56)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from magnum.common import utils
>>> utils.is_valid_ipv4('10.0.0.1') == True
True
>>> utils.is_valid_ipv4('') == False
True

I then created some appropriate unit tests for these two methods based on this interactive validation. These tests show that I not only test for valid values, I also test various boundary contains for invalid values including blank, character and out of range values of IP addresses.

    def test_valid_ipv4(self):
        self.assertTrue(utils.is_valid_ipv4('10.0.0.1'))
        self.assertTrue(utils.is_valid_ipv4('255.255.255.255'))

    def test_invalid_ipv4(self):
        self.assertFalse(utils.is_valid_ipv4(''))
        self.assertFalse(utils.is_valid_ipv4('x.x.x.x'))
        self.assertFalse(utils.is_valid_ipv4('256.256.256.256'))
        self.assertFalse(utils.is_valid_ipv4(
                         'AA42:0000:0000:0000:0202:B3FF:FE1E:8329'))

    def test_valid_ipv6(self):
        self.assertTrue(utils.is_valid_ipv6(
                        'AA42:0000:0000:0000:0202:B3FF:FE1E:8329'))
        self.assertTrue(utils.is_valid_ipv6(
                        'AA42::0202:B3FF:FE1E:8329'))

    def test_invalid_ipv6(self):
        self.assertFalse(utils.is_valid_ipv6(''))
        self.assertFalse(utils.is_valid_ipv6('10.0.0.1'))
        self.assertFalse(utils.is_valid_ipv6('AA42::0202:B3FF:FE1E:'))

After making these changes you want to run and verify your modified test works as previously demonstrated.

$ testr run test_utils -- -f
running=OS_STDOUT_CAPTURE=${OS_STDOUT_CAPTURE:-1} \
OS_STDERR_CAPTURE=${OS_STDERR_CAPTURE:-1} \
OS_TEST_TIMEOUT=${OS_TEST_TIMEOUT:-160} \
${PYTHON:-python} -m subunit.run discover -t ./ ${OS_TEST_PATH:-./magnum/tests/unit} --list  -f
running=OS_STDOUT_CAPTURE=${OS_STDOUT_CAPTURE:-1} \
OS_STDERR_CAPTURE=${OS_STDERR_CAPTURE:-1} \
OS_TEST_TIMEOUT=${OS_TEST_TIMEOUT:-160} \
${PYTHON:-python} -m subunit.run discover -t ./ ${OS_TEST_PATH:-./magnum/tests/unit}  --load-list /tmp/tmpDMP50r -f
Ran 59 (+1) tests in 0.824s (-0.016s)
PASSED (id=19)

If your tests fail you will see a FAILED message like. I find it useful to write a failing test intentionally just to validate the actual testing process is working.


${PYTHON:-python} -m subunit.run discover -t ./ ${OS_TEST_PATH:-./magnum/tests/unit}  --load-list /tmp/tmpsZlk3i -f
======================================================================
FAIL: magnum.tests.unit.common.test_utils.UtilsTestCase.test_invalid_ipv6
tags: worker-0
----------------------------------------------------------------------
Empty attachments:
  stderr
  stdout

Traceback (most recent call last):
  File "magnum/tests/unit/common/test_utils.py", line 98, in test_invalid_ipv6
    self.assertFalse(utils.is_valid_ipv6('AA42::0202:B3FF:FE1E:832'))
  File "/home/rbradfor/os/openstack/magnum/.tox/py27/local/lib/python2.7/site-packages/unittest2/case.py", line 672, in assertFalse
    raise self.failureException(msg)
AssertionError: True is not false
Ran 55 (-4) tests in 0.805s (-0.017s)
FAILED (id=20, failures=1 (+1))

Confirming your new unit tests

You can verify this has improved coverage percentage by re-running the coverage commands. I use the text based version as an easy way to see a decrease in the number of lines not covered.

Before

$ coverage report -m | grep "common/utils"
magnum/common/utils    273     94     76     38    62%   92-94, 105-134, 151-157, 208-211, 215-218, 241-259, 267-270, 275-279, 325, 349-384, 442, 449-453, 458-459, 467, 517-518, 530-531, 544
$ tox -e cover

After

$ coverage report -m | grep "common/utils"
magnum/common/utils    273     86     76     38    64%   92-94, 105-134, 151-157, 241-259, 267-270, 275-279, 325, 349-384, 442, 449-453, 458-459, 467, 517-518, 530-531, 544

I can see 8 lines of improvement which I can also verify if I look at the html version.

Before

After

Additional Testing

Make sure you run a full test before committing. This runs all tests in multiple Python versions and runs the PEP8 code style validation for your modified unit tests.

$ tox -e py27

Here are some examples of PEP8 problems with my improvements to the unit tests.

pep8 runtests: commands[0] | flake8
./magnum/tests/unit/common/test_utils.py:88:80: E501 line too long (88 > 79 characters)
./magnum/tests/unit/common/test_utils.py:91:80: E501 line too long (87 > 79 characters)
...
./magnum/tests/unit/common/test_utils.py:112:32: E231 missing whitespace after ','
./magnum/tests/unit/common/test_utils.py:113:32: E231 missing whitespace after ','
./magnum/tests/unit/common/test_utils.py:121:30: E231 missing whitespace after ','
...

Submitting your work

In order for your time and effort to be included in the OpenStack project there are a number of key details you need to follow that I outlined in contributing to OpenStack. Specifically these documents are important.

You do not have to be familiar with the procedures in order to look at the code, and even look at improving the code. You will need to follow the steps as outlined in these links if you want to contribute your code. Remember if you are new, the best access to help is to jump onto the IRC channel of the project you are interested in.

This example along with additions for several other methods was submitted (See patch). It was reviewed and ultimately approved.

References

Some additional information about the tools and processes can be found in these OpenStack documentation and wiki pages.

Contributing to OpenStack

Following my first OpenStack Summit in Vancouver 4/2015 it was time to become involved with contributing to OpenStack.

I have lurked around the mailing lists and several IRC channels for a few weeks and familiarized myself with OpenStack in varying forms including devstack, the free hosted Mirantis Express and the VM version, Ubuntu OpenStack, and even building my own 3 physical server cloud from second hand hardware purchased on eBay.

There are several resources available however I suggest you start with this concise presentation I attended at the summit by Adrian Otto on “7 Habits of Highly Effective Contributors” (slides, video).

You should also look at contributions from existing developers by looking at current code being submitted for review at https://review.openstack.org. I spent several weeks just looking at submissions, and I look at new submissions most days. While it does not always make sense (including a lot initially) its important to look at the full scope of all the projects. It is extremely valuable to look at how the review process works, how others comment on contributions, and look at the types of patches and code changes that are being contributed. There are a number of ways of not doing it right which can be discouraging when you first start contributing. The following links are vital to read, and re-read.

Individual projects also have various information, for example Magnum’s Ways to Contribute.

The benefit of observing for some time is you can be better prepared when you start to contribute. I was also new to how unit testing and automated testing worked in Python (about 7th on my list of known languages), and so learning about running OpenStack tests with tox and understanding the different OpenStack tox configs were valuable lessons, helped by feedback of OpenStack developers on the mailing list and IRC (If you have not looked at the 7 Habits presentation, now is a great time).

I took the time to find areas of interest and value which become more apparent after attending my first Design Summit. I even committed to assist in a design priority in the Magnum project as a result of my learning about how unit testing worked.

And if you write about your experiences another thing you can do is Add your blog to Planet OpenStack. I have received great feedback from the OpenStack community when writing about my first experiences.

MySQL HandlerSocket under Ubuntu

Starting with the great work of Yoshinori-san Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server and Golan Zakai who posted Installing Dena’s HandlerSocket NoSQL plugin for MySQL on Centos I configured and tested HandlerSocket under Ubuntu 10.04 64bit.

NOTE: This machine already compiles MySQL and Drizzle. You should refer to appropriate source compile instructions for necessary dependencies.

# Get Software
cd /some/path
export DIR=`pwd`
wget http://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
wget http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52.tar.gz
wget  http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52-linux-x86_64-glibc23.tar.gz

# Install MySQL Binaries
tar xvfz mysql-5.1.52-linux-x86_64-glibc23.tar.gz
ln -s mysql-5.1.52-linux-x86_64-glibc23 mysql5152
rm -rf mysql5152/mysql-test
rm -rf mysql5152/sql-bench
cd mysql5152
scripts/mysql_install_db
bin/mysqld_safe &

# Install MySQL Source
tar xvfz mysql-5.1.52.tar.gz

# Install HandlerSocket
tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
cd ahiguti-HandlerSocket-Plugin-for-MySQL-d032ec0/
./autogen.sh
./configure --with-mysql-source=${DIR}/mysql-5.1.51 --with-mysql-bindir=${DIR}/mysql5152/bin
make
sudo make install

# libtool: install: /usr/bin/install -c .libs/handlersocket.lai /home/rbradfor/projects/handlersocket/mysql5152/lib/plugin/handlersocket.la

# Install the Perl dependency
cd perl-Net-HandlerSocket
perl Makefile.PL
Checking if your kit is complete...
Warning: the following files are missing in your kit:
	lib/HandlerSocket.pm
Please inform the author.
Writing Makefile for Net::HandlerSocket
# For some reason I had to run this twice, after error I check, saw nothing wrong, ran a second time and it worked ???
perl Makefile.PL
Writing Makefile for Net::HandlerSocket
make
sudo make install

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.so
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.bs
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket.pm
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket/Pool.pm
Installing /usr/local/man/man3/Net::HandlerSocket.3pm
Appending installation info to /usr/local/lib/perl/5.10.1/perllocal.pod

cp lib/Net/HandlerSocket.pm blib/lib/Net/HandlerSocket.pm
cp lib/Net/HandlerSocket/Pool.pm blib/lib/Net/HandlerSocket/Pool.pm
/usr/bin/perl /usr/share/perl/5.10/ExtUtils/xsubpp  -typemap /usr/share/perl/5.10/ExtUtils/typemap  HandlerSocket.xs > HandlerSocket.xsc && mv HandlerSocket.xsc HandlerSocket.c
Please specify prototyping behavior for HandlerSocket.xs (see perlxs manual)
g++ -fPIC -c  -I. -I../libhsclient -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -O3 -Wall -Wno-unused   -DVERSION="0.01" -DXS_VERSION="0.01" -fPIC "-I/usr/lib/perl/5.10/CORE"   HandlerSocket.c
Running Mkbootstrap for Net::HandlerSocket ()
chmod 644 HandlerSocket.bs
rm -f blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
LD_RUN_PATH="/usr/local/lib" g++ -fPIC  -shared -O2 -g -L/usr/local/lib -fstack-protector HandlerSocket.o  -o blib/arch/auto/Net/HandlerSocket/HandlerSocket.so 	
	   -lhsclient  	

chmod 755 blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
cp HandlerSocket.bs blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
chmod 644 blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
Manifying blib/man3/Net::HandlerSocket.3pm

Now all built, it’s as simple as the following to install:

${DIR}/mysql5152/bin/mysql -uroot
mysql> INSTALL PLUGIN HandlerSocket SONAME 'handlersocket.so';
mysql> SHOW PLUGINS;
+---------------+----------+----------------+------------------+---------+
| Name          | Status   | Type           | Library          | License |
+---------------+----------+----------------+------------------+---------+
| binlog        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| partition     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| ARCHIVE       | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| BLACKHOLE     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| CSV           | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| FEDERATED     | DISABLED | STORAGE ENGINE | NULL             | GPL     |
| MEMORY        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| InnoDB        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MyISAM        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MRG_MYISAM    | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| handlersocket | ACTIVE   | DAEMON         | handlersocket.so | BSD     |
+---------------+----------+----------------+------------------+---------+
11 rows in set (0.00 sec)

However, I found it not actually running even after restarting MySQL. You need the following configuration

cd ${DIR}/mysql5152
echo "[mysqld]
plugin-load=handlersocket.so
loose_handlersocket_port = 9998 # the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999 # the port number to bind to (for write requests)
loose_handlersocket_threads = 16 # the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1 # the number of worker threads (for write requests)" >> my.cnf

After restarting you get the following confirmation.

mysql>SHOW PROCESSLIST;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |
| 18 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | SHOW PROCESSLIST |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
18 rows in set (0.00 sec)

My Test Data

USE test
CREATE TABLE user (
  user_id INT UNSIGNED NOT NULL,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(user_id)
) ENGINE=InnoDB;

INSERT INTO user (user_id,name, email) VALUES
(100,'Mickey Mouse','[email protected]'),
(101,'Marvin the Martian','[email protected]'),
(102,'Spiderman','[email protected]');

SELECT user_id,name,email FROM user WHERE user_id=101;
+---------+--------------------+---------------------+
| user_id | name               | email               |
+---------+--------------------+---------------------+
|     101 | Marvin the Martian | [email protected] |
+---------+--------------------+---------------------+

My test retrieval program is basically unchanged from the original post.

$ cat retrieve.pl
#!/usr/bin/perl

use strict;
use warnings;
use Net::HandlerSocket;

#1. establishing a connection
my $args = { host => '10.0.0.6', port => 9998 };
my $hs = new Net::HandlerSocket($args);

#2. initializing an index so that we can use in main logics.
 # MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
    'name,email,created');
die $hs->get_error() if $res != 0;

#3. main logic
 #fetching rows by id
 #execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_namet$user_emailt$createdn";
}

#4. closing the connection
$hs->close();

If your HandlerSocket is not running you will see:

$ perl retrieve.pl
FATAL_EXIT: getaddrinfo failed: ip_to_remote_host:9998
$ telnet 10.0.0.6 9998
Trying 10.0.0.6...
telnet: Unable to connect to remote host: Connection refused

When working

$ telnet 10.0.0.6 9998
Trying 10.0.0.6...
Connected to 10.0.0.6.
Escape character is '^]'.
P	0	test	user	PRIMARY	name,email,created
0	1
0	=	1	101
0	3	Marvin the Martian	[email protected]	2010-11-05 13:11:34


$ perl retrieve.pl
Marvin the Martian	[email protected]	2010-11-05 13:11:34

Again, great work by Yoshinori-san to have this code as production deployable. Now to run some real benchmarks.

Installing MySQL on Oracle Enterprise Linux

One of the significant benefits of MySQL is it’s ease of use. Generally already installed on most Linux systems, MySQL can be installed by a single command if not yet present. On Oracle Enterprise Linux 5.4 you can use the following commands to check for MySQL, configure your yum repository and install MySQL.

# Check if already installed
$ rpm -qa | grep -i mysql

# Configure yum repository on new server
$ su -
$ cd /tmp
$ wget http://public-yum.oracle.com/public-yum-el5.repo
$ sed -e "s/enabled=0/enabled=1/" public-yum-el5.repo > /etc/yum.repos.d/public-yum-el5.repo

# Install MySQL
$ yum install -y mysql-server mysql

# Start and test MySQL Instance
$ /etc/init.d/mysqld start
$ mysql -uroot -e "SELECT VERSION()"

+-----------+
| VERSION() |
+-----------+
| 5.0.77    |
+-----------+

You can find more information about the Oracle public yum repository at http://public-yum.oracle.com You will also note that the version installed is 5.0. The current GA version of MySQL is 5.1, however Red Hat is notorious for taking time to update repositories more regularly. You can always use more current rpm files available at the MySQL downloads page.

If you want MySQL to be configured to automatically start and stop on your server you need to run the following command.

$ chkconfig mysqld on
$ chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

You can link to this post with the short url rb42.com/oel-install-mysql

Related Posts

Getting started with Ruby and Sinatra

I’ve been doing a little work with Ruby, starting with some XHTML parsing with Nokogiri. I’ve just created my first web page using Sinatra.

While the instructions makes it look simple, it was a little more complex due a package dependency error.

Installation

$ sudo gem install sinatra
Password:
Successfully installed rack-1.2.1
Successfully installed sinatra-1.0
2 gems installed
Installing ri documentation for rack-1.2.1...
Installing ri documentation for sinatra-1.0...
Installing RDoc documentation for rack-1.2.1...
Installing RDoc documentation for sinatra-1.0...

Smoking it

Following the 5 line example on the home page, didn’t produce the result I expected.

 ruby rb42.rb
/Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138:in `union': can't convert Array into String (TypeError)
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require'
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/request.rb:1
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require'
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/showexceptions.rb:3
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	 ... 7 levels...
	from /Library/Ruby/Gems/1.8/gems/sinatra-1.0/lib/sinatra.rb:4
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `require'
	from rb42.rb:2

Some Googling found a reference to a compatibility problem. While the syntax given on the post wasn’t correct, it was sufficient for me to find a correct solution.

$  sudo gem uninstall rack
Remove executables:
	rackup

in addition to the gem? [Yn]  y
Removing rackup

You have requested to uninstall the gem:
	rack-1.2.1
sinatra-1.0 depends on [rack (>= 1.0)]
If you remove this gems, one or more dependencies will not be met.
Continue with Uninstall? [Yn]  y
Successfully uninstalled rack-1.2.1

$ sudo gem install rack --version '1.2.0'
Successfully installed rack-1.2.0
1 gem installed
Installing ri documentation for rack-1.2.0...
Installing RDoc documentation for rack-1.2.0...

And now expected results starting then viewing http://localhost:4567/

$ ruby rb42.rb
== Sinatra/1.0 has taken the stage on 4567 for development with backup from Mongrel

Environment

$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.5.8
BuildVersion:	9L31a
$ ruby --version
ruby 1.8.6 (2009-06-08 patchlevel 369) [universal-darwin9.0]
$ gem --version
1.3.6

Getting Nokogiri working under Mac OS X

The official Installation documentation states:

sudo port install libxml2 libxslt
sudo gem install nokogiri

however I found this not to work for me. The following did work.

$ sudo port install libxml2 libxslt

$ sudo gem install nokogiri
ERROR:  could not find nokogiri locally or in a repository

$ sudo gem sources -a http://gems.github.com
Bulk updating Gem source index for: http://gems.github.com
Updating metadata for 1 gems from http://gems.rubyforge.org

$ sudo gem install nokogiri
ERROR:  could not find nokogiri locally or in a repository

$ sudo gem update --system
Updating RubyGems...
Attempting remote update of rubygems-update
Successfully installed rubygems-update-1.3.6
1 gem installed
Installing ri documentation for rubygems-update-1.3.6...
Installing RDoc documentation for rubygems-update-1.3.6...
Updating version of RubyGems to 1.3.6
Installing RubyGems 1.3.6
RubyGems 1.3.6 installed

$ sudo gem install nokogiri
Building native extensions.  This could take a while...
Successfully installed nokogiri-1.4.2
1 gem installed
Installing ri documentation for nokogiri-1.4.2...
No definition for parse_memory
No definition for parse_file
No definition for parse_with
No definition for get_options
No definition for set_options
Installing RDoc documentation for nokogiri-1.4.2...
No definition for parse_memory
No definition for parse_file
No definition for parse_with
No definition for get_options
No definition for set_options

I’m not sure why I got those additional errors, however I was able to confirm a valid installation with.

$ irb
>> require ‘nokogiri’
=> true
>> quit

ImageMagick on Mac OS X

Wanting to do some image manipulation I realized my Linux scripts don’t run under Mac OS X, as ImageMagick is not installed via my MacPorts.

However installation failed:

$ sudo port install imagemagick
--->  Computing dependencies for ImageMagick
--->  Verifying checksum(s) for xorg-libX11
Error: Checksum (md5) mismatch for libX11-1.3.3.tar.bz2
Error: Checksum (sha1) mismatch for libX11-1.3.3.tar.bz2
Error: Checksum (rmd160) mismatch for libX11-1.3.3.tar.bz2
Error: Target org.macports.checksum returned: Unable to verify file checksums
Error: The following dependencies failed to build: xorg-libXext xorg-libX11 xorg-libXt xorg-libsm xorg-libice
Error: Status 1 encountered during processing.
Before reporting a bug, first run the command again with the -d flag to get complete output.

Figuring that some of my packages may require upgrade:

$ sudo port selfupdate
sudo port -d upgrade outdated

The problem is this all failed. Turning to the FAQ it seemed what I needed to do was remove and re-install the offending package receiving the checksum error via the following syntax.

$ sudo port clean --all 
$ sudo port install 

It seemed I had to do this for several packages manually however in the end removing and installing a number of packages addressed the problem and now ImageMagick is happily running on Mac OS X

bash-3.2$ sudo port clean --all xorg-libX11
--->  Cleaning xorg-libX11
bash-3.2$ sudo port install xorg-libX11
---->  Computing dependencies for ImageMagick
--->  Verifying checksum(s) for xorg-libX11
Error: Checksum (md5) mismatch for libX11-1.3.3.tar.bz2
Error: Checksum (sha1) mismatch for libX11-1.3.3.tar.bz2
Error: Checksum (rmd160) mismatch for libX11-1.3.3.tar.bz2
Error: Target org.macports.checksum returned: Unable to verify file checksums
Error: The following dependencies failed to build: xorg-libXext xorg-libX11 xorg-libXt xorg-libsm xorg-libice
Error: Status 1 encountered during processing.
Before reporting a bug, first run the command again with the -d flag to get complete output.
bash-3.2$ sudo port clean --all libX11
Error: Port libX11 not found
Before reporting a bug, first run the command again with the -d flag to get complete output.
bash-3.2$ sudo port clean --all xorg-libX11
--->  Cleaning xorg-libX11
bash-3.2$ sudo port install xorg-libX11

Installing Ubuntu Desktop 10.04 with LVM

With a new quad core desktop with 8GB RAM & 1TB HDD I wanted to install the Ubuntu desktop version using LVM. This is not possible with the “Desktop CD”. You need to use the “alternative CD” which will easily allow you to configure LVM via a text/cursors installation and also give you a deskop GNOME environment. The “Server CD” also gives you LVM options, but no GUI.

While there are complicated instructions on how to configure/setup LVM with various versions of Ubuntu, all you need with Ubuntu 10.04 is the right CD.

While installing I also read up on two tips that I found of benefit.

  • During installation of the base system, package unpacking and setup messages are redirected to tty4. You can access this terminal by pressing Left Alt+F4; get back to the main installer process with Left Alt+F1.
  • You can get a terminal window easily during installation by switching to the second virtual console by pressing Left Alt+F2

Installation was rather seamless, the only annoyance the cursors interface not displaying clearly on my Dell 2407WFP monitor during the installation process. Monitor works fine with installed GUI at 1920×1200.

Getting started with Cassandra

With the motivation from today’s public news on Twitter’s move from MySQL to Cassandra, my own skills desire following in-depth discussions at last November’s Open SQL Camp to consider Cassandra and yesterday’s discussion with a new client on persistent key-value store products, today I download installed and configured for the first time. Not that today’s news was unexpected, if you follow the Twitter Engineering Open Source projects you would have seen Cassandra as well as other products being used or evaluated by Twitter.

So I went from nothing to a working Cassandra node in under 5 minutes. This is what I did.

  1. While I knew this was an Apache project, a Google Search yields for me the 3rd link for the The Apache Cassandra Project at http://incubator.apache.org/cassandra/. Congrats for Cassandra now a top level Apache Project. This url will update soon.
  2. Download Cassandra. Hard to miss with a big green button on home page. Current version is 0.5
  3. I read Getting Started, which is the 3rd top level link on menu after Home and Download. Step 1 is picking a version which I’ve already done, Step 2 is Running a single node.
  4. The Getting Started indicated a problem on Mac OS X for the required minimum Java version. I was installing on Mac OS X 10.5 and CentOS 5.4. I’ve experienced this Java 6 default path issue before. Set my JAVA_HOME and PATH accordingly (after I updated the wiki with correct value)
  5. I extracted the tar file, changed to the directory and took at look at the README.txt file. Yes, I always check this first with any software and relevant because it includes valuable instructions on creating the default data and log directories.
  6. Start with bin/cassandra -f. No problems!
  7. I then followed the instructions from the link in Step 2 with the CassandraCli. This tests and confirms the installation is operational.

Ok, a working environment. I’ve now installed on a second machine and tested however I now need to configure the cluster, and the documentation is not as straightforward. Time to try out Google again.

On a side note, this is one reason why I love Open Source. I followed the instructions online and found a mistake in the Mac OS X path, I simply registered and corrected providing the benefit of my experience for the next reader(s).

You may also like to view future posts including.

Speaking at MySQL UC 2010

My talk on 10x performance improvements – A case study has just been approved for the 2010 MySQL Conference. This will be my 5th straight year speaking at the MySQL conferences. For those in Europe wanting a sneak peek I am also speaking at FOSDEM 2010 in Brussels on Feb 7th where I’ll be giving an abridged version.

As an independent MySQL consultant, my work generally covers performance tuning and scalability and sometimes database architecture. Often however my work involves a review of a given problem and recommendations (immediate, short and long term). I’m rarely involved in the full implementation and generally do not see the full fruits of the proposed work.

Recently however I was able to work with a client, first in resolving critical performance issues and then in a review of the application architecture and MySQL environment, provide recommendations and also helping internal resources in the successful implementation. The result was a very successful engagement and an ideal case study on a strategy for tackling improving performance of an application using MySQL.

With an existing environment that included a MySQL master, 3 database slaves and 6 web servers this application provided the grounds of a well sized configuration and the need for greater availability and scalability.

The Call for Papers for the 2010 MySQL conference is still open. If you would like to share your experiences with MySQL submit your talk today.


O'Reilly MySQL Conference & Expo 2010

NoSQL options

The NoSQL event in New York had a number of presentations on non relational technologies including of Hadoop, MongoDB and CouchDB.

Coming historically from a relational background of 20 years with Ingres, Oracle and MySQL I have been moving my focus towards non relational data store. The most obvious and well used today is memcached, a non persistent distributed key/value pair store. There are a number of persistent key/value stores in the marketplace, Tokyo Cabinet, Project Voldemort and Redis to name a few.

My list of data store products helps to identify the complex name space of varying products that now exist. A trend is towards schema less solutions, the ability to better manage dynamically typed/formatted information and the Agile Methodology release approach is simply non achievable in a statically type relational database table/column structure. The impact of constant ALTER TABLE commands in a MySQL database makes your production system unusable.

In a highly distribute online and increasing offline operation, fault tolerance and data synchronization and eventual consistency are required features in complex topologies such as multi-master.

I advise and promote a technology agnostic solution when possible. With the use of an API this is actually achievable, however in order to use a variety of backend data store products, one must consider the design patterns for optimal management. Two factors to support a highly distributed data set are no joins and minimal transactional semantics. The Facebook API is a great example, where there are no joins for their MySQL Relational backend. The movement back to a logical and non-normalized schema, or move towards a totally schemaless solution do require great though in the architectural concepts of your application.

Ultimately feature requirements will dictate the relative strengths and weaknesses of products. Full text search is a good example. CouchDB provides native support via Lucene. Another feature I like of couchDB is its append only data mode. This makes durability easy, and auto-recovery after crash a non issue, another feature a transactional relational database can not achieve.

With a 2 day no:sql(east) conference this month, there is definitely greater interest in this space.

Monitoring MySQL – The error log

It is important that you monitor the MySQL error log. There are a few different options available for defining the details of the log. If not specified the default is [datadir]/[hostname].err. This is not an ideal location for 2 reasons.

First, a correctly configured MySQL datadir will have permissions only for the mysql user, and this is generally restrictive access to the user only and no group or world permissions. Other users/groups should have limited access to the mysql error log.

Second, the datadir is for data, not logs, especially logs that can potentially fill file systems. I am referring here to more then just the error log.

I would recommend you create a separate directory for MySQL logs such as the error, slow and general logs. An example I implement for single installation environments using Linux mysql packages is:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
chmod 750 /var/log/mysql

There does not seem to be a consensus over whether to include the hostname or not in the error log filename. My preference is to not include. I would rather the filename to be consistent across multiple servers. The argument is what about when consolidating logs from multiple servers. I discount this because you have to connect to the server to retrieve logs, create a sub directory of that hostname for consolidated logs.

With Linux distributions you may not find log files where you expect. Ubuntu packages for example has the log going to syslog. While the theory is to make system logging and monitoring easier, it makes MySQL specific monitoring more difficult. You also suffer a logrotate problem where you may only have 7 days of log. I prefer to have access to all historical MySQL log information.

The best choice is to define the error log with log-error, in both the [mysqld_safe] and [mysqld] section of your servers my.cnf

[mysqld_safe]
log-error=/var/log/mysql/error.log

[mysqld]
log-error=/var/log/mysql/error.log

In MySQL 5.1 you have the luxury of different output sources, FILE, TABLE or BOTH for the general log and the slow log with –log-output. No option exists for the error log.

Other my.cnf options to be aware of include:

  • log-warnings | skip-log-warnings
  • syslog | skip-syslog

There is generally also lacking in the standard monitoring products/plugins that present MySQL status information. In my monitoring MySQL solutions I provide a line count of the MySQL error log, so that a delta can be easily detected and then reviewed more proactively.

One issue with a recent client is the lack of access to the physical box by different parties and therefore the lack of access to the log. The identification that something needs to be viewed, then the ability to be able to view is an important problem to be solved.

References

Some other references for MySQL error log monitoring.

Where can you find MySQL Events?

As a frequent traveler for my MySQL consulting (last 4 weeks were Sydney, San Francisco, New York and Vancouver), I like to keep abreast of any local tech event that includes MySQL that I may be able to attend.

Now there is a consolidated location that you can use, the Open Source Events Calendar. Kudos to the MySQL Community team members Lenz Grimmer and Giuseppe Maxia who have put this together.

We need your help. If you have a local event, please submit your event request. This projects needs the support of all.

You will also find valuable conference information including dates for close of proposals. A great tool for scheduling your upcoming conference year.

InnoDB I_S.tables.table_rows out by a factor of 100x

I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.

Today I found that the figures varied on one table from 10x to 100x wrong.

Before performing an ALTER I always verify sizes for reference.

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb   | data_mb    | index_mb  | today      |
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |     208993 |           7475 |  1491.5312 |  1490.0156 |    1.5156 | 2009-09-09 |

mysql> alter table s_a modify col VARCHAR(255);
Query OK, 23471 rows affected (4 min 26.23 sec)

After

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |    2407063 |            629 |  1447.0312 |  1445.5156 |    1.5156 | 2009-09-09 |

I was so caught out by this. The table reported 200k rows, but the alter returned 23k, that’s like 10x out.
I ran my query again, and the second time I got.

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |      21813 |          69487 |  1447.0312 |  1445.5156 |    1.5156 | 2009-09-09 |

This is closer to what I’d expected, 22k verses 23k.

I have to say, while I have always treated the data and index size as accurate, I now how little confidence in the table_rows any more.

MySQL DML stats per table

MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.

You can achieve this with tools such as MySQL Proxy and mk-query-digest, however there is actually a very simple solution that requires no additional tools.
The following 1 line Linux command (reformatted for ease of reading) gave me exactly what I wanted, and it had ZERO impact on the database.

$ mysqlbinlog /path/to/mysql-bin.000999 |  
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | 
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  
   sed -e "s/t/ /g;s/`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" |  
   sort | uniq -c | sort -nr


  33389 update e_acc
  17680 insert into r_b
  17680 insert into e_rec
  14332 insert into rcv_c
  13543 update e_rec
  10805 update loc
   3339 insert into r_att
   2781 insert into o_att
...

Granted the syntax could do with some regex improvements, but in 2 minutes I was able to deduce some approximate load. The mysqlbinlog command also gives option to retrieve data for a given time period, so it is very easy to get these statistics on a per hour basis.

Sometimes the most simple options are right in front of you, just just need to strive to find the simplest solution.

Has your blog been hacked?

While not a MySQL topic, as most of my readers view my MySQL Blog, my WordPress blog has been hacked? Has yours?

Like many, I’m sure you may have read about it like at WordPress blogs under attack from hack attack but I was surprised when my custom permlinks did not work.

Being surprised I looked at Administrator accounts, and I found that there was one more number then being displayed in the list. I had to dig into the database to find the problem.

mysql> select * from wp_users where ID in (select user_id from wp_usermeta where meta_key = 'wp_capabilities' and meta_value like '%admin%');
+-----+-------------+------------------------------------+---------------+------------------------------+---------------------------+---------------------+---------------------+-------------+--------------+
| ID  | user_login  | user_pass                          | user_nicename | user_email                   | user_url                  | user_registered     | user_activation_key | user_status | display_name |
+-----+-------------+------------------------------------+---------------+------------------------------+---------------------------+---------------------+---------------------+-------------+--------------+
|   1 | admin       | $P$BHZFK/prDplb/W/024yrH49JvAmmCE. | ronald        | [email protected] | http://ronaldbradford.com | 2005-11-21 23:43:47 |                     |           0 | Ronald       |
| 127 | ronald      | $P$B..e75VtFsv9bUGj5H5NTiXXPQIitr1 | ronald        | [email protected]    | http://ronaldbradford.com | 2009-02-22 20:13:33 |                     |           0 | ronald       |
| 133 | ChaseKent87 | $P$Bl8cVSzBums33Md6u2PQtUVY2PPBHK. | chasekent87   |                              |                           | 2009-09-05 06:36:59 |                     |           0 | ChaseKent87  |
+-----+-------------+------------------------------------+---------------+------------------------------+---------------------------+---------------------+---------------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> delete from wp_users where ID=133;
mysql> delete from wp_usermeta where user_id=133;

However the damage has been done, and an update to the recommend 2.8.4 is unlikely to fix the data corruption.

Being a good DBA I have a nightly backup of my database. Being a diligent system administrator, I have not 1 copy, by 3 copies of my system, one on my web site and two offsite.

The problem is I don’t keep older backups of my data, only a day old version.

What do you monitor in MySQL?

If you are unfamiliar with what to monitor in MySQL, starting with looking at what popular Monitoring products monitor. For example, the following is the list of MySQL Cacti Plugin measurements.

Innodb Buffer Pool Activity

  • Pages Created
  • Pages Written
  • Pages Read

Innodb Buffer Pool Pages

  • Pool Size
  • Database Pages
  • Free Pages
  • Modified Pages

Inoodb File I/O

  • File Reads
  • Files Writes
  • Log Writes
  • File Fsyncs

Innodb Pending I/O

  • Aio Log Ios
  • Aio Sync ios
  • Buffer Pool Flushes
  • Chkp Writes
  • Ibuf Aio Reads
  • Log Flushes
  • Log Writes
  • Normal Aio Reads
  • Normal Aio Writes

Innodb Insert Buffer

  • Inserts
  • Merged
  • Merges

Innodb Log

  • Log Buffer Size
  • Log Bytes Written
  • Log Bytes Flushed
  • Unflushed Log

Innodb Row Operations

  • Rows Read
  • Rows Deleted
  • Rows Updated
  • Rows Inserted

Innodb Semaphores

  • Spin Rounds
  • Spin Waits
  • OS Waits

Innodb Transactions

  • Innodb Transactions
  • Current Transactions
  • History List
  • Read Views

MySQL Binary/Relay Logs

  • Binlog Cache use
  • Binlog Cache Disk Use
  • Binary Log Space
  • Relay Log Space

MySQL Command Counters

  • Questions
  • SELECT
  • DELETE
  • INSERT
  • UPDATE
  • REPLACE
  • LOAD
  • DELETE MULTI
  • INSERT SELECT
  • UPDATE MULTI
  • REPLACE SELECT

MySQL Connections

  • Max Connections
  • Max Used Connections
  • Aborted Clients
  • Aborted Connects
  • Threads Connected
  • Connections

MySQL Files and Tables

  • Table Cache
  • Open Tables
  • Open Files
  • Opened Tables

MySQL Network Traffic

  • Bytes Received
  • Bytes Sent

MySQL Processlist

  • State Closing Tables
  • State Copying to Tmp Table
  • State End
  • State Freeing Items
  • State Init
  • State Locked
  • State Login
  • State Preparing
  • State Reading From Net
  • State Sending Data
  • State Sorting Result
  • State Statistics
  • State Updating
  • State Writing to Net
  • State None
  • State Other

MySQL Query Cache

  • Queries In Cache
  • Hits
  • Inserts
  • Not Cached
  • Lowmem Prunes

MySQL Query Cache Memory

  • Query Cache Size
  • Free Memory
  • Total Blocks
  • Free Blocks

MySQL Replication

  • Slave Running
  • Slave Stopped
  • Slave Lag
  • Slave Open Temp Tables
  • Slave Retried Transactions

MySQL Select Types

  • Select Full Join
  • Select Full Range Join
  • Select Range
  • Select Range Check
  • Select Scan

MySQL Sorts

  • Sort Rows
  • Sort Range
  • Sort Merge Passes
  • Sort Scan

MySQL Table Locks

  • Table Locks Immediate
  • Table Locks Waited
  • Slow Queries

MySQL Temporary Objects

  • Created Tmp Tables
  • Created Tmp Disk Tables
  • Created Tmp Files

MySQL Threads

  • Thread Cache Size
  • Threads Created

SQL Analysis with MySQL Proxy – Part 2

As I outlined in Part 1 MySQL Proxy can be one tool for performing SQL analysis. The impact with any monitoring is the art of monitoring will affect the results, in this case the performance. I don’t recommend enabling this level of detailed monitoring in production, these techniques are designed for development, testing, and possibly stress testing.

This leads to the question, how do I monitor SQL in production? The simple answer to this question is, Sampling. Take a representative sample of your production system. The implementation of this depends on many factors including your programming technology stack, and your MySQL topology.

If for example you are using PHP, then defining MySQL proxy on a production system, and executing firewall rules to redirect incoming 3306 traffic to 4040 for a period of time, e.g. 2 seconds can provide a wealth of information as to what’s happening on the server now. I have used this very successfully in production as an information gathering an analysis tool. It is also reasonably easy to configure, execute and the impact on any failures for example are minimized due to the sampling time.

If you run a distributed environment with MySQL Slaves, or many application servers, you can also introduce sampling to a certain extent as these specific points, however like scaling options, it is key to be able to handle and process the write load accurately.

Another performance improvement is to move processing of the gathered information in MySQL proxy to a separate thread or process, removing this work from the thread execution path and therefore increasing the performance. I’m interested to explore the option of passing this information off to memcached or gearman and having MySQL proxy simply capture the packet information and distributing the output. I have yet to see how memcached and/or gearman integrate with the Lua/C bindings. If anybody has experience or knowledge I would be interested to know more.

It is interesting to know that Drizzle provides a plugin to send this level of logging information to gearman automatically.

SQL query analysis with MySQL Proxy

Long before there was the official Query Analyzer (QUAN), a component of MySQL Enterprise, SQL analysis was possible using MySQL Proxy.

The following is an introduction to logging and query analysis with MySQL Proxy.

Get MySQL Proxy

You need to first download MySQL Proxy. In this example I am using the Linux RHEL5 64bit OS and Version 0.7.2

$ wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/
$ tar xvfz mysql-proxy-0.7.2-linux-rhel5-x86-64bit.tar.gz
$ ln -s mysql-proxy-0.7.2-linux-rhel5-x86-64bit mysql-proxy
$ export PATH=`pwd`/mysql-proxy/sbin:$PATH
$ mysql-proxy --help-all

Pre-requisites

MySQL Proxy uses TCP/IP, so it is important you connect via the actual hostname. You should first confirm this, as appropriate MySQL permissions may be necessary. For example:

$ mysql -h`hostname` -u -p

On confirmation this works, you can then connect directly to the proxy

$ mysql -h`hostname` -P4040 -u -p

Logging

$ cd mysql-proxy/share/doc/mysql-proxy/
$ wget -O log.lua http://ronaldbradford.com/mysql-dba/mysql-proxy/log.lua
$ mysql-proxy --proxy-lua-script=share/doc/mysql-proxy/log.lua &
$ tail -f mysql.log

This script is based on simple query logging which requires a modification to work in more current versions of MySQL proxy.

$ mysql -hhostname -P4040 -u -p
mysql>  SELECT host,user,password FROM mysql.user;
mysql>  SELECT table_schema,COUNT(*) FROM information_schema.tables GROUP BY table_schema;
mysql>  SELECT NOW(), SLEEP(3);
mysql>  EXIT
$ cat mysql.log
2009-09-02 17:15:01     58 -- select @@version_comment limit 1
2009-09-02 17:16:15     58 -- SELECT host,user,password FROM mysql.user
2009-09-02 17:16:30     58 -- SELECT table_schema,COUNT(*) FROM information_schema.tables GROUP BY table_schema
2009-09-02 17:16:39     58 -- SELECT NOW(), SLEEP(3)

Query Analysis

Restart proxy with the histogram.lua sample provided.

$ mysql-proxy --proxy-lua-script=share/doc/mysql-proxy/histogram.lua &

Connect and run some queries.

$ mysql -hhostname -P4040 -u -p
mysql>  SELECT host,user,password FROM mysql.user;
mysql>  SELECT table_schema,COUNT(*) FROM information_schema.tables GROUP BY table_schema;
mysql>  SELECT NOW(), SLEEP(3);

While connected to the proxy, you can now review data from two pseudo tables.

mysql>  SELECT * FROM histogram.tables;
mysql>  SELECT * FROM histogram.queriesG
mysql>  DELETE FROM histogram.tables;
mysql>  DELETE FROM histogram.queries;

mysql> SELECT * FROM histogram.tables;
+---------------------------+-------+--------+
| table                     | reads | writes |
+---------------------------+-------+--------+
| information_schema.tables |     1 |      0 |
| mysql.user                |     1 |      0 |
+---------------------------+-------+--------+

mysql> SELECT * FROM histogram.queries;
+--------------------------------------------------------------------------------------------------+-------+----------------+----------------+
| query                                                                                            | count | max_query_time | avg_query_time |
+--------------------------------------------------------------------------------------------------+-------+----------------+----------------+
| SELECT @@version_comment LIMIT ?                                                                 |     1 |            300 |            300 |
| SELECT `table_schema` , COUNT( * ) FROM `information_schema` . `tables` GROUP BY `table_schema`  |     1 |           1822 |           1822 |
| SELECT `host` , `user` , `password` FROM `mysql` . `user`                                        |     1 |            494 |            494 |
| SELECT NOW( ) , SLEEP( ? )                                                                       |     1 |        3000735 |        3000735 |
+--------------------------------------------------------------------------------------------------+-------+----------------+----------------+

Moving forward

The power is that with Lua you have the flexibility to write your own logging. Some improvements to these scripts could be.

  • Add the query time, number of rows, and result set size to the logging
  • Be able to sort histogram results or see top percentile. Being able to copy data into real tables would enable any level of analysis
  • Combine the logging and histogram scripts
  • Enable global enable/disabling of logging with SET GLOBAL commands
  • Support variable length IN queries, those that pass multiple values, so you end up with a subset of all queries
  • Provide a actual query example, making it easy to do a QEP. For normalized queries you need to do additional work to find values.
  • The histogram does not support the C API multi query functionality, where multiple queries can be passed to the server at one time. The problem is there is no way to time the individual queries.

Read on in SQL Analysis with MySQL Proxy – Part 2.

References

A good introduction document
MySQL Proxy – From architecture to implementation – OSCON 2008

Seeking public data for benchmarks

I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.

You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.

Of course, the data is only the starting point, having representative transactions and queries to execute and a framework to execute and a reporting module are also necessary. The introduction of Lua into Sysbench may now be a better option then my tool of choice mybench which I use simply because I can configure, write and deploy generally for a client in under 1 hour.

If anybody has other good references to free public data that’s easily loadable into MySQL please let me know.

Setting up a Virtual IP address (VIP)

These instructions are for CentOS/Redhat Linux distributions.

1. Identify your current NIC’s and IP addresses in use.

$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:30:48:98:9C:A6
          inet addr:192.168.53.201  Bcast:192.168.53.255  Mask:255.255.255.0
          inet6 addr: 0080::230:48ff:fe98:9ca6/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:6159779 errors:0 dropped:0 overruns:0 frame:0
          TX packets:6137085 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:1158210510 (1.0 GiB)  TX bytes:541617489 (516.5 MiB)
          Memory:e8000000-e8020000

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:3791468208 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3791468208 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:740660664181 (689.7 GiB)  TX bytes:740660664181 (689.7 GiB)

Determine which NIC you want the VIP on, in this case eth0 (the private address). We want to add the address 192.168.53.220

$  echo "DEVICE=eth0
BOOTPROTO=static
BROADCAST=192.168.53.255
IPADDR=192.168.53.220
NETMASK=255.255.255.0
NETWORK=192.168.53.0
ONBOOT=yes" > /etc/sysconfig/network-scripts/ifcfg-eth0:0
$ /etc/sysconfig/network-scripts/ifup-aliases eth0

Check your /sbin/ifconfig and now you should have a virtual IP address.

$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 99:30:48:98:9C:A6
          inet addr:192.168.53.201  Bcast:192.168.53.255  Mask:255.255.255.0
          inet6 addr: 0080::230:48ff:fe98:9ca6/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:6159779 errors:0 dropped:0 overruns:0 frame:0
          TX packets:6137085 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:1158210510 (1.0 GiB)  TX bytes:541617489 (516.5 MiB)
          Memory:e8000000-e8020000

eth0:0    Link encap:Ethernet  HWaddr 99:30:48:98:9C:A7
          inet addr:192.168.53.220  Bcast:192.168.53.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Memory:e8100000-e8120000

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:3791468208 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3791468208 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:740660664181 (689.7 GiB)  TX bytes:740660664181 (689.7 GiB)

We need more CATs

Before you think I’ve posted an animal story in my MySQL category please read on. For reference, The RAT and the CAT is something I wrote back in 2006, that explains the CAT part.

I was reviewing the website performance tonight of a client. I had access to an idle system so being lazy in collecting SQL after my initial audit of the system and MySQL, I turned on the General Query log and hit the home page. NOTE: Do not do this on a running production system, especially with any volume.

What I got was 77 lines of output (after I removed newlines in the queries). 74 were queries, and 72 were from the same table, via some unnecessarily complex sub-joins. I wish I could have laughed but it wasn’t really funny.

My Friday wisdom for writing SQL is this:

  • Don’t write duplicate SQL statements
  • Look the entire process/function, not just an individual SQL statement
  • Don’t use SELECT *. While I could go into detail, in this example they were 4, I repeat 4 TEXT columns where output was not used.
  • All developers should know what a QEP is, and use it.
  • The best performance improvement you can make with an SQL statement, is to never execute it. Even better, never think about writing it.

I trust just looking at this below will impact more wisdom.

1 Connect     XXXX
1 Init DB     XXXX
1 Query       SELECT * FROM categories where cparent_id = 0 AND show_on_navigation =1  order by cid ASC
1 Query       Select home_title, home_keyword from general_meta where id = '1'
1 Query       SELECT * FROM categories where cparent_id = 0 and show_on_navigation = 1  order by cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '45' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '2') AS pretext FROM categories cp WHERE cp.cparent_id = '2' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '5') AS pretext FROM categories cp WHERE cp.cparent_id = '5' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '6') AS pretext FROM categories cp WHERE cp.cparent_id = '6' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '81' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '82' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '83' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '84' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '85' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '86' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '87' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '8') AS pretext FROM categories cp WHERE cp.cparent_id = '8' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '133' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '134' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '135' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '9') AS pretext FROM categories cp WHERE cp.cparent_id = '9' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '88' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '40') AS pretext FROM categories cp WHERE cp.cparent_id = '40' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '669' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '670' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '46' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '10') AS pretext FROM categories cp WHERE cp.cparent_id = '10' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '90' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '91' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '92' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '93' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '94' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '95' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '96' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '97' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '11') AS pretext FROM categories cp WHERE cp.cparent_id = '11' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '12') AS pretext FROM categories cp WHERE cp.cparent_id = '12' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '13') AS pretext FROM categories cp WHERE cp.cparent_id = '13' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '14') AS pretext FROM categories cp WHERE cp.cparent_id = '14' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '69') AS pretext FROM categories cp WHERE cp.cparent_id = '69' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '47' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '15') AS pretext FROM categories cp WHERE cp.cparent_id = '15' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM categories where cparent_id = '98' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '99' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '100' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '101' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '102' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '103' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM categories where cparent_id = '104' and show_on_navigation = 1  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '16') AS pretext FROM categories cp WHERE cp.cparent_id = '16' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '17') AS pretext FROM categories cp WHERE cp.cparent_id = '17' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '18') AS pretext FROM categories cp WHERE cp.cparent_id = '18' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '19') AS pretext FROM categories cp WHERE cp.cparent_id = '19' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '20') AS pretext FROM categories cp WHERE cp.cparent_id = '20' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '45'  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '2') AS pretext FROM categories cp WHERE cp.cparent_id = '2' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '5') AS pretext FROM categories cp WHERE cp.cparent_id = '5' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '6') AS pretext FROM categories cp WHERE cp.cparent_id = '6' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '8') AS pretext FROM categories cp WHERE cp.cparent_id = '8' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '9') AS pretext FROM categories cp WHERE cp.cparent_id = '9' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '40') AS pretext FROM categories cp WHERE cp.cparent_id = '40' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '46'  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '10') AS pretext FROM categories cp WHERE cp.cparent_id = '10' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '11') AS pretext FROM categories cp WHERE cp.cparent_id = '11' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '12') AS pretext FROM categories cp WHERE cp.cparent_id = '12' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '13') AS pretext FROM categories cp WHERE cp.cparent_id = '13' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '14') AS pretext FROM categories cp WHERE cp.cparent_id = '14' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '69') AS pretext FROM categories cp WHERE cp.cparent_id = '69' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '47'  order by cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '15') AS pretext FROM categories cp WHERE cp.cparent_id = '15' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '16') AS pretext FROM categories cp WHERE cp.cparent_id = '16' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '17') AS pretext FROM categories cp WHERE cp.cparent_id = '17' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '18') AS pretext FROM categories cp WHERE cp.cparent_id = '18' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '19') AS pretext FROM categories cp WHERE cp.cparent_id = '19' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '20') AS pretext FROM categories cp WHERE cp.cparent_id = '20' AND show_on_navigation =1)abc ORDER BY cid ASC
1 Query       select * from general_meta where id=1
1 Quit

Handy MySQL documentation indexes

Updated
If your wanting to know more about MySQL Indexes on tables, then check out Understanding Different MySQL Index Implementations.

I just discovered today in the MySQL 5.1 Reference Manual a handy set of additional indexes in the System Navigation section.

  • Index
  • Standard Index
  • C Function Index
  • Command Index
  • Function Index
  • INFORMATION_SCHEMA Index
  • Transaction Isolation Level Index
  • JOIN Types Index
  • Operator Index
  • Option Index
  • Privileges Index
  • SQL Modes Index
  • Status Variable Index
  • Statement/Syntax Index
  • System Variable Index

Perhaps they have been around for some time and I’ve not noticed, but there are much better then searching when you know the content type as per the index list on what you are searching for.

Have you checked your MySQL error log today?

As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

Today’s special found in a 13GB MySQL server error log.

090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101071 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101051 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101072 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.

As you can see the same error is occuring, and in this example 3 times in the last second. To find the cause of the error I didn’t have to look far because I had already checked the /etc/my.cnf file.

$ cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
innodb_buffer_pool_size = 500
safe-show-database

Where do people come up with these my.cnf files? A decision was made to create one, and not use either no file or at least the default that was provided with the installation that is still on the system.

$ find / -name *my*cnf
/home/dontcold/.my.cnf
/etc/my.cnf
/root/.my.cnf
/usr/local/cpanel/whostmgr/my.cnf
/usr/share/doc/MySQL-server-5.0.77/my-small.cnf
/usr/share/doc/MySQL-server-5.0.77/my-medium.cnf
/usr/share/doc/MySQL-server-5.0.77/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-5.0.77/my-large.cnf
/usr/share/doc/MySQL-server-5.0.77/my-huge.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf

What is interesting is that I’ve seen nearly the same file on a previous installation and I documented in For MySQL DBA fame and glory. Prize included.

Getting started with Gearman

Gearman is an open source generic framework for distributed processing. At OSCON 2009 I attended the Gearman: Build Your Own Distributed Platform in 3 Hours tutorial.

While it’s very easy to install Gearman, and follow the first example, if you missed the all important additional PHP steps listed on just one slide you may be left with the “‘Class ‘GearmanClient’ not found” error.

The following are detailed instructions for the installation and configuration of Gearman and PHP on Ubuntu 9.04 Jaunty.

Add the Drizzle PPA to get pre-packaged versions of Gearman.

cp /etc/apt/sources.list /etc/apt/sources.list.orig
echo "deb http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main
deb-src http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main" >> /etc/apt/sources.list
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 06899068
apt-get update

Get the gearman packages

apt-get install -y gearman gearman-job-server gearman-tools libgearman1 libgearman-dev libgearman-dbg libgearman-doc

Get the German PHP extension.

wget http://pecl.php.net/get/gearman-0.4.0.tgz
tar xvfz gearman-0.4.0.tgz
cd gearman-0.4.0/
phpize
./configure
make
make install

If phpize is not available then you are missing the development packages.

$ apt get php5-dev

You also configure PHP to load the extension. This will vary on different Linux environments. In this case.

echo 'extension="gearman.so"' >>/etc/php5/cli/php.ini

Verify the PHP Gearman extension is configured.

$ php --info | grep gearman
gearman
gearman support => enabled
libgearman version => 0.8

Now you are ready for working with the Gearman PHP examples.

Setting up sysbench with MySQL & Drizzle

Sysbench is a open source product that enables you to perform various system benchmarks including databases. Drizzles performs regression testing of every trunk revision with a branched version of sysbench within Drizzle Automation.

A pending branch https://code.launchpad.net/~elambert/sysbench/trunk_drizzle_merge by Eric Lambert now enables side by side testing with MySQL and Drizzle. On a system running MySQL and Drizzle I was able install this sysbench branch with the following commands.

cd bzr
bzr branch lp:~elambert/sysbench/trunk_drizzle_merge
cd trunk_drizzle_merge/
./autogen.sh
./configure
make
sudo make install

Running the default lua tests supplied required me to ensure drizzle was in my path and that I created the ‘sbtest’ schema. I’ll be sure it add that checking to my future developed benchmark scripts.

$ cd sysbench/tests/db
$ sysbench --test=insert.lua --db_driver=drizzle prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

FATAL: unable to connect to Drizzle server: 23
FATAL: error 0: Unknown database 'sbtest'
FATAL: failed to execute function `prepare': insert.lua:7: Failed to connect to the database
$ drizzle -e "create schema sbtest"
$ sysbench --test=insert.lua --db_driver=drizzle prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...

And running produces the following results.

$ sysbench --num-threads=1 --test=insert.lua --db_driver=drizzle run
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Threads started!

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           10000
        other:                           0
        total:                           10000
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 10000  (879.68 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          11.3678s
    total number of events:              10000
    total time taken by event execution: 11.3354s
    per-request statistics:
         min:                                  0.32ms
         avg:                                  1.13ms
         max:                                 68.74ms
         approx.  95 percentile:               2.41ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   11.3354/0.0

Rerunning the prepare also lacked some auto cleanup to allow for automated re-running.

$ sysbench --test=insert.lua --db_driver=drizzle prepare
Creating table 'sbtest'...
ALERT: Drizzle Query Failed: 1050:Table 'sbtest' already exists
FATAL: failed to execute function `prepare': insert.lua:57: Database query failed

For MySQL

$ sysbench --test=insert.lua --db_driver=mysql --mysql_table_engine=innodb prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...

Unfortunately this doesn’t actually create the table in the right storage engine, I had to hack the code to ensure I was comparing InnoDB in each test.

$ sysbench --num-threads=1 --test=insert.l
ua --db_driver=mysql run
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Threads started!

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           10000
        other:                           0
        total:                           10000
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 10000  (897.67 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          11.1399s
    total number of events:              10000
    total time taken by event execution: 11.1084s
    per-request statistics:
         min:                                  0.27ms
         avg:                                  1.11ms
         max:                                252.63ms
         approx.  95 percentile:               2.48ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   11.1084/0.00


Armed with a working environment I can now write some more realistic production like tests in Lua.

configure: error: mysql_config executable not found

If your compiling a product that includes a dependency of MySQL, you can easily get the error

configure: error: mysql_config executable not found

I generally don’t see this problem, because I use MySQL binary tar files, however if you use MySQL packages, such as Ubuntu, you can easily miss the required dependency.

My currently installed MySQL packages on this Ubuntu machine are:

$ sudo dpkg -l | grep mysql
ii  libdbd-mysql                               0.8.2-1-4.1                               MySQL database server driver for libdbi
ii  libdbd-mysql-perl                          4.008-1                                   A Perl5 database interface to the MySQL data
ii  libmysqlclient15off                        5.1.30really5.0.75-0ubuntu10.2            MySQL database client library
ii  libmysqlclient16                           5.1.31-1ubuntu2                           MySQL database client library
ii  libqt4-sql-mysql                           4.5.0-0ubuntu4.1                          Qt 4 MySQL database driver
ii  mysql-client-5.1                           5.1.31-1ubuntu2                           MySQL database client binaries
ii  mysql-common                               5.1.30really5.0.75-0ubuntu10.2            MySQL database common files
ii  mysql-server-5.1                           5.1.31-1ubuntu2                           MySQL database server binaries
ii  php5-mysql                                 5.2.6.dfsg.1-3ubuntu4.1                   MySQL module for php5

The missing link is the development version of the libmysqlclient library.

sudo apt-get install libmysqlclient15-dev

Understanding Different MySQL Index Implementations

It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

There are four general index types to consider when creating an appropriate index to optimize SQL queries.

  • Column Index
  • Concatenated Index
  • Covering Index
  • Partial Index

For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

Example Table

For the following examples, I will use this test table structure.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  external_id INT UNSIGNED NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

Column Index

Quite simply, you have an index on a single column to help with performance. For example, if you were to query your data on external_id, without an index the system will need to read all data pages and then sequential scan pages to identify matching records. As there is no information known about how many rows satisfy the criteria, all data must be read. You can confirm this with the QEP.

SELECT id, user_name
FROM   t1
WHERE external_id = 1;

By adding an index to external_id, the query is optimized to only look at records that satisfy your criteria.

ALTER TABLE t1
  ADD INDEX (external_id);

Concatenated Index

I often see many single column indexes on tables, when these are simply not needed, and generally will be not used. This is easily identified when looking at the QEP and seeing multiple 3,4,5 possible keys.
You need to also consider in your MySQL Index theory, that in general only one index is used for each table in a MySQL query. There are a few exceptions however these are rare.

A concatenated index uses multiple columns. Let’s look a modified version of our query.

SELECT id, user_name
FROM   t1
WHERE external_id = 1
AND      country_id = 5;

The original external_id index will be used, however if we create a concatenated index on external_id and country_id we improve the query path.

ALTER TABLE t1
  DROP INDEX external_id,
  ADD INDEX (external_id, country_id);

What about an index on country_id, external_id? If your access to your data always includes these two columns, you can consider swapping the columns based on the cardinality. However, if you have queries that search on external_id or external_id and country_id, then creating an index on country_id, external_id will not be used.

Tip In the QEP look at the key length to determine how effective concatenated indexes are.

Covering Index

A covering index as the name describes covers all columns in a query. The benefit of a covering index is that the lookup of the various Btree index pages necessary satisfies the query, and no additional data page lookups are necessary.

If we revisit our earlier example, by modifying the external_id index, and create a concatenated index on external_id and user_name we actually satisfy

ALTER TABLE t1
  DROP INDEX external_id,
  ADD INDEX (external_id, user_name);
SELECT id, user_name
FROM   t1
WHERE external_id = 1;

With MySQL, the QEP will indicate in Extra, ‘Using Index’. This is not a reference to the index actually being used, but the index satisfies all requirements of the query.

Partial Index

The final type is the partial index. This is a MySQL feature which allows you specify a subset of a column for the index.

Let’s say we query data and allow pattern matching on last name.

SELECT id, first_name, last_name, user_name
FROM   t1
WHERE last_name like 'A%'

We should add an index to last_name to improve performance.

ALTER TABLE t1
  ADD INDEX (last_name);

Depending on the average length of data in last_name (you can use PROCEDURE ANALYSE as a quick tool to sample this), creating a partial index may greatly reduce the size of the index, and minimize the additional data lookups required.

ALTER TABLE t1
  DROP INDEX last_name,
  ADD INDEX (last_name(10));

In this example, you would want to investigate the size of the index, the improvement, and then the amount of additional reads necessary for sample queries. If your accessed data is generally hot, then the benefit of a smaller index will not be impacted by additional data seeks.

Conclusion

As with any performance tuning, sufficient analysis and before and after testing is necessary for your specific environment.

Some future topics on indexes not discussed here include:

  • Using UNIQUE Indexe
  • The impact of NULL columns and values on indexes
  • Eliminating filesort by using indexes
  • The affect of too many indexes
  • Index cardinality

You need to also consider in your MySQL Index theory, that in general only one index is used for each table in a MySQL query. There are a few exceptions however these are rare.

I common question I am also asked is about function based indexes? MySQL provides no means to use a scalar function against a column in an index.