The Muse: mysql

Showing posts with label mysql. Show all posts

DWR + Lazy Loading

Time immemorial, we are all aware about the famous lazy load exceptions in Hibernate. Using a detached object does not exactly hide us from the issue. We are using DWR in our application and found that when marshalling (or unmarshalling if you insist) a detached object to JSON, DWR was erratically trying to call a null (lazyly loaded) object, causing LazyLoadExceptions.

We did not want to use OpenSessionInViewFilter as it is evil (some day I'll post all the consolidated rantings as one). You can google and find a lot of info around it.

As a quick fix we did what one would first think of, "It's a demo, just eager load it!". Well the demo was over and now was the time to investigate a better solution, and we did find it.

It was no rocket science, the solution was available in front of us all along, we just did not look hard enough. When using DWR, there are BeanConverters available which are responsible for this marshalling process. There was one for Hiberante, called "hibernate3". http://directwebremoting.org/dwr/server/hibernate

The HibernateBeanConverter tries to avoid reading from un-initialized properties. (If you just want something that blindly reads everything then just use a plain BeanConverter).

Bingo, so when exporting a detached hibernate object to DWR, use type="hibernate3" and that should resolve the lazy load issues.

Setting up a PHP development environment on your local system

This is an old post I had written on google docs, before VMware Server was free for the general user. Thought could be still more relevant to the general developer for testing in Linux envoirment. VMPlayer I beleive is lighter than VMware Server.

There are 2 stages of setting up the development & testing environment for yourself. The preferred work environment is Linux. If you already on Linux then you can safely skip Stage I, you can directly proceed to Stage II, also if you have Linux already installed, just cross check if you have the LAMP (Linux + Apache + MySQL + PHP) server installed.

Pre Installation Software Download Links

http://www.vmware.com/download/player/ [VMPlayer]
http://www.ubuntu.com/getubuntu/download [Ubuntu]

STAGE I: Setting up Ubuntu on VMWare.

Download Ubuntu/Xubuntu/Kubuntu Desktop edition ISO, what ever you like... all the same just different filemanagers (Gnome/Xfe/KDE resp.). If you are comfortable with command line, then you can also install the Server edition with no XWindows and only commandline.
Download and install VMPlayer for your system.
Download qemu for your operating system and extract in any directory you like.
Open your commandline to go ahead to bin folder of qemu and type

qemu-img create -f vmdk ubuntu.vmdk 3G

Here we are creating a 3GB, virtual hard drive in VMWare format. This file will now act as our hard drive for VMWare to install an OS on it. The name of the file is ubuntu.vmdk, you can name it what ever you wish to.
Now the main vmx file (The VMWare configuration file). change the highlighted paths as per your system.

 #!/usr/bin/vmware
config.version = "8"
virtualHW.version = "4"
ide0:0.present = "TRUE"
ide0:0.filename = "ubuntu.vmdk"
# The amount of RAM you want to allot to the Operating system. For Desktop use 512 and server just 256.
memsize = "512"
MemAllowAutoScaleDown = "FALSE"
ide1:0.present = "TRUE"

#ide1:0.fileName = "auto detect"
#ide1:0.deviceType = "cdrom-raw"

ide1:0.fileName = "ubuntu-7.04-server-i386.iso"
ide1:0.deviceType = "cdrom-image"

ide1:0.autodetect = "TRUE"
floppy0.present = "FALSE"
ethernet0.present = "TRUE"
usb.present = "TRUE"
sound.present = "TRUE"
displayName = "Ubuntu LAMP Server"
guestOS = "ubuntu"
nvram = "ubuntu-server-three.nvram"
MemTrimRate = "-1"

ide0:0.redo = ""
ethernet0.addressType = "generated"
uuid.location = "56 4d ce 99 e0 d2 2b bf-73 47 ac 62 65 13 57 86"
uuid.bios = "56 4d ce 99 e0 d2 2b bf-73 47 ac 62 65 13 57 86"

tools.syncTime = "TRUE"
ide1:0.startConnected = "TRUE"

uuid.action = "create"

checkpoint.vmState = "ubuntu-lamp-server.vmss"

isolation.tools.hgfs.disable = "TRUE"
virtualHW.productCompatibility = "hosted"
tools.upgrade.policy = "manual"

tools.remindInstall = "TRUE"

usb.autoConnect.device0 = ""

After setting all the paths correctly, if you have VMPlayer installed, just save the vmx file, (call it ubuntu.vmx) and double click on the file.
If all the paths are set correctly, the VMPlayer will boot up the virtual drive and show the ubuntu installation menu. This is easier than windows a million times.
At the end of the installation (server or desktop) the process will ask you, if you want to install LAMP server. Select it and let it install the LAMP server.
Reboot the system, and you are good to go. You have successfully installed Ubuntu on VMWare.

STAGE II: Check the configuration setup of Apache/MySQL/PHP

Now we'll check if PHP and Apache have been successfully installed on your system. Go the /var/www folder, you should be seeing a apache2-default folder out there. If you see these then apache seems to be installed. Just open the browser and type http://localhost you should be able to see the apache-default folder over there. If you a receive a page not found then apache is not running or installed properly.

If things look good, next stage is to check PHP installation. Create a file named index.php and type the following there.

 <?php phpinfo(); ?>

Just refresh the localhost (place the file in /var/www) That should give you loads of PHP info on the screen in blue, purple and a huge table. If that happens you are good to go!!! Else something is wrong!!!.

MySQL Check
Open the command line and type

mysql -uroot

If it opens up with a mysql> prompt then it's good else something is wrong. Your mysql password is blank.

If all sounds good, now go to Stage III

STAGE III: Applications to setup.

Download

JOOMLA
WORDPRESS

Extract them to the /var/www folders and access them using http://localhost/joomla and http://localhost/wordpress respectively if you extracting them to the folders on these names.

How to go about setting them, start the index.php or read the readme file and you should be good to go.

DATETIME vs TIMESTAMP vs DATE & TIME - II

Ok now the test are run and the results are out, I know we are all excited to know them, and I'm equally eager to print them too!!

The test did a simple select * from the tables.

  public void fetchAll() throws Exception {
  String SQL1 = "SELECT * FROM dateandtime";
  String SQL2 = "SELECT * FROM datetime";
  String SQL3 = "SELECT * FROM timestamps";

  long start = 0;
  long end = 0;

  System.out.println("ONE");
  start = new Date().getTime();
  selectQuery(SQL1);
  end = new Date().getTime();
  System.out.println(" SQL 1 - dateandtime " + (end - start));

  System.out.println("TWO");
  start = new Date().getTime();
  selectQuery(SQL2);
  end = new Date().getTime();
  System.out.println(" SQL 2 - datetime " + (end - start));

  System.out.println("THREE");
  start = new Date().getTime();
  selectQuery(SQL3);
  end = new Date().getTime();
  System.out.println(" SQL 3 - timestamps " + (end - start));
}

The time to fetch kept on reducing with every subsequent calls.

 SQL 1 - dateandtime 4526 ms
SQL 2 - datetime 2852 ms
SQL 3 - timestamps 3577 ms

SQL 1 - dateandtime 4168 ms
SQL 2 - datetime 2467 ms
SQL 3 - timestamps 3073 ms

SQL 1 - dateandtime 4080 ms
SQL 2 - datetime 2346 ms
SQL 3 - timestamps 3130 ms

SQL 1 - dateandtime 3949 ms
SQL 2 - datetime 2419 ms
SQL 3 - timestamps 3043 ms

So looks like DATETIME wins in fetching speed.

DATETIME vs TIMESTAMP vs DATE & TIME

I'm starting off this project and wanted to study some data retrieval optimization values. DATE & TIME are the two most deciding factors for processing the information in my app. The aggregation, classification, sorting & grouping of data is based on DATE & TIME.

Daily reports
Weekly reports
Every day at 00:00 hours.
Every year on this date

So there is a huge amount of chronlogical processing. We might require to process the data just date, just time, or both date & time. So was born the question. "What is the most optimum way of storing information DATE & TIME, DATETIME or TIMESTAMP?" The initial study helped me find this.

From the MySQL manual...

Storage Requirements for Date and Time Types

Data Type Storage Required

DATE 3 bytes

TIME 3 bytes

DATETIME 8 bytes

TIMESTAMP 4 bytes

YEAR 1 byte

The storage requirements shown in the table arise from the way that MySQL represents temporal values:

DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32
TIME: A three-byte integer packed as DD×24×3600 + HH×3600 + MM×60 + SS

DATETIME: Eight bytes:

A four-byte integer packed as YYYY×10000 + MM×100 + DD
A four-byte integer packed as HH×10000 + MM×100 + SS

TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)
YEAR: A one-byte integer

So in terms of data storage, DATETIME is 8 bytes, TIMESTAMP 4 bytes, DATE & TIME 6 bytes (3 each). Ideally TIMESTAMP is good enough, if it fits my needs.

8 bytes > 6 bytes > 4 bytes

Memory is getting cheaper by the day, so let's ignore this for the time being, we'll revisit the storage factor a bit later.

Since I have to fetch information and process it, I decided to run some test in MySQL. Below is the schema of the database.

CREATE DATABASE datetest;

USE datetest;

DROP TABLE IF EXISTS dateandtime;

DROP TABLE IF EXISTS datetime;

DROP TABLE IF EXISTS timestamps;

CREATE TABLE dateandtime (
 timeonly TIME,
 dateonly DATE,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (timeonly, dateonly));

CREATE TABLE datetime (
 dateandtime DATETIME,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (dateandtime));

CREATE TABLE timestamps (
 timestamps TIMESTAMP,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (timestamps));

I added approximately 100,000,000 records to each table, and then ran further test on it. As of now I'm yet to write the test cases, after I'm done I'll put the files on.

Found another interesting post, you might want to touch base on.
http://www.scribd.com/doc/2565263/The-top-20-design-tips-for-MySQL-Enterprise-data-architects

Bibliography

http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

Agile environment using virtualization

(This document is aimed more towards Java & VMWare, but the same can be replicated for any other language & environment)

Abstract

You have a team of developers working on different modules for a project-product which are inter-dependant. Each developer diligently writes unit & integration tests supporting their code. You want to set-up an agile test environment to run the unit tests & staging server for integration test, but purchasing hardware for multiple machines is a constraint.

The following article provides a guide on how virtualization tools like VMware can be used to set-up staging or QA environment (Agile), with approximate minimum hardware & time investment.

Hardware Sizing

The hardware sizing performed here is not an exact calculation, but an educated guess at the approximate system requirement to run the application.

Application	RAM	CPU
Revision Control (SVN or CVS)	512MB - 1GB	Single
Continuous Integration (Cruise Control or Bamboo)	1GB - 2GB	Single
Database (MySQL*)	2GB - 4GB	Single-Dual
Webserver (Wiki, Bugzilla, JIRA)	1GB	Single

*MySQL on VMware has some serious performance issues for production environments, if your application is very database intensive with thousands to millions of I/O per second, you might want to avoid virtualization all together. For testing purpose (application sanity, NOT performance) VM is decent.

Considering the above list of applications, and the assumption, each physical core can support 2 virtual cores.

Hardware configuration

Quad-core processor CPU
8GB of RAM
5 HDDs (1 OS + 4 HDD with RAID**)
Other basic required components

**Setting up RAID and the type of configuration suiting your needs is a seperate chapter all together, We consider mirrored and stripped there are loads of articles available on the internet on this topic.

The approximate cost of the above system is roughly $2,500.00 - $3,500.00 (as the date of this article)

Set-up

You can either install Windows or Linux (we are on an Ubuntu system) as the HostOS. Since you have 8GB of RAM on the physical machine, 32 bit OS's are not capable of using above 3-4GB. Use 64-bit distributions.

Download VMWare server from [http://www.vmware.com/products/server] select the distribution type depending on your OS. The guest and the host OS can be completely different, they need not be same there is no relation between them.
eg: You can install Windows as host and Linux as guest or vice-versa.

You can either create the virtual machines all by yourself using the VMware step-by-step wizard, or download 'appliances' created by others from the VMware site [http://www.vmware.com/appliances], this saves you the initial installation effort.

When you create a new VM, you are also asked the default networking connection type. NAT and Bridged are the most common options one selects from. Use NAT if you want the VM to talk within its own subnet only, or Bridged if you want other machines on the same subnet as the host access them.
eg: Your host machine is on 192.168.10.50

Tip: If you installing the guest yourself, you can create one master guest, with all the common applications configured (eg: OpenSSH server would be great to have on all the guests for remote management or java), you'll have to change the host name after that.

Power up each of the VM's and install the required applications for which they've been set and use them like any other machine on the network.

Your source files are versioned on the Revision Control Server
Your tests are run nightly on the Cruise Control Server
Your team documents the whole project and process on the Wiki, and file bugs in Bugzilla
Your MySQL server is used for running the DB & the supporting DBs for other apps (bugzilla, wiki)

These VM's need to be maintained like any other normal physical machine on the network.

Pitfalls

Putting all your eggs in one basket, if the main VMware server fails, all the "machines" (VM's) fail.
Since the server is dependant on the native OS it sits on, the capabilities of the VMServer is restricted by the OS
Solution: Use commercial ESX Server or other alternatives

Alternatives

You can use the Xen too for virtualization. I've heard pretty good things about it too, with a few short comings & advantages over VMServer (that's a separate topic all together).

There is an article dedicated to Virtual Machines on Wikipedia, you can refer that depending on your needs and fix upon a solution that suits your needs better

Glossary

Virtualization - The virtual machine simulates enough hardware to allow an unmodified "guest" OS (one designed for the same CPU) to be run in isolation

Continuous Integration - Continuous integration describes a set of software engineering practice's that speed up the delivery of software by decreasing integration times

Revision Control - Revision control (also known as version control (system) (VCS), source control or (source) code management (SCM)) is the management of multiple revisions of the same unit of information

Host OS - The operating system installed on the physical machine running VMware Server.

Guest OS - The operating system installed on the virtual machine.

Bibliography

Data Type	Storage Required
`DATE`	3 bytes
`TIME`	3 bytes
`DATETIME`	8 bytes
`TIMESTAMP`	4 bytes
`YEAR`	1 byte