PositionPapersSlides/SIA_Chandra.doc

assistantashamedΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 9 μήνες)

266 εμφανίσεις


ITK 478

BHALCHANDRA

SPECIAL INTEREST ACTIVITY


FALL 07




TOPIC:
Comparison between MySQL and PostgreSQL for ease of
installation and administration from a layman’s perspective.





Introduction and B
ackground


Database management systems

are important for any business and today we find every
mom and pop business going online. I
n

this hands on activity

I am trying to evaluate
open source DBMS options MySQL and PostgreSQL from the point of view of a sma
ll
E
-
Commerce business

(basically a business where there is minimal IT related
knowledge)
. Both these database are industrial strength databases and enjoy wide
following. Craigslist, Yahoo, Wikipedia, Flickr use MySQL and Genetec
h, BASF, Skype
use PostgreS
QL
. Any E
-
commerce business irrespective of its size needs to store
data about 1) Products offered 2) Customer related information 3) Order status 4)
Visitor browser behavior etc. also sometimes there is a need to embed a database in
the software solu
tions that the company is offering.

Activity Abstract:

In this both the databases were installed on machine/s with Windows OS

1) XP Home Edition and 2) Vista Professional.

Tables were created and the experience was compared from point of view of an
appli
cation similar to tumble book.com

In my position paper “
MySQL vs PostgreSQL for a Small Scale E
-
Commerce Business.”

I came to a conclusion that there is not much to differentiate in these two options based
on technical issues ( w.r.t. our application) how
ever issues like ease of installation,
ease of administration, availability of support in case we run into trouble are going to
count a lot especially when you are not an IT person.

So to get first hand feel of what can happen as far as our application is

concerned I
installed both the databases on my laptop

having Windows Vista Business and on my
desktop having Windows XP Home Edition.

Tables were created and I tried to store the swf files as blob type in tables. Now there
are arguments in favor of this a
nd against this but instead of getting into those details I
decided to save blobs in tables.

The max size of blob that is to be stored is 12MB.










HANDS ON COMPONENT

DATABASE INSTALLATION


Download MySQL and MySQL Administrator










Tabl
e creation and data insertion

in MySQL

1) CLICK ON “Catalogs” IN MYSQL ADMINISTRATOR


2) RIGHT CLICK ON “test” SELECT “Create New Schema”


3
) Enter table name say “myfirsttable” and with “columns and indices” tab selected add
columns say “col1” select a
ppropriate data type.

Click on “Apply Changes”



6) Click on “Execute”


7) Click on “Tools” Select “MySQL Query Browser” Click it



8) Select the table from Myfirstschema and run sel
e
ct * query….by clicking on the
“Execute”Button


9) To add data selec
t field and click “Edit”


10) Click on “Apply Changes”



11) To add data in LONGBLOB field


http://dev.mysql.com/doc/query
-
browser/en/mysql
-
query
-
browser
-
usi
ng
-
blobs.html

IMPORTANT:

In MySQL Administrator Select “Startup Variables”, Select “Advanced
Networking”
tabs

and adjust “Max Packet Size” adjust according to need and Click
“Apply Changes”


Select file and click “Open” and click on “Apply Changes”


Issu
es encountered in installation

1)

For the records after installing both the databases on XP Machine I had to format
the disc a
s CPU Usage was constantly 100%
.

Now this may or may not be
related to i
nstallation.

2)

Installation

of MySQL

on Windows Vista Business
was possible only after turning
User Account Control Off.


MySQL Administrator is an excellent tool that allows you easy creation of tables, data
editing,

User administration etc.

http://www
.mysql.com/products/tools/administrator/

















Download PostgreSQL and pgAdminIII







CREATION OF TABLES IN PostgreSQL

Open pgAdminIII and double click on selected database server, enter password on the
popped up window and click OK




Now to insert data in our newly created table Click on “Tools” select “Scripts” and select
“INSERT Script”


Or alternately



Issues
involved in download and table creations

1)

There are so many options are available for downloading that it is quite confu
sing
to decide the right download.

2)

In PostgreSQL
for insertion

of “blob” pgAdminIII does not provide any sort of drag
and drop way I tried using Navicat also but that also offers no convenient way of
doing blob insertion
.

Installation of PostgreSQL along
with pgAdminIII was an easy job. As far as table
creation and User administration was concerned; for normal tables involving the usual
data types like integer, varchar; table creation and data insertion was quite simple,
however since we have to
insert swf

files in our table

there is no convenient way to do it
and when I say convenient I mean sort of drag and drop. The other tool Navicat for
PostgreSQL also do not provide any convenient way to insert blobs in a table.

User community and available resources.

If we search for word PostgreSQL in Title
of book at Milner we get 3 results


A similar search for MySQL returns 35 results


Now this cannot be called as true reflection of support available for each database but
can be considered as a good indicator of

the same.

Conclusion:

Both the databases are
easy to install and administer but if we must compare, then
MySQL is a bit more convenient.

For our application MySQL works out better
,

thanks to MySQL Administrator
, we

could
actually create a table
containing

our

swf files

which on extraction from the table
displayed the content as expected.

Despite trying for a long time I am not able to find a convenient way to insert blobs in
postgreSQL and I will continue my search in this regard. As far as MySQL is concer
ned
I encountered no such issue which made me search the web for a solution.

So if I have to pick a choice at this juncture MySQL is a clear winner.

REF:


www.mysql.com


www.postgresql.org