Jaslabs: High performance Software

By Justin Silverton

The most powerful command within mysql is explain. Explain can tell you exactly what mysql is doing when you execute a query and with this information, you can find slow queries and minimize execution time, which can significantly improve the speed of your web application.

How to use the explain command

Here is a simple example of its usage:

Database Schema:

(users table)

(address table)

In this simple example, I am selecting a record that is representing a user, based on a userid.

Here is the output:

Variable	Details
	The ID of this table in the query. EXPLAIN will create one output record for each table in the query.
	possible values: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, and DERIVED.
	The name of the table MYSQL will read the records from.
	The type of join that mysql will use. Possible values: eq_ref,ref,range, index, or all.
	a list of indexes (or NULL if none) mysql can use to find rows in the table.
	The name of the index MYSQL will use (after checking all possible indexes).
	The size of the key in bytes.
	The columns or values that are used to match against the key
	The number of rows mysql thinks it needs to examine to execute the query.
	extra information about the query

This example is pretty straight forward. Since we are peforming a search based on the primary key (userid), there can be only one record that can possible match (the rows variable is 1).

A more advanced example:

This query is more advanced than the first one. It is performing an inner join on the users and address table based on the userid. The userid is a primary key within the users table, but it is not an index in the address table. The output of the explain command shows us the following:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: all
Possible_Keys: (none)
Ref: (none)

The first table is optimized. It is using the primary key for this query. The second table, however is not optimized. The type is all and the Possible_keys=(none), which means it is going to have to go through a full table-scan. Adding an index on the user field optimizes this query.

Final output after index is added:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: const
Possible_Keys: primary
Ref: const

More information on this command can be found Here

by Justin Silverton

It has happened to all of us running a website or application using mysql as its back-end database. Performance is suddenly very sluggish and you have no idea what is causing it. Now there may be other factors that are causing the issue (overloaded CPU, harddrive running out of space, or a lack of bandwidth), but it could also be a query that is not optimized and/or is taking much longer than it should to return.

How do you know which queries are taking the longest to execute? Mysql has built-in functionality for checking this through the slow query log.

To enable (do one of the following):

1) add this to /etc/my.cnf

log-slow-queries=/tmp/slow_queries.log
long_query_time=10

2) call mysqld with –log-slow-queries[=/tmp/slow_queries.log]

long_query_time is the maximum amount of seconds a query can take before it will be logged to the slow query log.

other related options:

–log-slow-admin-statements

Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

–log-queries-not-using-indexes

If you are using this option with –log-slow-queries, queries that do not use indexes are logged to the slow query log.

If slow query logging has been enabled successfully, you will see “ON” in the VALUE field for “log_slow_queries” (shown above).

Note: Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

You may also run into the case where a query is slow at one time (such as when you are logging it) but not another (if you execute it manually):

• A table may be locked, causing the query to wait. the lock_time indicates how long the query waited for locks to be released
• none of the data or indexes have been cached in memory. This is common when MySQL first starts or your tables have not been optimizied
• a background process was running, making disk I/O considerably slower
• The server may have been overloaded with other unrelated queries at the same time, and there wasn’t enough CPU power to do the job efficiently

Log analysis

MySQL also comes with mysqldumpslow, a perl script that can summarize the slow query log and provide a better idea of how often each slow query executes.

By Justin Silverton

1. use the explain command
   Use multiple-row INSERT statements to store many rows with one SQL statement.

   The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

   Example of usage: explain select * from table

   explanation of row output:

   • table—The name of the table.
   • type—The join type, of which there are several.
   • possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
   • key—The key actually used in this query, or NULL if no index was used.
   • key_len—The length of the key used, if any.
   • ref—Any columns used with the key to retrieve a result.
   • rows—The number of rows MySQL must examine to execute the query.
   • extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).
2. use less complex permissions

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

3. specific mysql functions can be tested using the built-in “benchmark” command

   If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute

4. optimize where clauses
• Remove unnecessary parentheses
• COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
• If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table
5. Run optimize table

This command defragments a table after you have deleted a lot of rows from it.

6. avoid variable-length column types when necessary

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

7. insert delayed

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

8. use statement priorities
• Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
• Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.
9. use multiple-row inserts

Use multiple-row INSERT statements to store many rows with one SQL statement.

10. synchronize data-types

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

By Justin Silverton

In a recent article, I wrote about how mysql will no longer provide binaries for windows. This isn’t true. In a recent article Here, the full story is explained:

“Contrary to some reports in the community, MySQL will continue providing binaries both for Windows and other operating systems. All our download pages, including those for MySQL 5.0, have binaries today, and will continue to have them.

The source-only releases we introduced with 5.0.33 (and will continue to provide in the future)are just in addition to the binary-and-source releases. The current latest binary-and-source MySQL Community Server release is 5.0.27, and I expect MySQL 5.0.35 Community Server to be released as binary-and-source within a month, both for Windows and our other platforms. This is as we always planned it, and tried to communicate it. I am sorry our communication has not been clear enough. “

Here is part of the original post/announcement: “We refer to our reference manual, especially the chapter 2.4.14. MySQL Installation Using a Source Distribution when it comes to building MySQL Community Server. At the same time, I want to point out that the service of providing MySQL Enterprise Server binaries is something we do for our paying customers, in the form of the MySQL Enterprise Server subscription, starting at 595 dollars a year”

The Enterprise edition of mysql will not have the binaries available without purchase, but the community edition will still have the latest source and binaries available in future releases. Also of note is that the next release of the mysl community edition that will have binaries available is 5.0.35.

By Justin silverton

In a recent decision by the people over at mysql, the latest community (Open source) edition of the database system will no longer be available for download in binary form (it looks like both windows and linux binaries are still on 5.27 and the lastest is 5.33). This means that if you want to use the latest version, you will have to have the following installed on your system to first compile it from source:

• Visual Studio .Net 2003 (7.1) compiler system




• Between 3GB and 5GB disk space.




• Windows XP, Windows 2000 or higher.

Another interesting statement found here says the following:

“Providing and verifying binaries is a paid-for service for those who want to spent money to save time”

It looks like if you want the official binaries, you need to pay to download them. It appears that mysql is starting to make it harder and harder to use their software without paying a licensing fee.