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: http://www.sqlite.org/

■ MySQL http://www.mysql.com/




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
                                 20≥SQL
                                 ~2>MySQL



2009-2-11                                       3
                    Portability
                                      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
                  Security
                                 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
               Customization
                                     SQLite MySQL
Allow user-defined functions         Y     Y
Custom function multi-language       Y     Y
supported
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
                     Scalability
                                       SQLite MySQL
Embedded system supports               Y      Y
Working in client/server               N         Y
Relational Data Base Management        N         Y
System
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
                   Interfaces
            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
            Backup




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
  table.
■ 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.



                                     HEAP
            MyISAM     InnoDB                    NDB
                                  In-memory


       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)
                                                       table
                                                    Read
Isolation levels        None          None                            All
                                                      committed
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
  records
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
■ SCSI or IDE
■ 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


                                                            replication



                                           replication

                                  MySQL master      MySQL master 2
 2009-2-11                                                                   20
                       Client/Server
                                         DB
                                                   Draw=tables

 Eligible Client     query
                                                           File=records


                                                 open


                             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
comparison
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