The latest from the MySQL community

Note: This article is about the WHERE extension to SHOW. I specifically use SHOW STATUS as an example, but WHERE is an extension to many SHOW statements.

Often DBAs will assess the health of a system by looking at some of the status variables returned by SHOW GLOBAL STATUS (specifying GLOBAL is important; remember that SHOW STATUS is the same as SHOW SESSION STATUS).

There are many status variables that SHOW GLOBAL STATUS returns. (SHOW GLOBAL STATUS on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the SHOW STATUS LIKE syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am looking for.

But I did not know of a way to …

I just ripped thru the 3 week tall stack of postal mail that was waiting for me at home.

The most exciting thing was a big stiff envelope from Julian Cash. I can always tell when something is from him, the way he joyfully hacks on the post office stands out.

Inside was my copy of The MySQL Community, since he had run out when he was giving them to the people in the book at OScon.

You can get your own copy here.

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

PLAIN TEXT SQL:

1. SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
2. FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IS
3. ON (s.TABLE_SCHEMA = IS.TABLE_SCHEMA AND
4. s.TABLE_NAME=IS.TABLE_NAME AND
5. s.INDEX_NAME=IS.INDEX_NAME)
6. WHERE IS.TABLE_SCHEMA IS NULL;

PLAIN TEXT SQL:

1. …

Agenda:

* Red Hat buys Qumranet
* Google’s Chrome browser
* Status of MySQL founder and CTO at Sun

iTunes or direct download (26:30, 6.1MB)

This Monday, September 8th, the Boston MySQL User Group broke our 2-month summer hiatus with a presentation on MySQL Views.

The slides can be downloaded from http://www.technocation.org/files/doc/2008_09_Views.pdf -- 89 kB, .pdf format.

The 199 Mb .flv file can be downloaded at http://technocation.org/node/621/download or played directly in your browser at http://technocation.org/node/621/play.

The presentation covers:

read more

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

1. New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)

1. New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user

1. FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

• INFORMATION_SCHEMA tables - USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS
• Access protection - USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information of tables that current user has access to (SELECT privileges required). This is important for hosting providers

Here is example …

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like "sometimes my slave clone script does not work" may hang on for years.

Entry posted by peter | One comment

Add to: | …

Mark Shuttleworth says it will take 2 years for Linux to catch up to the Mac READ MORE

In case you are processing and working with geospatial data on MySQL, you may be interested in the following UDF (plugin) for MySQL 5.1: Koji Okumura from Oki Labs Japan has ported two functions from PostGIS into a MySQL UDF:

• distance_sphere(point, point): Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points
• distance_spheroid(point, …

In case you are processing and working with geospatial data on MySQL, you may be interested in the following UDF (plugin) for MySQL 5.1: Koji Okumura from Oki Labs Japan has ported two functions from PostGIS into a MySQL UDF:

• distance_sphere(point, point): Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points
• distance_spheroid(point, …