PowerPoint Presentation by 0ww0RNy

VIEWS: 0 PAGES: 17

									CSQL –Main Memory DBMS

          Prabakaran.T
            CSQL – Founder,
      Architect – Lakshya Solutions
     prabakaran.t@lakshyasolutions.com
             History & Road Map
Prototype     May,2005

Registered    Apr,2006 (sourceforge.net)

1.0 Beta      Feb,2007 (Storage Engine)

1.3 Beta      Feb, 2008 (SQL, ODBC, JDBC)

2.0 GA        May,2008 (Caching)

2.4 Beta      Feb,2009 (Replication)

2.6 Beta      Aug,2009 (Multiple DS, Order,
                     Outer Join)

3.0 GA        Sep, 2009 (Mgmt Interface,
              Windows Porting)
                            Team

Developers: 15

Around 50 years of collective experience in database system
implementation

Exp in both main memory and disk based database system

Backed by Lakshya solutions (www.lakshyasolutions.com)
                       Introduction

Open Source   – http://sourceforge.net/projects/csql
              - http://www.csqlcache.com
Enterprise    - http://www.csqldb.com

Main Memory Database
Small Footprint (2 MB)
Embedded architecture
High performance (100K selects/sec, 50K writes/sec)
Highly concurrent
Implemented in C++
Middle tier caching for any DRDB
High availability and load balancing - Replication
Supported Platforms : Linux – x86/x86_64, Solaris sparc
              MMDB is better than DRDB

Hardware trend:
   CPU speed, memory & Disk capacity grows 80%/yr
   Memory performance grows 10%/yr
   Disk speed remains same
   Price of memory continues to drop

•Access time for main memory is orders of magnitude less than
that of disk. (100ns VS 10 ms)
•Application queries require random access most of the
time(point lookups)
•Data resides permanently on main memory. No disk I/Os
•No buffer manager overhead
•Data Structures and algorithms are designed for memory
access rather than disk access
                      CSQL is faster

Performance: Developed from scratch keeping one thing in
mind- performance.

Direct access to data: uses shared memory architecture

User Level Mutexes: Hand written using atomic instructions

Custom Allocators : Fixed and Variable Size Allocators

Zero disk I/O for read: No buffer manager overhead

Embedded Architecture: No network overhead, no data copies

Tightly coupled: code segments which are performance
critical are tightly coupled to avoid data copies
CSQL Architecture
                   Interfaces Support


JDBC 3.0 – Java Standard Interface to SQL Kernel

ODBC 3.0 – C Standard Interface to SQL Kernel

CSQL – Command line interactive tool to SQL Kernel

SQLAPI – Proprietary C++ Interface to SQL Kernel
                                 SQL Support
DDL:
  •CREATE TABLE
  •DROP TABLE
  •CREATE INDEX
  •DROP INDEX

DML:
  •INSERT INTO <tablename> [fieldNameList] VALUES (valuelist)
  •UPDATE <tablename> SET fldname=value , ... [WHERE
  <condition>]
  •DELETE FROM <tablename> [WHERE <condition>]
  •SELECT <* | fieldNameList> FROM <tablelist> [WHERE
  <condition>] [ORDER|GROUP BY fldname>,…] [HAVING <cond>]

   condition shall be [NOT] <predicate> <AND | OR > <predicate>
   predicate shall be <fieldName> <"=, !=, >,<, >=, <="> [fieldName | value] |
                       <fieldName> [NOT] <BETWEEN value AND value> |
                       <fieldName >[NOT] <IN valuelist> |
                       <fieldName> LIKE for character datatype
   Aggregates: MIN, MAX, SUM, AVG, COUNT
                         Storage Manager

        Provides mechanism for storing, indexing, retrieving data
from the physical data store

•Atomicity – All or none
•Consistency – takes from one consistent state to another
•Isolation – behavior with respect to other transactions
•Highly concurrent - Multi Threaded Architecture
•Custom Allocators - Fixed and Variable size allocators
•Fault Tolerance – Process failures
•Multi user – Authentication, Roles
•Multi granular locking – Database, Table, Row
•Hash Index - for point lookup
•Tree Index - for Range queries
•Relational Objects - Table, Session
                                  Transaction

                                 Full ACID Properties

Physical and logical undo logs

Primary Key, Unique, Not Null, Foreign Key

Isolation Levels Support
          READ UNCOMMITTED,
          READ COMMITTED,
          READ REPEATABLE

Durability supported through write ahead redo logging and checkpoint
                             Fault Tolerant
Highly concurrent access
    Multi Process & Multi Threaded
    Lock free data structures and index structures

Server Process Failure
         Heart beat monitor
         Auto restart in case of failure

Application Process Failure
          Automatic cleanup of mutexes acquired by crashed process
          Automatic rollback of active transactions of crashed process

Media Failure
         Archive and Restore

High Availability
         Replication
                                Index


Hash Index
       Equality Predicates
       Fixed Hash Index
       Unique and Non Unique
       Unclustered – row and index are stored separately

Tree Index
       Range Predicates
       B+ Tree
       Unique and Non Unique
       Unclustered – row and index are stored separately
                  Wisconsin Benchmark Results
Machine Configuration
    – Dell Optiplex 320, Intel core 2 duo @2.0 GHz, with 1GB RAM on Linux 2.6
       Kernel
    – Wisconsin benchmark tables with 10K records using JDBC
    – Leading DB (tables fully loaded in memory)
    – All statements retrieves one record except “select-100” which retrieves
       100 records

                    Leading DRDB         CSQL MMDB           Times Faster
                            Hash Tree          Hash Tree           Hash Tree
   Statement Type NoIndex Index Index NoIndex Index Index NoIndex Index Index
 SelectInt -1          6097  331  325   247     11    11    24.68  30.09 29.55
 SelectStr-1           6495  979  356   286     16    15    22.71  61.19 23.73
 Select-100            6861  NA   826   508     NA    120   13.51   NA    6.88
 Insert – 1            218   265  213    20     13    11    10.90  20.38 19.36
 Update -1             5572  217  188   473     14    12    11.78  15.50 15.67
 Delete-1              5741  200  168   573     15    13    10.02  13.33 12.92
 Join (10K * 1K) -1    6459  320  292    35     11    11   184.54 29.09 26.55
 Join (10K * 10K) - 1 14916  411  320    36     13    14   414.33 31.62 22.86
                                                     All time in microseconds
                      Community Vs Enterprise Version
      Functionality           Open source community Edition                         Enterprise Edition
      Performance             20 times faster than leading DRDB             30 times faster than leading DRDB
       Atomicity                              Yes                                           Yes
      Consistency                             Yes                                           Yes
        Isolation                             Yes                                           Yes
                                                                           Soft shudown and for application crash
        Durability         Only in case of soft shutdown using archive       using redo logs and checkpoint

                                                                         High as it uses lock free internal structures
   Concurrent Access              Medium as it uses mutex                            and index structures
    JDBC and ODBC                      Primitive Support                    JDBC3.0 and ODBC3.0 compliant
                              Supports only synchronous update
                                          propagation                     Supports both sync and aysnc update
        Caching                            to target db                          propagation to target db
                                                                          Coherent cache across all nodes with
                                                                                          sync
   Multi node caching     Delayed propagation to other cache peers                and async propagation
Target Database for cache                MySQL                                  MySQL, Postgres, Oracle
   GUI Query Browser
     and monitoring                         No                       Yes. Supported using SquirrelClient
       Replication                          No                                      Yes
     Documentation                       Primitive                             Professional
                           Free- open source contributors with no
                                           SLA
Support and Maintenance             Expensive- Inhouse             Nominal Fee: Lakshya Experts with SLA
                        Roadmap
CSQL-3.0
   Improve Test Coverage
   Async Multi data source caching
   Conflict resolution for Asynchronous update
   propagation
   Hibernate support

CSQL-4.0
   Windows Porting
   Management Interface
   Sub-query and Set operations support
   Triggers
                        Summary

Supports Full ACID properties
SQL support
Standard ODBC and JDBC Interface
No or minimal application changes
Increased Throughput
Light Weight
High Availability and Load Balancing
Cache for MySQL, Postgres, Oracle, DB2, Sybase




          Contact: info@csqldb.com

								
To top