High Throughput Databases with SqlAnywhere

Document Sample
High Throughput Databases with SqlAnywhere Powered By Docstoc
					                                 Achieving Higher
                                 Throughput with Your
                                 SQL Anywhere
                                 Database
                                 Todd A. Loomis
                                 Chief Systems Architect
                                 CodeWorks Software Systems, LLC




Sybase Confidential Propriety.
Who is Todd


  Independent consultant
  Specialty in database design, development
   frameworks, and systems architecture
  Taught language theory and relational databases at
   University of Maryland, Baltimore County (UMBC)
  Has worked with SQL Anywhere for over six years
What is a High Throughput System


  Rotating window of data collection
  Logging and aging of data
  Typically collect data from many locations
  Large amounts of data: 50 to 150 gigabytes
Focus on Performance


  Every decision needs to be thought of with respect to
   how it will affect performance
  The goal is to grab a little bit of speed from every
   possible area
Focus on Performance


  Database Design
  Indexing
  Database Options
  Hardware Considerations
  Backup and Recovery
  Testing
Database Design


  A ‘properly’ designed schema is not necessarily a
   fast performing schema
  Stick with core database functionality
  Simpler is faster
Wide Tables


  Table width is a key cause of slow performance
  Wide tables lead to inefficient page usage
  If you can live without it, don’t store it
  This is not license for 5th normal form
Constraints and Triggers


  Avoid triggers on high throughput tables
  Move Constraints into the middleware
    Value boundaries
    Integrity constraints
    Not null constraints
    Default values other than primary key
Auto Increment Primary Keys


  Use surrogate keys, rather than time or compound
   keys
  Insert rates will decrease slightly due to the extra
   field
  Delete rates will be greatly increased
  Use unsigned bigint
Indexing


   The indexing paradox
     insert vs select rates
   Keep to a minimum index set
   Multi-field indexes vs single field
   Educate developers on acceptable methods of
    accessing data
   Be wary of dynamic SQL
Foreign Key Indexes


  Remove FK’s that are only used for ‘completeness’
  Descriptive fields which are never part of a query
  Educate developers
The Test Machine


  Basic desktop system
  Single Pentium 4 processor
  1.5 gigabytes of RAM
    Seagate Barracuda 7200rpm disk
  100MBps disk throughput
Performance Test 1 - Baseline


   Initial schema prior to any changes for better
   performance

                                                   Temperature_Data
                                         Temp_Type_Code     varchar(10)   <pk,fk1>
                                         Sensor_Id          integer       <pk,fk2>
                                         Collection_Time    timestamp     <pk>       FK_TEMPERAT_REFERENCE _SENSOR
     FK_TEMPERAT_REFERENCE _TEMPERA T
                                         Temperature        float
                                         Humidity           float
                                         Temp_Status        varchar(8)                               Sensor
         Temperature_Type
                                         Measurement_Type   varchar(1)
Temp_Type_Code      varchar(10)   <pk>                                                 Sensor_Id         integer        <pk>
                                         Val_1              float
Temp_Type_Name      varchar(10)                                                        Sensor_Location   varchar(128)
                                         Val_2              float
Temp_Type_Display   varchar(10)                                                        Longitude         varchar(16)
                                         Val_3              float
                                                                                       Lattitude         varchar(16)
                                         Val_4              float
                                         Val_5              float
                                         Val_6              float
                                         Val_7              float
                                         Val_8              float
                                         Val_9              float
                                         Val_10             float
Performance Test 1 - Results


 All other tests will be compared to this one



       Test                 Insert Time Delete Time
       Test 1 - Baseline           129.2       100.3
Performance Test 2


 Temperature_Stats table is trimmed of excess data

              Temperature_Data                         Temperature_Data
     Temp_Type_Code     varchar(10) <pk,fk1>
                                               Temp_Type_Code     varchar(10) <pk>
     Sensor_Id          integer     <pk,fk2>
     Collection_Time    timestamp <pk>
                                               Sensor_Id          integer     <pk>
     Temperature        float                  Collection_Time    timestamp <pk>
     Humidity           float                  Temperature        float
     Temp_Status        varchar(8)             Humidity           float
     Measurement_Type   varchar(1)             Temp_Status        varchar(8)
     Val_1              float
     Val_2              float
                                               Measurement_Type   varchar(1)
     Val_3              float
     Val_4              float
     Val_5              float
     Val_6              float
     Val_7              float
     Val_8              float
     Val_9              float
     Val_10             float
Performance Test 2 - Results


 Trimmed 10 float values from table


     Test                            Insert Time Delete Time
     Test 1 - Baseline                      129.2       100.3
     Test 2 - Remove Excess Fields          117.1         98.5
     Percentage Increase                    9.37%       1.79%
Performance Test 3


 Add surrogate primary key
 create table TEMPERATURE_DATA                                  create table TEMPERATURE_DATA
 (                                                              (
   TEMP_TYPE_CODE              varchar(10)          not null,     TEMP_DATA_ID                integer
   SENSOR_ID           integer             not null,                          not null default autoincrement,
   COLLECTION_TIM E            timestamp            not null,     TEMP_TYPE_CODE                 varchar(10),
   TEMPERATURE              float,                                SENSOR_ID              integer,
   HUMIDITY            float,                                     COLLECTION_TIM E               timestamp,
   TEMP_STATUS             varchar(8)                             TEMPERATURE                 float,
       check (TEMP_STATUS is null or                              HUMIDITY               float,
       ( TEMP_STATUS in ('VALID','INVALID') )),                   TEMP_STATUS                varchar(8)
   MEASUREM ENT_TYPE varchar(1) default 'F'                           check (TEMP_STATUS is null or
       check (MEASUREM ENT_TYPE is null or                             ( TEMP_STATUS in ('VALID','INVA LID') )),
       ( MEASUREMENT_TYPE in ('F','C') )),                        MEASUREM ENT_TYPE varchar(1)                default 'F'
   VAL_1           float,                                             check (MEASUREM ENT_TYPE is null or
   VAL_2           float,                                            ( MEASUREM ENT_TYPE in ('F','C') )),
   VAL_3           float,                                         VAL_1              float,
   VAL_4           float,                                         VAL_2              float,
   VAL_5           float,                                         VAL_3              float,
   VAL_6           float,                                         VAL_4              float,
   VAL_7           float,                                         VAL_5              float,
   VAL_8           float,                                         VAL_6              float,
   VAL_9           float,                                         VAL_7              float,
   VAL_10           float,                                        VAL_8              float,
   primary key (TEMP_TYPE_CODE, SENSOR_ID,                        VAL_9              float,
 COLLECTION_TIM E));                                              VAL_10              float,
                                                                  primary key (TEMP_DATA_ID) );
Performance Test 3 - Results


 Add system generated primary key


       Test                         Insert Time Delete Time
       Test 1 - Baseline                   129.2       100.3
       Test 3 - Use Autoincrement          132.6          5.2
       Percentage Increase                -2.63%      94.82%
Performance Test 4


 Remove indexes from Temperature Data

        /*==============================================================*/
        /* Index: TEMP_DATA_IDX_1                 */
        /*==============================================================*/
        create index TEMP_DATA_IDX_1 on TEMPERATURE_DATA (
        SENSOR_ID ASC,
        COLLECTION_TIM E ASC
        );

        /*==============================================================*/
        /* Index: TEMP_DATA_IDX_2                 */
        /*==============================================================*/
        create index TEMP_DATA_IDX_2 on TEMPERATURE_DATA (
        COLLECTION_TIM E ASC,
        TEMP_TYPE_CODE ASC
        );
Performance Test 4 - Results




       Test                        Insert Time Delete Time
       Test 1 - Baseline                  129.2       100.3
       Test 4 - Remove 2 Indexes          128.9         NA
       Percentage Increase                0.23%         NA
Performance Test 5


 Remove foreign keys from Temperature_Data

        alter table TEMPERATURE_DATA
          add foreign key FK_TEMPERAT_REFERENCE_TEMPERAT (TEMP_TYPE_CODE)
            references TEMPERATURE_TYPE (TEMP_TYPE_CODE)
            on update restrict
            on delete restrict;

        alter table TEMPERATURE_DATA
          add foreign key FK_TEMPERAT_REFERENCE_SENSOR (SENSOR_ID)
            references SENSOR (SENSOR_ID)
            on update restrict
            on delete restrict;
Performance Test 5 - Results


 Dropped two foreign keys

     Test                   Insert Time Delete Time
     Test 1 - Baseline             129.2       100.3
     Test 5 - Remove FKs           126.4         98.4
     Percentage Increase           2.17%       1.89%
Performance Test 6


 Remove field constraints, and defaults
  create table TEMPERATURE_DATA                         create table TEMPERATURE_DATA
  (                                                     (
    TEMP_TYPE_CODE          varchar(10)     not null,     TEMP_TYPE_CODE          varchar(10)  not null,
    SENSOR_ID               integer         not null,     SENSOR_ID               integer      not null,
    COLLECTION_TIM E        timestamp       not null,     COLLECTION_TIM E         timestamp    not null,
    TEMPERATURE              float,                       TEMPERATURE              float,
    HUMIDITY                 float,                       HUMIDITY                 float,
    TEMP_STATUS             varchar(8)                    TEMP_STATUS              varchar(8),
        check (TEMP_STATUS is null or                     MEASUREM ENT_TYPE varchar(1),
        ( TEMP_STATUS in ('VALID','INVALID') )),          VAL_1           float,
    MEASUREM ENT_TYPE varchar(1) default 'F'              VAL_2           float,
        check (MEASUREM ENT_TYPE is null or               VAL_3           float,
        ( MEASUREMENT_TYPE in ('F','C') )),               VAL_4           float,
    VAL_1           float,                                VAL_5           float,
    VAL_2           float,                                VAL_6           float,
    VAL_3           float,                                VAL_7           float,
    VAL_4           float,                                VAL_8           float,
    VAL_5           float,                                VAL_9           float,
    VAL_6           float,                                VAL_10           float,
    VAL_7           float,                                primary key (TEMP_TYPE_CODE, SENSOR_ID,
    VAL_8           float,                              COLLECTION_TIM E));
    VAL_9           float,
    VAL_10           float,
    primary key (TEMP_TYPE_CODE, SENSOR_ID,
  COLLECTION_TIM E));
Performance Test 6 - Results




      Test                          Insert Time Delete Time
      Test 1 - Baseline                    129.2       100.3
      Test 6 - Remove Constraints            116         87.2
      Percentage Increase                10.22%       13.06%
Performance Test 7


  Combine all changes from previous tests
      Trim table
      Add surrogate key
      Remove indexes
      Remove foreign keys
      Remove table constraints
Performance Test 7 Results




     Test                              Insert Time Delete Time
     Test 1 - Baseline                        129.2       100.3
     Test 7 - All Schema Modifications         95.5          1.9
     Percentage Increase                    26.08%       98.11%
Database Options


  Streamline Commits
    Delayed_Commits = ‘On’
    Cooperative_Commits = ‘Off’
  Experiment with unknown options, but validate them
   through testing
Options Example




     Test                                Insert Time Delete Time
     Test 1 - Baseline                            129.2      100.3
     Test 7 - All Schema Modifications             95.5        1.9
     Test 8 - Set Commit Options                     60        NA
     Percentage Increase from Test 7            26.08%         NA
     Percentage Increase from Test 1            53.56%         NA
Hardware Considerations


  Memory
  CPU
  Disk Subsystem
  Operating System
  File System
Solaris File Systems



    Veritas or UFS
    UFS - noatime, forcedirectio, logging
    Whatever the filesystem, search for settings that
     may help performance
    Disk Location
      local vs remote
      SANs
Disk Subsystem


  Throughput Rate
  Disk Controller
  Cache
  Disk Speed
  Hardware RAID
    Never use software RAID
Database Physical Settings


   Database page size
     Best to match to physical format page size
     4096 is typical
   Memory
     Allocate enough to the database to keep from using
      a Temp File
   Striped disks, vs multiple disks and multiple
    database files
Page Size Example




      Test                                Insert Time Delete Time
      Test 1 - Baseline                            192.2      100.3
      Test 7 - All Schema Modifications              95.5       1.9
      Test 9 - Reduce Page Size                       100       NA
      Percentage Increase from Test 7             -4.71%        NA
Backup and Recovery


  Dealing with 100 plus gigabytes
  Backup elapse time and performance during backup
  Automatic recovery issues
  Recovery scenarios
  Running dbvalid
    Run on backup copy, to validate the database
    Only run on production if a problem exists
Mirroring Backup Strategy


  Mirroring can provide a fast recovery solution
  Move to mirrored copy instead of recovering
  Disks are inexpensive, don’t cheap out
Incremental Backup Strategy


  Weekly Full Backup
  Nightly Backup of Log file
  Better point in time recovery
  Recovery process should be practiced, no matter
   which method is chosen
Benchmark Testing


  If you can’t measure it, you can’t manage it
  Determine a set of tests, to compare performance
   changes against
  Tests must be repeatable and done in a controlled
   environment
  The ‘First Test’ issue
    Page allocation
Conclusion


  With planning, design, testing, and some self
   restraint, SQL Anywhere makes for an excellent
   database to support high throughput
  Think out of the box
  Hunt for settings and options, test for performance,
   and keep what works
Thank you for listening


  For further questions
   Todd Loomis
   CodeWorks Software Systems
   603-778-4236
   t.loomis@databaseGunForHire.com
   www.databaseGunForHire.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:4/28/2013
language:Unknown
pages:39