Archive for the ‘mysql’ Category

MySQL Design and Administration Tools – Q1 2011

MySQL (http:/www.mysql.com), the web database formely fully open source, then bought by Sun for $1bn, which was then swallowed by the all mighty database giant Oracle (http://www.oracle.com) is finally growing up.

From MySQL 5.0 with the introduction of views and stored procedures (terrible performance) to 5.1 which stabilized the features to 5.5 (the latest GA) version which provides a true relational database experience. Oracle is positioning MySQL as the low-end database to compete against Microsoft SQL Server (http://www.microsoft.com/sqlserver/), Postgre (www.postgresql.org/), Firebird, Interbase etc, with an eventual upgrade to Oracle at the high end

As a database the tools for managing MySQL database have been “inferior” at best, or needing a commercial license of over $100. The most common tools are:

a) PhpMyAdmin (www.phpmyadmin.net) – web based, opensource and free and one of the best available at the time

b) Adminer (www.adminer.org)- formerly PhpMinAdmin -web based, opensource and free -single file database management tool simpler and with less features than PhpMyAdmin

c) Navicat SQL (http://www.navicat.com) – commercial GUI – probably the best MySQL GUI available

d) Webyog SQLyog (www.webyog.com)- commercial GUI – a similar feature set to Navicat, more powerful but not as userfriendly

e) MySQL Gui Tools (http://dev.mysql.com/downloads/workbench/) – opensource GUI – administrator, Query Browser and workbench – free tools which were merged into MySQL Workbench 5.2 as a single tool

The latest MySQL workbench is making inroads into the territory for commercial tools, as it improves the quality of its adminstration, modelling and query tools.

Google Need for “Page” Speed – The Web Developer’s Arsenal

Google the king of search has made the speed of websites and page loading a key actor in ranking. This coupled with the need to use 3rd party frameworks for front end development, like JQuery, MooTools, Scriptalicious, Yahoo YUI, to support the multitiude of browsers has a larger burden web developers.

In true fashion however, the release of the Page Speed addons for both Firefox and Apache, http://code.google.com/speed/page-speed/download.html, somewhat simplify this process.

We have developed a custom web application for a client, and it seemed that the pages were loading very slowly and performance was not acceptable. The approach that we are using to improve performance is three fold:

1. User Interface focused on browser improvements

– GZip all resources – using an Apache .htacess file

– Force caching of resources – expire all resources in the future, and require public cache (images, css) and private browser caching (javascript files)

– Minify CSS and Javascript files, and also combine each type into a single file to reduce the HTTP requests to load the page using Minify (http://code.google.com/p/minify/)

– Minify HTML contents when the page is loaded

2. Business Logic Enhancements

– Add an SQL lite cache to Doctrine resultsets and queries

– Reduce the number of components which are regenerated at the server back-end

3. Database Enhancements

– Use the smallest possible column types for each table – this is diffcult in some cases since the requirements are being identified as the application is being used

– Query performance – the tables have to be kept in 3rd normal form, therefore reporting performance will be affected. As the reports grow more complex, we will keep reviewing this

This is a continuous process so we keep measuring at each point, however each step leads us further into the depths of tuning and performance

The need for speed is now, the question is how far are u willing to go?

Virtual Machine – the New Way for Server Hardware?

On a client project, a new server has been provisioned 400GB HDD, 32GB RAM, Intel Xeon 5660 processor, CentOS 5 all in a virtual machine.

The virtualization is transparent to us, so we can configure, restart it, back it up without any issues.

Now we can test out running a MySQL database with all its data loaded in memory, all 4GB of it. This is going to be exciting, running a project where memory is not an issue 🙂

%d bloggers like this: