It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema. The column was being used to represent a integer primary or foreign key column.

In a representative production instance (one of a dozen plus distributed production database servers) the overall database footprint was decreased from ~10 GB to ~2 GB, a 78% saving. In total, 15 columns across just 4 tables were changed from DECIMAL(31,0) to INT UNSIGNED.

One single table > 5GB was reduced to under 1GB (a 81% saving). This being my record for any GB+ tables in my time working with the MySQL database.

Had this server for example had 4GB of RAM, and say 2.5GB allocated to the innodb_buffer_pool_size, this one change moved the system from requiring more consistent disk access (4x data to memory) to being able to store all data in memory. Tests showed …

Some days you learn about MySQL commands even without knowing about them. Today I wanted to check the privileges a user had because they did not have permissions to drop a view. Rather then typing SHOW GRANTS I quite by accident typed SHOW PRIVILEGES only to realize not only was it a valid command, it actually provided information that means I don’t have to go to the Privileges Provided by MySQL documentation page which I was already on.

Old dog, new trick.

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               | …

I have written about a patch to InnoDB that generates a trace of all pages accessed by InnoDB. I used this to collect another trace for 24 hours and the results from it match the previous results. I then modified the simulator to estimate the miss rate when the database is compressed by 2X and the miss rate is significantly smaller. This can be viewed in the results I published where the miss rates for the uncompressed and compressed databases are displayed by the gold and green lines. The other lines are estimates assuming a Zipfian distribution.

The miss rate determines performance when your database server is IO bound and for my workload I have simulation results that estimate the miss rate improves linearly with the …

Topics for this podcast:

*Microsoft founds CodePlex Foundation, losing Sam Ramji
*Software patents at the center of MS, OIN maneuvering
*Eucalyptus Systems releases hybrid cloud product
*Oracle-Sun Microsystems and the potential fate of MySQL

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

A new case study has been published that describes how and why Mapion adopted MySQL Cluster as the real-time, mission critical database for their Mapion Mobile applications.

Mapion adopted MySQL Cluster Databse because it allows for the availability of existing applications through its ’shared-nothing’ distributed architecture with no single point of failure, to meet Mapion’s mission-critical application requirements of 99.999% availability. This is coupled with automatic data partitioning with load balancing, which allows almost unlimited database scalability for the company. Mr. Takashi Ando comments, “Some databases supported clusters when we previously reviewed our database technology, but they were hard to operate and would have increased costs substantially. We found ourselves in a completely different situation when planning the introduction of the new system. MySQL Cluster Database had made it possible to construct a highly …

I would like to hear some opinions from the MySQL worldon what knowledge does it require to be a MySQL Developer?

Not the folks who write the MySQL server code but a developer who really knows their stuff when programming with MySQL? The MySQL 5.0 developer exams going under a review. The hands on exam for 5.1, which has been hindered by other chores, is also under construction. But I still have a fundamental question : What makes someone a MySQL Developer?

The course book from the MySQL for Developers class is very thick and filled with a lot of information. It covers a broad spectrum from basic SQL to query optimization. None of it is programming language specific (or covers approaches like using PDO …

For anyone running any version of Kontrollbase right now I’d highly recommend downloading this release. There are a lot of bug fixes, especially ones that some very nice users have pointed out during the support process – “nandan_it” and “hipparcos” to mention a few.The biggest changes are the Analytics tab that adds about 99% of [...]

Welcome to the 162nd edition of Log Buffer, the weekly review of database blogs.

Oracle

The big news this week came was Oracle’s unveiling the OLTP Oracle Database Machine & Exadata v2, as reported by Alex Gorbachev.

Kevin Closson covered it, of course: Oracle Drops Exadata In Favor of Sun FlashFire Based OLTP Database Machine?, and he and his readers kick it around in a diverting way.

…

This is a “dogfood” type story (see below for explanation of the term)… Open Query has ideas on resilient architecture which it teaches (training) and recommends (consulting, support) to clients and the general public (blog, conferences, user group talks). Like many other businesses, when we first started we set up our infrastructure quickly and on the cheap, and it’s grown since. That’s how things grow naturally, and is as always a trade-off between keeping your business running and developing while also improving infrastructure (business processes and technical).

Quite a few months ago we also started investing (mostly time) in the technical infrastructure, and slowly moving the various systems across to new servers and splitting things up along the way. Around the same time, the main webserver frequently became unresponsive. I’ll spare you the details, we know what the problem was and it was predictable, but since it wasn’t …

Citrix joins the Linux Foundation. BonitaSoft raises $3m. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Citrix joined The Linux Foundation.

# Open source BPM vendor BonitaSoft raised $3m from Ventech and Auriga Partners.

# Jaspersoft updated JasperReports Professional with enhanced data visualization.

# US CIO Vivek Kundra outlined the government’s cloud strategy, using NASA’s open source Nebula cloud.

# Infobright …