This miniconf aims to cover many of the current methods of data storage and retrieval and attempt to bring order to the universe. We’re aiming to cover what various systems do, what the latest developments are and what you should use for various applications.

We aim for talks from developers of and developers using the software in question.

Aiming for some combination of: PostgreSQL, Drizzle, MySQL, XFS, ext[34], Swift (open source cloud storage, part of OpenStack), memcached, TokyoCabinet, TDB/CTDB, CouchDB, MongoDB, Cassandra, HBase….. and more!

Call for Papers open NOW (Until 22nd October).

WHAT CAN YOU DO WITH GEO DATA?

Geo spatial indexes are what make this type of data valuable.  With shape and point data you can find relationships between object in our physical world.  How close is the lightning in the storm front?  What homes where hailed on? (WDT) What schools are in my city?  With a list of homes for sale, how fare are they from their nearest school?  What picture where take in this area. (TwitPic)

Lets start with a simple grid of coordinates by creating a table for it call geom, adding our data points in and out of our grid and then searching with a small bounding box. The grid looks like this.

CREATE TABLE geom

What data is available?

GEO data is expensive to create, so has been created by governments.  In the past governments charged for this data.  In 1980 the USGS was charging $300 (usd) per county for Oklahoma GEO data. (I complained to my congressman.) Today, a quick Internet search turns up lots of free GIS data.

I was hoping to find a neat collection of basic GEO data.   It would be nice if there was one place you could get world political borders (Polygons), postal codes (Polygons) and  points of interest like hospitals and airports.  What you can find is lots of  lists, often collections of odd data created for a virility of complex political purpose.  For example, The Global Change Master Directory is a large list of data sources on earth

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying,

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select "1"+"1";
+---------+
| "1"+"1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression

There’s a real explosion of cloud platforms and management tools, it seems you can’t swing a dead cat without hitting one these days. In the commercial proprietary solutions space you have – CA’s 3Terra AppLogic, Enomaly, Nimbula, RightScale. In open source there are Eucalyptus, 

Your searching for how to create a join across two databases on two different servers and it can’t be done directly.   select  d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.

You learn about federated databases.  The federated storage engine allows accesses data in tables of remote databases.  Now how do you make it work?

1) Check if the federated storage engine is supported.  Federation is OFF by default!

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| InnoDB     | YES    

Do you have MyISAM tables you reload with new data?

Do your queries, using that table, get blocked because the table is locked?

Do the waiting queries create idle connections slowing down the table load?

Do you wish you could just replace the table?

Years ago I was told you can replace CSV tables by simply replacing the CSV file. I figured this would also be true of a MyISAM file and it is. I use this perl script to replace MyISAM tables forcast and current observation weather data. The processing and tables are created on another computer. Weather forecasting is CPU and database expensive. I then copy (rsync) the files to the production system and run this script.

#!/usr/bin/perl
################################################################################

There are many areas you need to review when addressing MySQL performance such as current database load, executed SQL statements, connections, configuration parameters, memory usage, disk to memory ratio, hardware performance & bottlenecks just to name a few.

If you were to just look at the data that is held in the database, what would you consider?
Here are my tips, when looking just at the data.

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

I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.

You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.

• Feebase – Data is in clean loadable format
• IMDB – Not

LewisC's An Expert's Guide To Oracle Technology

Sun just announced MySQL Workbench, a new database design tool for MySQL developers and DBAs. I'm a data modeling tool junkie. I like to play with any I can get my hands on. I've used almost every modeling tool that's been built. My all time favorite is probably Erwin.

I decided to download MySQL Workbench (http://www.mysql.com/products/workbench/) and give it a try. Since I was playing with it, I figured I should write about it and while I am writing about it, I might as well write about a couple of other tools, that I have personally used, that you might like.

After the interesting comment storm on Doug’s blog when he posted some of Tim Gorman’s comments on the value of data in his career experiences as compared to the value of the applications manipulating that data, I hesitate a little to post this.

But, I can’t stop myself because it’s such an interesting insight!

Anand Rajaraman, ex-Director of Technology at Amazon.com posted this useful insight that builds on Tim’s. It’s not data vs software as seen by Tim, it’s one step further - more data vs better software.

I like this idea so much

Have you ever needed a random timestamp in MySQL? For example to create demo data programmatically? Here’s my solution:

SELECT FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP('2007-01-01') +
        RAND() *
	(UNIX_TIMESTAMP('2007-01-03')-UNIX_TIMESTAMP('2007-01-01'))
    )
) as random_timestamp;

Or if you prefer a function:

CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP)
RETURNS TIMESTAMP NOT DETERMINISTIC
RETURN FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP(start) +
    RAND() *
    (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start))
  )
);

mysql> select random_timestamp('2007-10-01', NOW());
+---------------------------------------+
| random_timestamp('2007-10-01', NOW()) |
+---------------------------------------+
| 2007-10-05 23:07:11                   |
+---------------------------------------+
1 row in set (0.00 sec)