VT’s Tech Blog

Here’s something in the making, a PHP extension which caches MySQL queries irrespective of the extension being used. It’s a plugin for mysqlnd, the native MySQL driver for PHP called  mysqlnd_qc. This currently requires PHP 5.3.3-dev which is still in development.

The extension allows caching buffered queries through mysql_query and mysqli_query. It allows cache-invalidation though TTL or a user defined callback. This extension is still in the protoype stage right now and will evolve as the project progresses.

The Query Cache is implemented as a PHP extension. It is written in C and operates “under the hood” of PHP. During the start up of the PHP interpreter it gets registered as a mysqlnd plugin to replaces selected mysqlnd methods.

At PHP run time it proxies queries send from mysqlnd/PHP to the MySQL server. If a query string starts with the SQL hint (/*qc=on*/) to enable caching of it and the query is not cached (Cache miss), the query cache plugin will record the raw wire protocol data send from MySQL to PHP to answer the query. The query cache records the wire protocol data in its cache medium and replays it, if still valid, on a cache hit.

Note that the query cache does not hold decoded result sets consisting of zvals (C struct representing a PHP variable). It stores the raw wire data of the MySQL client server protocol. In case of a cache hits, mysqlnd still needs to decode the cached raw wire data into PHP variables before passing the result to the user space. This approach has one major advantage: simplicity.

You can get more information and installation instructions over at the MySQLND Query Cache Project page at Mysql Forge.

Check out this slide which gives you more information about this extension:

How often have you tested your application on your local machine and had it run properly, but when you deploy it and the database starts getting filled up, you start noticing performance problems?
Here’s something you can use to test out your application with databases which are not empty – generatedata.com allows you to define your table structure and the type of data to fill it with. You can export the generated data as SQL (MySQL and Oracle are supported currently)  insert statements or csv which you can import into your databases. The online version allows you to export 200 rows of generated data. If you want larger datasets to be generated, you can download the datagenerator script and run it on your own server.

The best part of the script is instead of generating totally random string or numeric data, you some some semi-realistic data which can be used for testing.

Here’s a sample form I created on the site (click on the image to enlarge):

The SQL generated from this form:

CREATE TABLE myTable (
  id mediumint(8) unsigned NOT NULL auto_increment,
  id MEDIUMINT default NULL,
  name varchar(255) default NULL,
  city varchar(50) default NULL,
  country varchar(50) default NULL,
  dateofjoining varchar(50) default NULL,
  PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1;
 
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('1','Inez','Muncie','Grenada','2009-04-19 12:55:49');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('2','Reese','Washington','Suriname','2009-10-17 01:28:15');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('3','Hope','Edina','Guyana','2008-02-22 10:17:55');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('4','Cameron','Sister Bay','Malaysia','2008-02-21 01:38:18');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('5','Zephr','Lebanon','Syrian Arab Republic','2008-12-09 21:40:04');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('6','Byron','Indio','Afghanistan','2009-03-15 08:50:17');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('7','Carol','Bay St. Louis','British Indian Ocean Territory','2009-04-16 04:57:43');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('8','Latifah','Geneva','Albania','2009-08-25 00:14:26');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('9','Nina','Fall River','Solomon Islands','2009-01-03 21:45:51');
INSERT INTO myTable (id,name,city,country,dateofjoining) VALUES ('10','Jorden','Ardmore','Latvia','2009-01-20 19:31:36');

Get more information at: generatedata.com

Here’s a problem we had come across today. Whenever we tried inserting data into a certain table, MySQL kept throwing up the following error:

mySQL error: 1062
Duplicate entry '0' for key 1

After scratching our heads over this one, we tried to alter the table to set the autoincrement field to the next number and even that failed to fix the problem. Then hunting around the web, we found the cause of this problem. It seems that Mysql throws this error because the field type of the auto increment field is not large enough to hold the next value, so it tries to wraps the count back to ’0′ where the is already a record with that value.

We changed the field from int to unsigned bigint which fixed the problem for us. So if you face the same problem, check if your autoincrement field has maxed the datatype for that field.

How do you sort a varchar field which has the dates stored in it? If you try the usual order by on the varchar fields you’ll have trouble with the sort order, since MySQL will order this field like a varchar and you will surely have problems with dates being sorted this way. Take the following example of a table which has a field called feed_date which defined as varchar. Now ordering the results by feed_date will not give the correct order like in the following example:

mysql> select * from datetest order by feed_date;
+----+-------------------+------------+
| id | feed_name         | feed_date  |
+----+-------------------+------------+
|  1 | VTs Tech Blog     | 1-23-2008  |
|  2 | vinuthomas.com    | 12-23-2006 |
|  3 | sitesandsounds.in | 3-12-2008  |
+----+-------------------+------------+
3 rows in set (0.00 sec)

Hmmm… 2006 coming in between 2008 doesn’t seem right does it? The only way to get MySQL to sort this result properly will be if we can get MySQL to understand this data as a date field. To to this, we can use the mysql function STR_TO_DATE. This function allows us to convert a string to date and allow us to specify in which format the date is present.

So to convert the current date format in the feed_date column which is in the mm-dd-yyyy format, we’ll have to use this syntax: STR_TO_DATE(feed_date, ‘%m-%d-%Y’). Now we can use this converted data to sort on:

mysql> SELECT id, feed_name, feed_date ,
 STR_TO_DATE( feed_date, '%m-%d-%Y' ) AS date_for_sort
 FROM `datetest` ORDER BY date_for_sort;
+----+-------------------+-------------+--------------+
| id   | feed_name        | feed_date  | date_for_sort|
+----+--------------------+------------+--------------+
|  2   | vinuthomas.com   | 12-23-2006 | 2006-12-23   |
|  1   | VTs Tech Blog    | 1-23-2008  | 2008-01-23   |
|  3   | sitesandsounds.in| 3-12-2008  | 2008-03-12   |
+----+--------------------+------------+--------------+
3 rows in set (0.00 sec)

Disclaimer: I know that doing this is going a round about way to getting this done, this is just a tip to help out when we have a problem like this and can’t really change the database field declaration and you have tons of data in the field which doesn’t confirm to the MySQL date format in a varchar field.

Link: MySQL Manual on STR_TO_DATE

Amazon’s S3 service seems to be getting into everything ‘online’ these days. Here’s an announcement by Mark Atwood of a S3 storage engine for MySQL 5.

It allows one to view and manipulate Amazon’s S3 storage service as
tables and items by MySQL. You can keep your blobs or large varchars
or truely huge datasets in S3, and then join the tables against your
local ones.

Announcement: MySQL Lists: internals: Storage engine for Amazon S3
Mark’s project page: http://fallenpegasus.com/code/

Here’s a quick tip in MySQL. Let’s say you have a table called browsercount like the one below:

Let’s say you have a web-analytic application which has to add a browser into the table if it doesn’t exist, but update the count for the browser if the entry already exists. How do you go about that?

The usual way would be to first query the table to see if the entry exists, then fire an insert statement or an update statement based on the results.

Here’s how to perform the same action in one SQL statement:

INSERT INTO browsercount (browser,count) VALUES ('Firefox',1) ON DUPLICATE KEY UPDATE count=count+1;

This will work if the id field in the table above is declared as UNIQUE or is a Primary Key.

The query basically tells mysql to insert the data into the database, but if a duplicate key is found, increment the count field by 1. It’s much faster than firing up 2 sql queries from your PHP script

This video is from one of Google’s Techtalk sessions by Jay Pipes

ABSTRACT Learn where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the “low hanging fruit” on the tree of bottlenecks. It’s not rocket science, but with a bit of acquired skill and experience, and of course good habits, you too can do this magic!

Here’s a cool utility for MySQL administrators which allows you to track the performance of your servers and also gives you performance enhancement tips. You can see a sample report from this tool over at: http://gert.sos.be/demo/mysqlar/
The MySQL Activity Report package is a tool to help MySQL database administrators to collect several database parameters and variables. These collected values can be used for server monitoring or performance tuning purposes.

Link: MySQL Activity Report

For Installation Instructions, head over to: http://www.linuxforums.org/forum/servers/72890-how-mysql-activity-report.html

Here’s a MySQL tip from the MySQL Performance Blog which deals with possible lock situations while using InnoDB and foreign keys.

Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter.

Peter Zaitsev goes on to outline an example of when this could happen and warns developers to look at locking overheads while implementing foreign keys in Innodb

Link: MySQL Performance Blog » Innodb locking and Foreign Keys