The latest from the MySQL community

As documented, the FLOAT datatype does not guarantee precise storage of decimal values. But where the non-precision is apparent can be a little confusing - take the following example:

mysql> CREATE TABLE my_table (a FLOAT);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into my_table (a) VALUES ('2.2');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM my_table WHERE a = 2.2;
Empty set (0.05 sec)

mysql> SELECT a, IFNULL(a,0) FROM my_table;
+------+-----------------+
| a    | IFNULL(a,0)     |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT a, a+0 FROM my_table;
+------+-----------------+
| a    | a+0             |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)

Need precision? Try Decimal.

Today I attended a webex demo of MySQL Enterprise Monitor from MySQL. As most of the Yahoo’s are interested in learning about this tool, so arranged a web-ex demo from MySQL. MySQL is kind enough to host this event.

I always thought a dedicated monitoring and alerting system is completely missing from MySQL product line for all these days, and I can see that this tool is heading in the right direction to capture the market. Currently it monitors all server variables, errors and identifies any critical conditions upfront to avoid a disaster. But the new features that are in the pipeline for the coming months seemed to be promising (like upgrade adviser, Load balancing, query analyzer and connection manager).

As this is not like a single node …

MySQL AB today launched the MySQL Authorized Hosting Partner Program, a new partner offering specifically designed for top-tier hosting companies and Managed Hosting Providers (MHPs). MySQL Authorized Hosting Partners get access to MySQL Enterprise -- MySQL AB's premium, commercial-grade software and services -- to affordably deliver the high database availability and performance required by the growing number of online, on-demand, Web-hosted applications.

For more information on the program's benefits and service levels, please visit this page.

Apparently the role of community managers is one of the coolest jobs around. Jono Bacon’s one for Ubuntu, and Jeff Waugh did an awesome job before that. Jay Pipes does a nice job for MySQL. Dawn Foster is the community manager that "powers" community managers at Jive Software who makes wicked-cool collaborative software. 

Glyn penned an article about the proliferation of community managers. In his article he mentions …

Matt Asay asks why Asia doesn’t contribute more to open source. It’s an interesting question, and the responses are equally interesting. There are a few in the comments to Matt’s piece, for example, pointing out Asia’s contribution to projects such as Ruby on Rails, not to mention Andrew Tridgell’s crucial involvement in the Samba project.

Those are the exception rather than the rule, however. There are also a few clues regarding the comparative lack of open source contribution from Asia in the ZDnet article that prompted the question in the first place.

“Harish Pillay, open source evangelist with Red Hat Asia-Pacific, acknowledges …

As mentioned earlier I am testing out SSD disk performance on a 4 core machine with 6GB of memory. I spent last week comparing the drive to a standard 10K RPM SATA Raptor drive (EXT3 file system right now). As noted here and elsewhere the performance of these drives really shines with a specific workload, but they are not for everyone out there. The random write performance of these drives leaves a great deal to be desired while their read performance is outstanding:

Above you can see that when we perform 10K random reads with 0 writes we peak at about 5200 IOPS vs the 161 IOPS on a standard SATA drive. When we flip the IO to all writes we end up getting around 100 IOPS out of the SSD drive. Not many sites are 100% reads, so some sort of mixed IO load is expected. Here you can see how the number of IO’s per second varies under different workloads:

One of the cooler inventions in recent versions of MySQL is having the slow log and general log available as plain-text files as well as CSV-engine type tables directly within the mysql database.

So now you want to analyze the slow query log using SQL, but (a) you don’t want to lock up the table while you do your work, and (b) you suspect that your work may involve adding indexes, dropping columns and such during the analysis.

So you decide to make a copy of the table.

Except that you can’t.

mysql> CREATE TABLE slow SELECT * FROM mysql.slow_log;
ERROR 1553 (HY000): You can't use locks with log tables.

As all the data is stored as a plain CSV …

There is a new wiki comparing MySQL to PostgreSQL. Because it's a wiki, hopefully it can be kept updated so that it's current AND accurate.

The wiki is MySQL vs PostgreSQL.

Personally, I'd like to see this grow into a universal comparison site that the community could keep updated.

LewisC

Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:

PLAIN TEXT SQL:

1. mysql> SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M') rows,concat(round(data_length/(1024*1024*1024),2),'G') DATA,concat(round(index_length/(1024*1024*1024),2),'G') idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10;
2. +-------------------------------------+--------+--------+--------+------------+---------+
3. | concat(table_schema,'.',table_name) | rows   | DATA   | idx    | total_size | idxfrac |
4. …

In the first article on IP to location I described the overall concept. In this edition we'll look at the implementation and how to scale the tracing to a reasonable speed.

With the examples from the last article you can also implement everything to build your own database. Before you jump into it take a minute to look at the implications.

Taking the example from the previous article 217.80.0.0/12 for the German Telekom we have to trace 1mio addresses and scanning them all will take a while:

# time traceroute -w 1 -m 8 -q 1 217.80.0.1
traceroute to 217.80.0.1 (217.80.0.1), 8 hops max, 40 byte packets
 2  217.0.72.230  36.974 ms   
 3  ke-eb1.ke.de.net.dtag.de (62.154.98.82)  54.454 ms
 4  217.0.74.213  54.610 ms
 5  * * *
 6  * * *
 7  * * *
 8  * * *

real    0m3.244s

If we can make the make …