The Database of CALICE SQLite vs MySQL by shameona

VIEWS: 374 PAGES: 21

									The Database of CALICE
   SQLite vs MySQL
  Structured Query Language

          Wu Tao
            Information Board
■ Information extracted from website

■ SQLite:


2009-2-11                              2
            Performance & Speed

     Features                    SQLite MySQL
     highly optimized            Y          Y

     internal architecture
     Small memory                Y          Y

                                 N          Y
     Plugin and sockets
                                 N          Y
     overheads daemon
     Fast/reliable/easy to use   Y          Y

2009-2-11                                       3
                                      SQLite MySQL
Cross-platform supports               Y     Y
Single file/lib(s), portable          Y     Y more
Large mount of Contributed software   N     Y
Works for different compilers         Y     Y
library is self-contained             Y     Y
Open Sources                          Y     Y
GNU supports                          N     Y

2009-2-11                                        4
                                 SQLite MySQL
Complex user grants scheme       N     Y
Specified controlling permissions N    Y
Privilege and password system    N     Y
password traffic is encrypted    N     Y
when you connect to a server
Permissions to access to         N     Y
particular tables and records

2009-2-11                                   5
                                     SQLite MySQL
Allow user-defined functions         Y     Y
Custom function multi-language       Y     Y
Command-line programing              Y     Y
Graphical programming                N     Y
Error messages to clients (ANSI)     N     Y
Different character sets for         N     Y
sorting and comparison (EN/FR/IT…)

2009-2-11                                       6
                                       SQLite MySQL
Embedded system supports               Y      Y
Working in client/server               N         Y
Relational Data Base Management        N         Y
network protocol (TCP/IP,SSL)          N         Y
Sockets & Toolkits (Linux/Unix)        N         Y
Powerful to involve massive DB files   N (2TB)   Y (64TB)
High frequency of slow write operations N        Y
Powerful backup implementation         N         Y
2009-2-11                                                   7
            Interface        SQLite   MySQL
PHP                          Y        Y
C/C++                        Y        Y
Perl, Python, Java           Y        Y
Tcl                          Y        Y
.NET                         Y        Y
Ruby                         Y        Y
Graphical Inteface           N        Y
Network protocol             N        Y
Open Database Connectivity   N        Y
2009-2-11                                     9
  What do we care about SQLite
■ Small, fast, reliable
■ Database back-end tool
■ Support embedded system & application
■ Optimized Internal Data Manipulation
■ File storage: easily & quickly
■ Not well suited for Network protocol
■ No Server/Client Applications & Backup
■ Not powerful for permission control
■ Not suited for Multi-process or multi-
  threaded application
2009-2-11                                  10
 What do we care about MySQL
■ Nearly all features that SQLite have
■ Sever/Client, Network protocol
■ Security: Account/Grant/Revoke/OS/network/Data
  encryption/Locking (tables, pages, rows …)
■ Multi-Version Concurrency Control, multithreading
■ Multiple concurrent user support
■ Storage engine (InnoDB)
■ Concurrent backups (backups can be taken while
  the database is serving users)
   Personal Opinion: MySQL
■ Logging
2009-2-11                                       11

2009-2-11            12
               For discussion
■ Do we use Triggers? A trigger is a named data-
  base object that is associated with a table and that
  is activated when a particular event occurs for the
■ Concurrent backups (backups while the database is
  serving users)
■ Multiple concurrent user support (including the
  concurrency and necessary locking).
■ A relational database (RDBMS) stores data in
  separate tables rather than putting all the data in
  one big storeroom. This adds speed and flexibility.
■ Storage procedures: what model? Clusters?
■ Database engine: InnoDB or NDB?
 2009-2-11                                         13
                     Storage engine

                 Connection management, security

                SQL parsing, execution, caching, etc.

            MyISAM     InnoDB                    NDB

       Storage engines

2009-2-11                                               14
 Storage Engines features in MySQL
Attribute               MyISAM        Heap          BDB               InnoDB
Transactions            No            No            Yes               Yes

Lock granularity        Table         Table         Page              Row
                                                    Single file per
Storage                 Split files   In-memory                       Tablespace(s)
Isolation levels        None          None                            All
Portable format         Yes           N/A           No                Yes

Referential integrity   No            No            No                Yes

Primary key with data   No            No            Yes               Yes
MySQL caches data
                        No            Yes           Yes               Yes
Availability            All versions All versions   MySQL-Max         All Versions

 2009-2-11                                                                     15
    Choosing a Storage Engine I
Feature                    MyISAM   Memory   InnoDB   Archive   NDB
Storage limits             256TB    Yes      64TB     No        384EB
Transactions               No       No       Yes      No        Yes
Locking granularity        Table    Table    Row      Row       Row
MVCC (snapshot read)       No       No       Yes      Yes       No
Geospatial support         Yes      No       Yes      Yes       Yes
B-tree indexes             Yes      Yes      Yes      No        Yes
Hash indexes               No       Yes      No       No        Yes
Full-text search indexes   Yes      No       No       No        No
Clustered indexes          No       No       Yes      No        No
Data caches                No       N/A      Yes      No        Yes
Index caches               Yes      N/A      Yes      No        Yes

2009-2-11                                                               16
    Choosing a Storage Engine II
Feature                                 MyISAM Memory InnoDB Archive NDB
Compressed data                         Yes     No     No    Yes    No

Encrypted data[2]                       Yes     Yes    Yes   Yes    Yes

Cluster database support                No      No     No    No     Yes

Replication support[3]                  Yes     Yes    Yes   Yes    Yes

Foreign key support                     No      No     Yes   No     No

Backup / point-in-time recovery         Yes     Yes    Yes   Yes    Yes

Query cache support                     Yes     Yes    Yes   Yes    Yes

Update statistics for data dictionary   Yes     Yes    Yes   Yes    Yes

2009-2-11                                                                17
  Performance Related Question
ONLY software is not enough:
■ RAID: 0 1 5 10
■ File systems: ext2/ext3/XFS/NTFS…
■ Swap, Threading
■ Solving I/O,CPU,MEM,Kernel Bottlenecks
■ Load balancing and high availability

2009-2-11                              18
             Toy: Load Balancing
                                    Client network
                    Load balancer

     Web servers

                   read                  write

            Load balancer              MySQL master

                      read           replication

                                        MySQL slaves

2009-2-11                                              19
             Toy: Cluster Partitioning
                 Search cluster                  User cluster

         Load balancer                Load balancer

               read                                         write

MySQL slaves                                                        MySQL slaves



                                  MySQL master      MySQL master 2
 2009-2-11                                                                   20

 Eligible Client     query


                             Queried results

            Client                             Server

2009-2-11                                                            21
      Some test reports in the web
Performance comparison           SQLite 3.3.3   MySQL 5.0.18
1000 inserts                     3.823          2.647
25000 inserts in a transaction   0.764          7.038
5000 selects with an index       1.872          3.725
100 selects without an index     3.153          2.718
100 selects on a string          4.853          3.424
   Both are in the
inner join without an index
                  14.473                        3.421
 comparable level
Creating an index 0.552                         1.159
inserts from a select             1.179         0.986
Inner join with index on one side 0.371         0.408
 2009-2-11                                                22

To top