Schema optimisation

PROCEDURE ANALYSE() examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes.

It is important that the type of each column be optimal. In general, smaller is better and NULL should be avoided (especially for columns which are going to be indexed) as the NULL value imposes a performance penalty in MySQL.

The work to do is to run each table in the sample database through PROCEDURE ANALYSE() and optimise the type of the columns if required.

Indexing

MySQL supports indexes which are created with the CREATE INDEX command.

Assuming we have created an index consisting of three columns, C1, C2 and C3, the query optimiser will use this index (and therefore speed up query execution) when it matches:

• fully (i.e. C1, C2 and C3 match)
• with a leftmost prefix (C1)
• with a column prefix (beginning of C1)
• with a range of values (range of C1 values)
• with one element of C1 and a range on C2

When writing an SQL query, it is advisable to pay special attention to the WHERE clause. If an indexed column appears there, it is better for it to be placed on its own on one side of the comparison function. When this is done, the MySQL query optimiser will use the index.

The SHOW INDEX FROM command can be used to get information on existing indexes. The OPTIMIZE TABLE command is used when fragmentation occurs (either in the table or in indexes).

The work to do is to identify slow SQL queries on the sample database and decrease their runtime by judiciously using indexes.

Partitioning

MySQL can store the data in tables in distinct user-defined partitions. As written in the MySQL manual,

“In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function, which in MySQL can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function.”

The effects of partitioning are diverse. First, partitioning can help in making MySQL read less data when executing a query if the query only refers to the data in a distinct partition (this is called pruning). Secondly, partitioned data is easy to maintain i.e. when having to discard old data by dropping a whole partition. Thirdly, partitions can be on different disks and therefore can be accessed concurrently.

In fact, partitioning can be thought as being a kind of coarse-grained indexing.

MySQL supports different kinds of partitions: range, list, column, etc.

Unfortunately, MySQL suffers from one very serious limitation: partitioned tables do not support foreign keys.

Partitions can be specified when doing a CREATE TABLE or, later, with ALTER TABLE.

The work to do is to add partitions to the existing tables in the same database and examine how query execution is impacted.

The MySQL event scheduler

MySQL has an event scheduler which can be used to run user-specified queries in the future and on a regular basis. By default, the scheduler is off and can be activated with SET GLOBAL event_scheduler = 1;.

When this is done, CREATE EVENT and ALTER EVENT can be used.

The work to do is to explore the various types of events which can exist and the various ways events can be created.

Benchmarking

When a database becomes big, query performance can suffer. It becomes important to measure the performance of the system and this is called benchmarking. We can measure many things like number of transactions per unit time, response time, the scalability or support for concurrency. When benchmarking, it is important not to use only a subset of real data, incorrectly distributed data or test using a single connection even though the database is supposed to be used concurrently.

ab

Various tools exist which help to benchmark a web application. One of them is the Apache Benchmarking tool (ab) which benchmarks the web application as a whole i.e. the full stack (operating system, database, web server, scripting language).

The work to do is to use ab to evaluate the performance of the web application written before.

mysqlslap

Another important benchmarking tool is mysqlslap which is distributed with the MySQL database. mysqlslap measures the performance of the database (and not the web application) and has various capabilities:

• Testing using automatically generated tables and SQL queries
• Measuring performance when the database is accessed concurrently
• Running tests many times to get more significant results
• Using existing schemas and custom SQL queries when measuring performance
• Producing performance reports

The work to do is to use mysqlslap to evaluate the performance of the standard MySQL employees sample database.

Benchmarking sometimes shows that query performance is not good enough. The question then is to know which query is too slow and to find ways to make it run more quickly. Those two steps are called profiling and optimisation.

Profiling

MySQL has a slow query log (which is inactive by default) which logs all SQL queries which are taking too long to execute. Whenever an application is slow, the slow query log should be enabled and monitored to discover the culprits. This can be done with the SET GLOBAL slow_query_log = 1; command. It is also possible to change the threshold above which queries are considered to be slow. Do SET long_query_time = 2; for 2 seconds for example.

MySQL provides many ways for a programmer (or a database administrator) to discover why an SQL query is performing badly.

MySQL server status variables provide information about the execution of queries within MySQL. There are a lot of variables but the most relevant for performance have names starting with:

• Bytes_ (bytes received and sent)
• Com_ (commands the server executed)
• Created_ (temporary tables created)
• Handler_ (storage engine operations e.g. InnoDB)
• Select_ (various types of join execution plans)
• Sort_ (various types of sort operations)

The various types of join execution plans. Some of them are:

• Select_scan: refers to a table that is completely read in sequence from the hard drive.
• Select_range: refers to a table that was read from the hard drive only in the necessary places to satisfy a limited range of conditions.
• Select_full_join: is the same as Select_scan with the difference that Select_full_join applies to the second and subsequent tables in the join plan for a multiple table query.
• etc.

The work to do is to use MySQL server status variables as well as the EXPLAIN command to fully understand how queries are executed.

(MySQL also provides the following commands:

SET profiling = 1;

SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

which give the durations of the various phases of a query)

In a lot of applications, users need to be able to upload files. For example, most social networks allow users to upload pictures.

We will modify the web application to allow users to upload pictures when they are viewing details about a specific phone. After some time, other users will be able to browse a gallery of user-uploaded pictures for each of the phones.

To do that, we will use the file upload capabilities of PHP.

It is important to make sure that the uploaded file is of the proper kind and size.

MySQL supports transactions. This allows software developers to create application which preserve data integrity even in the event of (software or hardware) failures.

The work to do is to use transactions as provided in MySQL to protect all collections of queries which should be treated as being atomic in nature.

Another important feature of MySQL is stored procedures. These procedures exist in the database and, when called from a PHP application, can greatly increase performance. Furthermore, stored procedures allow multiple applications, written in PHP or not, to behave in a consistent manner.

The work to do is simplify the checkout of cart items using stored procedures.

HTTP is stateless. Consequently:

When a web server is required to customize the content of a web page for a user, the web application may have to track the user’s progress from page to page. A common solution is the use of HTTP cookies. Another method include server side sessions.

PHP supports both sessions and cookies and, as such, allow us to transform the web application into one which supports multiple users concurrently. In other words, the work to do is to transform the web application into one where multiple users can log in and order phones at the same time. We will use PHP sessions to implement those new functionalities.

Additionally, we would like to implement a “Show popular phone of the hour”. We will use PHP cookies (with appropriate expiration dates) to implement this functionality in such a way that queries to the database are minimal.

Since version 5.0 of MySQL was released in October 2005, a lot of new features have been added to it to make it standards-compliant, secure and efficient. In 2011, MySQL 5.5 is still an open source software and is freely downloadable for various platforms. In this blog entry, I will focus on performance in MySQL.

MySQL provides two important utility statements, EXPLAIN and PROCEDURE ANALYSE, which can be used to understand where performance bottlenecks are. EXPLAIN is used to obtain information about how MySQL executes a SELECT statement. PROCEDURE ANALYSE suggests optimal data types for each column that may help reduce table sizes. Also, as MySQL exposes its inner workings to the database administrator, one can go very far in optimising performance.

One simple way to increase performance is to create indexes as they generally allow queries to run more quickly. Care must be taken to only create indexes when really needed as indexes consume space. In some special case, indexes can, in fact, decrease performance.

In the real world, web applications are used by concurrent users and it is important to use transactions and proper locking mechanisms in order to preserve the integrity of data. Of course, it is also important to understand that transactions and locking can have a negative impact on performance.

Whenever the same sequence of SQL statements is done over and over again, there is an opportunity to use a stored procedure which is just a set of SQL statements stored in the database server itself. Consequently, clients don’t need to issue the individual statements but can refer to the stored procedure instead. As written in the MySQL documentation, stored procedures can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side.

One recently added feature of MySQL is partitioning. This allows different portions of a table to be stored as separate files in different locations on disk. The rule used for doing the partitioning is specified by the user. Some queries can run much more quickly when a table is partitioned in virtue of the fact that data satisfying a given WHERE clause can be in one (or a few of the) partition(s), which obviously excludes any remaining partitions from the search.

Finally, given the prevalence of storage systems with very high I/O rates compared to our classical hard disks (e.g. solid state devices), MySQL has an adjustable I/O rate which can be tweaked to maximise performance.

We will cover some of these interesting aspects of MySQL in our coming Web Development with PHP and MySQL training starting on 25 July and 8 August.

One thing is sure: MySQL is far from being a toy.

The Linux kernel is open source software and, consequently, its source code (millions of lines of C) is available.

Sometimes, it is important (and interesting) to compile either a specific kernel module (typically because of a bug correction) or to compile the whole kernel. Of course, it is technically possible to download the latest Linux kernel, compile it and install it on an enterprise Linux distribution like RHEL or CentOS but this is not recommended.

Instead, Red Hat and CentOS recommend to obtain the source code of the kernel which is already in use in those distributions, configure it, compile it and install the compiled kernel without removing the previous one(s).

When using CentOS (for example), the first step is to obtain the source code of the kernel and unpack it correctly.

Then, depending on user requirements, the system administrator can:

• either build one kernel module (e.g. to correct a bug) and use that corrected module in the current kernel
• or rebuild the whole kernel and install it without removing existing kernels.

Network File System

Network File System (NFS) allows remote hosts to mount file systems over a network and interact with those file systems as though they are mounted locally. NFS is configured by editing /etc/exports. Note that the defaults settings for NFS shares are ro (read-only), sync, wdelay and root_squash. It is possible to use all_squash and specify anonuid and anongid. Hostnames should follow a specific format.

The mount command (when used with the appropriate options) can be used to connect to an NFS share. Naturally, one can also edit /etc/fstab so that NFS mounts are done automatically at boot time.

Samba

Samba is an important software as it allows Linux computers to easily communicate with computers running Windows.

Samba can:

• Serve directory trees and printers to Linux, UNIX, and Windows clients
• Assist in network browsing (with or without NetBIOS)
• Authenticate Windows domain logins
• Provide Windows Internet Name Service (WINS) name server resolution
• Act as a Windows NT-style Primary Domain Controller (PDC)
• Act as a Backup Domain Controller (BDC) for a Samba-based PDC
• Act as an Active Directory domain member server
• Join a Windows NT/2000/2003/2008 PDC

What Samba cannot do:

• Act as a BDC for a Windows PDC (and vice versa)
• Act as an Active Directory domain controller

Samba can be configured by editing /etc/samba/smb.conf but most people prefer using a graphical tool such as SWAT (it is important to add FLAGS = IPv4 in its xinetd configuration). When configuring Samba, it is possible to create a share:

[shared]
comment = This is a Linux share
path = /home/shared/
valid users = john, jane
public = no
writable = yes
printable = no
create mask = 0644

Valid users (e.g. john and jane in the above example) need to be assigned SMB passwords which can be distinct from their Linux password. The smbpasswd command need to be used.

Shares can then be accessed using Nautilus and even mounted in Linux using the cifs filesystem type. Samba comes with a number of useful command line tools which system administrators should be aware of.