1 / 35

Create Database and Start Server

4. Create Database and Start Server. Create Database. OK so we have thought about the device or devices – about time we considered some of the options to create the database Of all of the options when issuing a CREATE DATABASE command the following slides details the most important.

brightl
Download Presentation

Create Database and Start Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 4 Create Database andStart Server

  2. Create Database • OK so we have thought about the device or devices – about time we considered some of the options to create the database • Of all of the options when issuing a CREATE DATABASE command the following slides details the most important

  3. Sub Clauses in Create Database • [ CASE { RESPECT | IGNORE } ] • [ PAGE SIZE ] • [ COLLATION ] • [ BLANK PADDING { ON | OFF } ] • [ IQ PAGE SIZE iq-page-size ] • [ BLOCK SIZE iq-block-size ]

  4. CASE • CASE • CASE RESPECT is the fastest • There is a 10-20% hit going to CASE IGNORE • Implications to FP Indexes (we will talk on this later) • Regardless of RESPECT vs. IGNORE all 1-byte and 2-byte FP indexes store all the binary values for the data • So ABC, abc, Abc, Abc are all stored even for CASE IGNORE

  5. PAGE SIZE • This is the size of page in the IQ Catalog Store • This is the ASA page size • According to the Manual and Sybase Central sizes are • 1024, 2048 and 4096 • But this will restrict the number of columns in a table • Actually ASA 7 supports 8192,16384 and 32768 • 32K will give 8,000 columns per table

  6. COLLATION • COLLATION • Set to ISO_BINENG, this is the fastest • If you must have a collation sequence this will slow the system down by around 10% for 8 bit character sets and 50% for multi-byte character sets • There are substantial slow downs for all multi-byte character sets

  7. IQ PAGE SIZE - 1 • This is an area of extreme contention • The IQ PAGE size (effectively) determines the the size of the smallest addressable area in memory • NOT ON DISK ! • The disk parameter is the BLOCK SIZE

  8. IQ PAGE SIZE – 2 • The rules for IQ PAGE SIZE (or memory buffer size) are simple • Set to 128K unless…. • Set to 256K when the memory model exceeds 4 Gbytes per cache (this may be a rare case!) • If even bigger then 512K is the one to use • For laptop and development server (less than 1-2 Gbytes per cache – set to 64K

  9. BLOCK SIZE • The block size is set automatically when the IQ PAGE SIZE is set • (In 11.x IQ you could set the Max Compression parameter that would vary the number of blocks per page) • In IQ 12, you can vary the BLOCK SIZE but it does not do much except in some extreme cases

  10. So what is BLOCK SIZE? Memory Cache Disk Device A Page When a “page” is written out of memory it is compressed. Only the resulting used “blocks” within the page are written to disk, this set of blocks is called a “chunk” A Chunk (A variable length object)

  11. Writing to Disk Default Page in memory 64Kb – 16 Blocks 10 Blocks used before compression Compress before Disk Write Operation Compressor 4 blocks used after compression Disk Write 1 Chunk on Disk the size of 4 blocks - 16kbytes

  12. Disk_Striping_Packed • The problem with large systems is that we only have 1 free list • This means that if we want 8 blocks of space we will grab the first > 7 block space we can find, which tends to fragment the devices • If Disk_Striping_Packed is ON then we have one free list for each “number of blocks” available • 1 for 1 block free • 1 for 2 blocks free • Etc. • The trade-off is better space usage against slightly worse localisation.

  13. File Placement • Use relative links – makes it easier to relocate files • Use symbolic links for IQ MAIN and IQ TEMP • /IQM_devs/IQ_MAIN_00 • /IQM_devs/IQ_TEMP_00 • Place the transaction log and transaction log mirror on significantly large file system • Recommend 5 GB of filesystem space per IQ node for database related storage • Transaction logs, IQ MSG, scripts, catalog file, etc

  14. Files Locations • Filename rules • Catalog: DATABASE_NAME.db • Transaction log: DATABASE_NAME.log • IQ MSG: DATABASE_NAME.iqmsg • IQ Main Store: DATABASE_NAME_iqmain_000 • 000 would be an incrementing number • IQ Temp Store: DATABASE_NAME_iqtmp_000 • 000 would be an incrementing number • IQ Configuration File: params.cfg (default)

  15. Top Level Directory • Use for writer and readers (multi-node configuration) to communicate • IQ 12.4.3 and earlier • Must be shared across all hosts • Generally done via NFS, but some customers use cluster based filesystems (Veritas Cluster Manager) • IQ 12.5 • No longer a shared filesystem! • Should be local filesystem on each host that is protected accordingly • The name and path can be unique for each node • No need for NFS or cluster based filesystems

  16. BLOCKS • So as can be seen the IO operation is performed in blocks • but how many are written at one time depends upon • The usage (how much of the page is used) • The Compressibility (how well the used blocks compress) • We have found the default works the best • That is for a 64K page 16 blocks of 4k

  17. Block Size • The compressor is designed to run in 2Kbyte chunks so • Never make the block size less than 2Kbyte • Always make is a multiple of 2Kbyte • The compressor does not have a huge amount of memory to run in so the max. for Block Size is 32K

  18. Start Server • There are a huge number of switches that can be set to start up an IQ server • In this section I will concentrate on those that affect performance • Memory Allocation • Cache Sizes • Thread Processing • Priority • Multiplex

  19. Memory • We will talk later about memory inside IQ but… • During Server Startup you can influence • The Cache Sizes (Main and Temp) • The ASA Cache (Max, Min and “Startup”) • Overall Memory

  20. Cache Memory • -iqmc – set the Main Cache • Option – Main_Cache_Memory_MB • -iqtc – sets the Temp Cache • Option – Temp_Cache_Memory_MB • The limits or rules on these we discuss further later on

  21. ASA Memory - 1 • We can set the ASA cache sizes • This is the memory that the .db (database) file is allocated • This should be larger than the .db size • We do not want to page the .db file • The Catalog Store is read for every query (sometimes more than once)

  22. ASA Memory - 2 • There are 4 start-up parameters that control the size of the memory to be used for this cache • -c suggested size of cache • -cl lower bound (min. allocated memory) NT • -ch Hard upper bound (max. memory to be allocated) NT • -ca 0 enforces static cache NT • It is suggested that –c is set to at least twice the size of the .db Catalog Store File

  23. Other Memory • Stack Size – for complex queries increase –iqtss (default is 200KB) – remember there is one stack per thread… • -gm num – # of “connections” to the server – should be set to the number of expected user • -gp size – Max size (in memory) of the catalog store page, required if ASA page of greater than 1024 is specified in CREATE DATABASE • -iqsmem size – (TRU64, HP and AIX) shared memory pool • -iqwmem size – (Tru64, HP and Solaris) wired memory

  24. Threads • -gn num - # of “execution” threads – one thread per user (except java) • -iqgovern num – maximum number of “concurrent” queries to execute on the Server • Not really terribly useful, but can prevent memory thrashing and thread exhaustion • -iqmt num – max. # of threads created at IQ startup time • Default – 60*CPU+2*(connections+1) • For faster CPUs increase this – slower decrease

  25. NT “nice” • In UNIX you can affect the priority of a task using the nice command • Under NT the same is true using the Task Manager • Or on NT you can start the server with the –gb level command • Idle not recommended • Normal • High • Maximum not recommended

  26. Multiplex Start-up • -iqmpx_r Start as READER node • -iqmpx_w Start as WRITER node • -iqmpx_sr Start as STRICT READER node (N/A for 12.5) • -iqmpx_ov 1 Start as WRITER in OVERRIDE mode

  27. TCP/IP Ports • When configuring a TCP/IP port for IQ to listen on, use something other than the default of 2638 • ASA and IQ use port 2638 as a broadcast listener • Multiple servers on the same host share the default port for broadcast listening, but not for client network traffic • Broadcast listening takes precedence over client network traffic as it is the first port started • Easier and safer to assume that 2638 is in use already by ASA/IQ and to use another port for client network traffic • Many things must be considered if an IQ server is to use port 2638 for client network traffic on a host with more than one IQ server (server start order, multiplex synchronization, etc)

  28. Network Connectivity • Different internal environments and settings for ODBC, JDBC, and Open Client connections • JDBC and ODBC are recommended • JConnect is Sybase’s implementation of JDBC • Use the JDBC 2 driver (JConnect 5.2/5.5) • com.sybase.jdbc2.jdbc.SybDriver • Open Client • Use with caution • Most applications written using Open Client expect an ASE server

  29. Open Client vs. ODBC - 1 • See Chapter 32 of the ASA Users Manual as well as the ASA Reference Section “Transact-SQL and SQL/92 compatibility options” for complete list of differences • If writing stored procedures or embedded application code, make sure to explicitly make settings for compatibility as these options will get set to different values for Open Client vs. ODBC connections • ALLOW_NULLS_BY_DEFAULT • QUOTED_IDENTIFIER • STRING_RTRUNCATION • ANSI_BLANKS • ANSINULL • CHAINDED • FLOAT_AS_DOUBLE

  30. Open Client vs. ODBC - 2 • ODBC and JDBC connectivity support all IQ datatypes • Open Client does not support all IQ datatypes • Unsigned integer • Depending on version of Open Client, bigint and unsigned bigint should be OK (OC 12.5 and later)

  31. Client Access • AutoPreCommit Within ODBC • Set registry setting AutoPreCommit to Y • Forces applications to issue a COMMIT before each query • Go to the registry and update the corresponding Sybase Data Source Name (DSN) created, by adding a new value 'AutoPreCommit’ with a value of ‘Y' • HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBC.INI/{DSN} • Packet Sizes • Larger packet sizes will help with large data retrieval • Use –p option in IQ configuration file to increase size • Use CommBufferSize parameter in ODBC connection string

  32. Network Connectivity • Network Speed • Data retrieval will be as fast as the network • 100 MB of data will take 80 seconds on a 10 Mbit LAN • 100 MB of data will take 8 seconds on a 100 Mbit LAN • 100 MB of data will take 0.8 seconds on a Gigabit LAN • LAN speed may be the performance bottleneck for queries that return large amounts of data • The faster the network cards and LAN the better off concurrency will be: more available bandwidth per user • Network Interface Cards • Adding multiple network cards to the IQ node will help with network

  33. Client Access and Connectivity • ASE Component Integration Services (CIS) • Can have ASE CIS reference IQ tables (proxy tables) • Use ASE 12.5 CIS class of ASIQ • Prior to ASE 12.5 ASAnywhere or ASEnterprise classes had to be used • Not a viable option if joining non-IQ tables with an IQ proxy table • Can map multiple ASE logins to a single IQ login • Data modifications should not be performed on proxy tables • Can map ASE proxy tables to stored procedure calls in IQ

  34. Client Access • IQ Component Integration Services (CIS) • Can only be used on the Solaris and WinNT versions of IQ (12.4.3) • All platforms support CIS with IQ 12.5 • Can map to tables in Oracle (ODBC), ASE (ODBC & JDBC), ASA (ODBC & JDBC), DB2 (ODBC), MS SQL Server (ODBC), and any ODBC source • Not a viable option if joining IQ tables with non-IQ proxy tables • Data modifications may be performed on proxy tables

  35. Create and Start - End

More Related