MySQL

1
MySQL

• Worlds Most Popular Open Source Database
• Mustafa Ilhan
• Somphone Soukhaseum
• Hiroaki Nakano

2
What is MySQL?

• Open-source, free, client-server RDBMS
• Invented by T.c.x in 1979 in Scandinavia
• Today, MySQL AB is the company that develops,
  supports, and markets mySQL
• Is used for web db, e-commerce applications, data
  warehousing, and distributed applications
• Can be downloaded for free from its website
• GNU General Public License

3
What is Open-Source

• Organizations can modify the product for their
  own use
• Users can freely access programs and change
  instructions
• People improve, adapt it, and fix bugs
• If you dont understand how it works, with your
  web browser you can reach the source code, if you
  dont like it, you can change the code
• Other open-source Apache web server, Linux OS,
  Netscape Communicator
• Other open-source db mSQL, Postgres

4
What MySQL offers

• Handle unlimited number of simultaneous users
• More than 50.000.000 records
• Perhaps fastest command execution in the market
• Easy and efficient user privilege system
• Stable and easy to use
• Flexible, robust, reliable
• Multithreaded performs many tasks at the same
  time as if multiple instances of the application
  are running simultaneously

5
Other features

• SQL is used to read, write, and manipulate data
• Used by 3 million companies
• Runs on many platforms Windows NT/2000/XP, Mac
  OS, Linux, Solaris, Unix
• Cross-database joins queries that can join
  tables from different databases
• Internationalization several different character
  sets, error messages in other languages
• Can also be accessed using application that
  support ODBC
• Clients can use multiple databases simultaneously

6
Where MySQL lacks

• Not a true enterprise level RDBMS
• MySQL does not have sub queries
• No trigger or stored procedure
• No atomic rollback

7
The Company MySQL AB

• Sweden based, headquarter in Seattle, WA
• Sole owner of the mySQL server source, MySQL
  trademark, and mysql.com domain
• Private, without debt, and financed by venture
  capital
• Employs 55 staff, thousands more contribute
• Provides support, consulting services, training
  programs
• Sell advertising space on website which has more
  than 10.000.000 page viewers per month

8
What is maximum ?

• Table size the size of your hard drive
• Unlimited number of users (limited with number of
  network connections)
• Number of database limited to number of
  subdirectories OS can handle
• 2000 columns
• Size of table, index or field name 64
  charactersAliases can have up to 500 characters
• Number of index 16
• Amount of data in a row 65.000 bytes

9
Who is using MySQL ?

• 4.000.000 active users worldwide, 27.000 copies
  downloaded per day
• NASA
• Motorola
• Silicon Graphics
• Lucent Technologies
• Xerox
• Cisco
• Agilent
• Cyberweb
• Yahoo, Google
• Slashdot.org, MP3.com(Vivendi Universal)

10
Database Administration Issues

• Someone should watch over and know what to do
  when problems occur
• Similar to other database systems but it has its
  own requirements
• Low-maintenance because of its simplicity
• DBA task Installation, configuration and tuning,
  access control, logging, backup and recovery,
  table maintenance
• To perform, you need to have both administrative
  access to MySQL server, and OS

11
Operating Systems Supported

• AIX
• BSDI
• DEC Unix
• FreeBSD
• HP-UX
• Linux 2.0
• Mac OS X
• OpenBSD
• OS/2 Warp 3
• SGI Irix

• SGI Irix
• Solaris
• SPARC
• SunOS
• Caldera (SCO)
• Windows 9x, Me, NT, 2000 and XP.

12
MySQL Table Size

• Operating System File Size
  Limit
• Linux-Intel 32 bit 2G-4G
• Linux-Alpha 8T
• Solaris 2.5.1 2G
• Solaris 2.6 4G
• Solaris 2.7 Intel 4G
• Solaris 2.7 ULTRA-SPARC 8T

13
MySQL Table Types

• Transaction Safe Tables (TST)
• InnoDB
• BDB
• Non-transaction Safe Tables (NTST)
• HEAP
• ISAM
• MERGE
• MYISAM (default table type)

14
Security

• MySQL uses Access Control Lists (ACLs)
• List of access control entries that identifies a
  trustee and specifies the access rights
• Support for SSL for client/server connection
• Secure Socket Layer
• Most common client/server encryption schema

15
APIs

• C
• C
• PHP
• JAVA
• PYTHON
• PERL
• ODBC

16
Scalability

• Scales up and down like Linux
• Could run many instances at once
• Could run with only a few MB of memory
• Suitable for small devices
• Will be disk bound
• Could be recompiled to add/remove features
• Table types, query cache, etc.

17
List of companies with MySQL

• Yahoo! Finance ( and other parts of Yahoo)
• NASA
• U.S. Census Bureau
• Texas Instruments
• Sony Pictures Digital Entertainment
• Motorola
• State of Rhode Island
• Other companies all over the world

18
What is the purpose of the MySQL DBMS?

• To store data
• To retrieve it quickly
• To change (insert, update, delete) it quickly
• To do this easily
• From wherever it is called

19
Benefits of MySQL 1

• Speed (especially SQL SELECT statements )
• reliability
• scalability
• stability / no down time
• easy and fast migration process
• easy to install, configure, maintain
• easy to learn / SQL compliant /customizable code
• great reputation / popularity
• market dominance in the Linux community
• Best with LAMP (Linux-Apache-MySQL-PHP) setup
• compatibility with most of servers and operating
  systems

20
Benefits of MySQL 2

• Low total cost of ownership
• a very good price/performance ratio
• free under the GPL license
• low maintenance costs
• very little hardware
• support / responsive developers at MySQL
• Free and helpful online documentation
• Don't have to pay in performance for unused
  features

21
Negative factors of MySQL

• doesn't support transactions, offer views,
  triggers, native XML storage
• lacks a number of features
• Oracle's reputation
• lacks the ability to cater to high-end enterprise
  application environments such as Customer
  Relationship Management(CRM), Supply Chain
  Management(SCM) and Enterprise Resource
  Planning(ERP).

22
Benchmark
23
Benchmark
24
Speed of select statement
Column 1 is in seconds. All other columns are
presented relativeto this. 1.00 is the same,
bigger numbers indicates slower The number in ()
shows how many SQL commands the particular test
did.
25
(No Transcript)
26
(No Transcript)
27
MySQL
28
Oracle
29
DB2
30
MS SQL Server
31
Throughput
32
The Fastest Response Time
33
Oracle
34
Oracle vs. IBM DB2by INPUT, IT market research
firm

• Oracle Total Cost of Ownership 28 Less Than IBM
  DB2
• The TCO per named user per annum is 28 lower for
  Oracle Database than for IBM DB2 (including
  downtime).
• The TCO per named user per annum is 7 lower for
  Oracle Database than for IBM DB2 (excluding
  downtime).
• The TCO per transaction per minute is 19 lower
  for Oracle Database than for IBM DB2 (excluding
  downtime).

35
Oracle vs. Microsoft SQ Lby INPUT, IT market
research firm

• Oracle is 37 faster than Microsoft.
• Oracle9i Database has a lower total initial cost
  (TIC) than SQL Server. TIC (license, support,
  upgrade)
• Oracle is Half the Cost of SQL Server for
  Packaged Applications
• Secured environment 13 per Oracle vs. 53 per
  Microsoft user
•  all cost of ownership components--acquisition,
  implementation, and ongoing costs for database,
  hardware, and application software (SAP, Siebel,
  PeopleSoft).

36
Oracle vs. Microsoft SQ Lby INPUT, IT market
research firm

• Database-specific Cost of Ownership (Per User)
• Oracle Database Microsoft SQL
  Server
• Software License 29.54 6.05
• Consulting, Implementation 2.64 9.23
• Support, Maintenance, On-Going Ops 34.51
  102.43
• TOTAL 66.69 117.71  
• Source INPUT

37
Oracle vs. Microsoft SQ Lby INPUT, IT market
research firm

• Cost Microsoft SQL Server 2000 SE
  Oracle9iDB SE
• Initial License cost 1,490 1,500
• Upgrade Cost 373 225
• Support Cost 245 per incident 105
• Total 2,108 1,830
•  
• Cost MS SS2K EE Oracle9iDB SE
• Initial License cost (2 processors) 40,000
  30,000
• Upgrade Cost 10,000 4,500
• Support Cost 12,250 2,100
• eBusiness Discount (10) 6,225 (5) 1,830
• Total 56,025 34,770
• Price based on 50 users

38
MySQL vs. Oracle

• Cost MySQL Oracle9iDB SE
• Initial License cost 395 30,000
• Upgrade Cost 0 4,500
• Support Cost 1,500 2,100
• eBusiness Discount (10) 1,830
• Total 1,895 34,770
• One year Entry Level (email) Technical Support
  for Standard Customer

39
Something about Cost

• Dont have to pay in performance for unused
  features
• Low total cost of ownership
• Easy to install, configure (no need for paying
  help)
• Recommend to any kind of server side applications
  that store data to use to run their online store,
  internal research and development if there's
  little reason to not use high-end enterprise
  systems, such as ERP and CRM applications
• Still the fastest database

40
Conclusion

• Introduce a useful and considerable MySQL as
  worthy alternatives to proprietary databases,
  Oracle to your managerial decision