Location based DB schema in mySQL

http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

http://dev.mysql.com/doc/refman/4.1/en/spatial-extensions.html

http://howto-use-mysql-spatial-ext.blogspot.com/

http://forge.mysql.com/wiki/GIS_Functions#Introduction

http://forums.mysql.com/read.php?23,40909,43079#msg-43079

Upgrading mySQL from 5.0 to 5.1.on CentOS 5

My first attempt was to use yum:

[root@linux /] yum info mysql
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.sanctuaryhost.com
* updates: mirror.fdcservers.net
* addons: mirror.steadfast.net
* extras: mirror.trouble-free.net
Installed Packages
Name   : mysql
Arch   : i386
Version: 5.0.45
Release: 7.el5
Size   : 7.3 M
Repo   : installed
Summary: MySQL client programs and shared libraries.
Description:
MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. The base package
contains the MySQL client programs, the client shared libraries, and
generic MySQL files.


Available Packages
Name   : mysql
Arch   : i386
Version: 5.0.77
Release: 3.el5
Size   : 4.8 M
Repo   : base
Summary: MySQL client programs and shared libraries
Description:
MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. The base package
contains the MySQL client programs, the client shared libraries, and
generic MySQL files.

The version 5.0.77 is good, but it not last updated release, which is 5.1.40 up today, which contains an important mysql feature named
Pationing: Enables distributing portions of individual tables across a file system, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table.

Basically it won’t do an upgrade as the vendor has changed from being MySQL to Sun Microsystems, and as a result I have to do a complete uninstall and re-install manually, first backup your data, stop mysql service and uninstall the current installation( 5.0.45 in my case):

[root@16 /]service mysqld stop
[root@16 /]rpm -qa | grep -i '^mysql-'
mysql-server-5.0.45-7.el5
mysql-5.0.45-7.el5
mysql-devel-5.0.45-7.el5
[root@16 /]rpm -e mysql-server-5.0.45-7.el5
[root@16 /]rpm -e mysql-5.0.45-7.el5
[root@16 /]rpm -e mysql-devel-5.0.45-7.el5

Now download all the current MySQL packages you need and install all with rpm -i( links from mySQL site):

[root@linux /usr/local/bin] mkdir mysql_5.1.40
[root@linux /usr/local/bin] cd mysql_5.1.40
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-shared-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-shared-compat-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-devel-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-embedded-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-test-community-5.1.40-0.rhel5.i386.rpm/from/http://mirror.mirimar.net/mysql/
[root@linux /mysql_5.1.40] rpm -i MySQL-shared-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] rpm -i MySQL-embedded-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] rpm -i MySQL-server-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] rpm -i MySQL-client-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] rpm -i MySQL-test-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] rpm -i MySQL-devel-community-5.1.40-0.rhel5
[root@linux /mysql_5.1.40] mysql_upgrade
[root@linux /mysql_5.1.40] service mysql start
[root@linux /mysql_5.1.40] mysqladmin -V
 mysqladmin  Ver 8.42 Distrib 5.1.40, for pc-linux-gnu on i686

Real World Web: Performance & Scalability

Real World Web: Performance & Scalability

How reset the auto_increment propertie in mysql table.

Recently encountered on a 'odd' behaviour of mySQL table with primary key that defined as auto-increment, in a first look the table had only 5 records, which was far away from limit(256), then I've remebered that the table was used by junit runs for inserting/deleting records and apparently the limit was reached, I think that most expected behavior would be to generate an id for primary key that most close to maximum value of primary key.
Anyway in order to reset the auto_increment, in a situation where some of the most recently added rows were deleted, use:

ALTER TABLE theStuckedTable AUTO_INCREMENT=1234

Distributed databases are going to replace RDMS?

Richard Jones of Last.fm just posted an overview with a great deal of engineering insight:

http://www.metabrew.com/article/anti-rdbms-a-list-of-distributed-key-value-stores/

Swaroop CH of Yahoo wrote an overview of distributed DBs:

http://www.swaroopch.com/notes/Distributed_Storage_Systems

The End of an Architectural Era(It’s Time for a Complete Rewrite)

http://www.vldb.org/conf/2007/papers/industrial/p1150-stonebraker.pdf

The HBase performance

http://www.mail-archive.com/hadoop-user@lucene.apache.org/msg02540.html

Distributed Systems and Web Scalability Resources

Distributed Systems and Web Scalability Resources

No long write-ups this week, just a short list of some great resources that I've found very inspirational and thought provoking. I've broken these resources up into two lists: Blogs and Presentations.

Blogs

The blogs listed below are ones that I subscribe to and are filled with some great posts about capacity planning, scalability problems and solutions, and distributed system information. Each blog is authored by exceptionally smart people and many of them have significant experience building production-level scalable systems.

Nati Shalom's Blog: Discussions about middleware and distributed technologies
http://natishalom.typepad.com/nati_shaloms_blog/

All Things Distributed: Werner Vogels' weblog on building scalable and robust distributed systems.
http://www.allthingsdistributed.com/

High Scalability: Building bigger, faster, more reliable websites
http://highscalability.com/

ProductionScale: Information Technology, Scalability, Technology Operations, and Cloud Computing
http://www.productionscale.com/

iamcal.com
http://www.iamcal.com/ (the "talks" section is particularly interesting)

Kitchen Soap: Thoughts on capacity planning and web operations
http://www.kitchensoap.com/

MySQL Performance Blog: Everything about MySQL Performance
http://www.mysqlperformanceblog.com/

Presentations

The presentations listed below are from the SlideShare site and are primarily the slides used to accompany scalability talks from around the world. Many of them outline the problems that various companies have encountered during their non-linear growth phases and how they've solved them by scaling their systems.

Scalable Internet Architectures
http://www.slideshare.net/shiflett/scalable-internet-architectures

How to build the Web
http://www.slideshare.net/simon/how-to-build-the-web

Netlog: What we learned about scalability & high availability
http://www.slideshare.net/folke/netlog-what-we-learned-about-scalability-high-availability-430211

Database Sharding at Netlog
http://www.slideshare.net/oemebamo/database-sharding-at-netlog-presentation

MySQL 2007 Techn At Digg V3
http://www.slideshare.net/epee/mysql-2007-tech-at-digg-v3

Flickr and PHP
http://www.slideshare.net/coolpics/flickr-44054

Scalable Web Architectures: Common Patterns and Approaches
http://www.slideshare.net/techdude/scalable-web-architectures-common-patterns-and-approaches

How to scale your web app
http://www.slideshare.net/Georgio_1999/how-to-scale-your-web-app

Google Cluster Innards
http://www.slideshare.net/ultradvorka/google-cluster-innards

Sharding Architectures
http://www.slideshare.net/guest0e6d5e/sharding-architectures

Amazon EC2 setup - link
Yahoo Hadoop tutorial - link
Michael Noll blog - link
Hadoop main page - link
Google lectures - link
HBase resources - link
Distributed computing(IBM) - link
HBase and BigTable - link

Understanding HBase column-family performance options - link

Debugging and Tuning Map-Reduce Applications

by Arun C Murthy, Principal Engineer at Yahoo! and Member of Apache Hadoop PMC

http://www.vimeo.com/2085477