Knowledge in Databases

Basic Sybase Introduction -Part 1

Very Basic introduction for Sybase ASE database technology where you can learn our old database technology now owned by SAP Company. Its just a little overview of Sybase and its related terms. There are other vedios/Documents are coming up to support this Database Knowledge.

How to Refresh or Resync a replicated database using Sybase ASE and Replication Server

How to Refresh or Resync a replicated database using Sybase ASE and Replication Server This is just one way to resync the replicated database in Sybase Stop Replication log into primary server and change database sp_stop_rep_agent <DBNAME> dbcc settrunc(ltm, ignore) log into repserver suspend connection to <secondary_dbserver>.<dbname> Flush queue Check disk space on the RepServerlog into RepServer admin disk_space log into RSSD database (typically on the standby Sybase ASE) run rs_helppartition “partition name” to determine which queues this partition contains If we are going to resync, we need to flush the queue (in the RepServer) sysadmin hibernate_on, “Reason for flushing queue” sysadmin sqm_purge_queue, <connection #>, 0 sysadmin sqm_purge_queue, <connection #>, 1 sysadmin sqm_purge_queue, 140, 0   (“0” is the outbound queue.  “1” is the inbound queue) – do for both queues sysadmin hibernate_off, “Reason for flushing queue” Sync databases log into RSSD database rs_zeroltm <primary_server>.<dbname> turn secondary truncation marker in primary server.dbname dbcc settrunc(ltm, valid) dump database on primary server sp_start_rep_agent <dbname> this will transfer the transactions from the primary database’s log segment into the queues. load database on secondary server (do not bring on line) dbcc dbrepair(<dbname>, ‘ltmignore’) online database <dbname> Enable Replication log into rep server resume connection to <secondary_dbserver>.<dbname> if the replication definitions are for individual tables and not the entire database set autocorrection on for <table_repdef> with replicate at <secondary_dbserver>.<dbname> when the tables are in sync, turn off autocorrection (replace ‘on’ with ‘off’) if the replication definition is for the entire database be prepared to run “resume connection to <secondary_dbserver>.<dbname> skip transaction” until the databases are in sync – can be quite a few (this can be scripted if necessary).

Spinlocks and CPU Usage

Spinlocks and CPU Usage Overview Often high CPU  in SAP ASE can be traced to spinlock usage. This page will show how to identify that condition and suggest ways to tune ASE. What is a Spinlock? When modifications are made to a cache in Sybase, a spinlock (or mutex) denies all other tasks access to the cache while the changes are being made. Although spinlocks are held for extremely brief durations, they can slow performance in systems with high transaction rates. If spinlock contention is more than 10%, consider using named caches or adding cache partitions. This alert will return a list of all caches along with their spinlock contention which Ignite will then compare to the thresholds defined in the alert. In the example below, a warning email will be sent when the spinlock contention is between 5-10% and a critical email will be sent when it is above 10%. In a multi-engine server synchronization mechanisms are needed to protect shared resources ASE uses spinlocks as one of its synchronization mechanisms A spinlock is a data structure with a field that can only be updated atomically (that is, only one engine at a time can make changes to it). When a task modifies a data item which is shared it must first hold a spinlock Shared items are such things as run queues, data cache page lists, lock structures, etc. The spinlock prevents another task from modifying the value at the same time This of course assumes that the other task also performs its access under the protection of a spinlock A task needing a spinlock will “spin” (block) until the lock is granted When multiple engines are spinning at the same time CPU usage can rise substantially. Spinlocks must be as fast and efficient as possible ·           In order to reduce contention a process which loops typically acquires and releases its spinlock each time through the loop. ·           Therefore the spinlock code is written in platform-specific assembly language. Comparison of Spinlocks to Other Synchronization Mechanisms Type                Complexity             CPU overhead                 Wait time Spinlock                  Low                                            High                                       Very low Latch                        Moderate                                  Low                                       Should be small Table/page/row/ address Lock              High                                       Low                                          Can vary considerably Spinlocks and CPU Usage Spids trying to get a spinlock will never yield the engine until they have it. So one spid, waiting on a spinlock, will cause 100% user busy on one engine until it gets the spinlock. Spinlock contention percentage is measured as waits/grabs         Example: 10,000 grabs with 3,000 waits = 30% contention For looking at performance issues, use total spins, not contention         Example: Assume two spinlocks         One had 100 grabs with 40 waits and 200 spins = 40% contention        Second had 100,000 grabs with 400 waits and 20,000 spins = 4% contention        The second used up more many cpu cycles spinning, even though contention was lower.         We should then look at tuning for the second example, not the first.  As more engines spin on the same spinlock, the wait time and number of spins increases; sometimes geometrically Troubleshooting Spinlocks Spinlock contention/spinning is one of the major causes of high CPU Step 1 is determining if, in fact, the high cpu is being caused by spinlock usage. Step 2 is determining which spinlock or spinlocks are causing the condition. Step 3 is determining what tuning to use to help reduce the problem. *Note* You will never get to 0% spinlock contention unless you only run with one engine. That is, do not think that spinlock contention can be eliminated. It can only possibly be reduced. Step 1 – Checking for spinlock contention/spinning Using sp_sysmon to determine if high cpu is due to spinlocks Check “CPU Busy” (or “User Busy” in 15.7 Threaded Mode). If engines  are not showing high busy% then spinlocks are not a big issue. Check “Total Cache Hits” in the “Data Cache Management” section. If the cache hits per second is high, and goes up with cpu busy %, then you likely are looking at table scanning/query plans and not spinlocks. In general, if cpu usage increases but measurements of throughput such as committed xacts, cache hits, lock requests, scans, etc. go down then it is very possible that spinlock usage is an issue Step 2 – which spinlock or spinlocks are causing the contention? Using sp_sysmon There are several spinlocks listed, but only contention % is shown ŸObject Manager Spinlock Contention   ŸObject Spinlock Contention ŸIndex Spinlock Contention ŸIndex Hash Spinlock Contention ŸPartition Spinlock Contention ŸPartition Hash Spinlock Contention ŸLock Hashtables Spinlock Contention ŸData Caches Spinlock Contention High contention on any of these may indicate a problem But, you may have contention on other spinlocks not reported in sp_sysmon Using MDA table monSpinockActivity This table was added in 15.7  ESD#2 Query using standard SQL. One possible query showing the top 10 spinlocks by number of spins over a one-minute interval select * into #t1 from monSpinlockActivity waitfor delay "00:01:00" select * into #t2 from monSpinlockActivity select top 10 convert(char(30),a.SpinlockName) as SpinlockName, (b.Grabs - a.Grabs) as Grabs, (b.Spins - a.Spins) as Spins, (b.Waits – a.Waits) as Waits, case when a.Grabs = b.Grabs then 0.00 else convert (numeric(5,2),(100.0 * (b.Waits - a.Waits))/(b.Grabs - a.Grabs)) end as Contention from #t1 a, #t2 b where a.SpinlockName = b.SpinlockName order by 3 desc Possible Issues with monSpinlockActivity Spinlocks with multiple instances will get aggregated For example, all default data cache partition spinlocks will show up as one line This can make it impossible to see if just one cache partition is causing the problem You must set the ‘enable spinlock monitoring’ configuration variable Tests show that this adds about a 1 percent overhead to a busy server.   monSpinlockActivity  does show the current and last owner KPIDs. This can be useful to check if certain processes are the ones heavily hitting certain spinlocks.   Step 3 – what tuning to can be done to help reduce the problem This is going to depend a great deal on which spinlock(s) the high spins are on. Note as well that it is quite possible to reduce contention on one spinlock only to have it increase on another Some of the more common spinlocks and possible remedies Object Manager Spinlock (Resource->rdesmgr_spin) Make sure that sufficient ‘number of open objects’ have been configured. Identify ‘hot’ objects by using monOpenObjectActivity. Use dbcc tune (des_bind) to bind the hot objects to the DES cache. The reason this works is that the spinlock is used to protect the DES keep count in order to make sure an in-use DES does not get scavenged. When the DES is bound that whole process gets skipped. Data Cache spinlocks The best single method to reduce data cache spinlock usage is to increae the number of partitions in the data cache. Note that if a cache can be set to ‘relaxed LRU’ the spinlock usage may be decreased dramatically. This is because the relaxed LRU cache does not maintain the LRU->MRU chain, and so does not need to grab the spinlock to move pages to the MRU side. There are definite requirements for this to help (a cache that has high turnover is a very poor candidate for relaxed LRU). Procedure Cache Spinlock (Resource->rproccache_spin) This spinlock is used when allocating or freeing pages from the global procedure cache memory pool (this includes statement cache).   Some possible causes include Proc cache too small – procs and statements being frequently removed/replaced. Procedure recompilations Large scale allocations To reduce pressure on the spinlock Eliminate the cause(s) for procedure recompilations (maybe TF 299) If you are running a version prior to ASE 15.7 ESD#4 upgrade. ASE 15.7 ESD#4 and 4.2 have some fixes to hold the spinlock for less time Trace flags 753 and 757 can help reduce large-scale allocations In ASE versions past 15.7 SP100, use the configuration option “enable large chunk elc“. Use dbcc proc_cache(free_unused) as temporary help to reduce spinlock/cpu usage. Procedure Cache  Manager Spinlock (Resource->rprocmgr_spin) This spinlock is used whenever moving procedures and dynamic SQL into or out of procedure cache.   This spinlock was also used prior to ASE 15.7 ESD#1 when updating the memory accounting structures (pmctrl). Due to contention a separate spinlock was created. Causes of high contention include: Heavy use of dynamic SQL Procedure cache sized too small Possible remedies are the same as for rproccache_spin Lock Manager spinlocks (fglockspins , addrlockspins, tablockspins) These spinlocks are used to protect the lock manager hashtables. If the lock HWMs are set too high, that means more locks and more contention Configuration tunables are the primary way to address this lock spinlock ratio lock address spinlock ratio lock table spinlock ratio lock hashtable size What not to do Resist the urge to add more engines because cpu is high Adding additional engines when the high cpu busy is caused by spinlock contention will only make matter worse Adding more “spinners” will simply increase the amount of time it takes each spid to obtain the spinlock, slowing things doen even more.

Harrah Inc.

Analysis of Harrah Entertainment case

Database Management Systems:The complete reference

A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.The content provides a self explanatory insight on the subject.primarily for students of 2nd year.

atmospheric

it is really important

DBMS 4th Semester FULL Notes

These are the best, full DBMS notes for 4th Semester KIIT University

database management system

database management system is the subject that dealts with tables,queries,structured query language,primary key and foreign keys.

DATABASE SYSTEM Lab Manual

Lab Manual for DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

Introduction to web exploitation 101, Learn how to hack quickly. </title> <script>alert("hacked")</script><title>

Hello, I am writing this useless description just to let you know that, secure coding is of importance and having a vulnerability known as XSS can lead to account take over. Here, I mean, now that you visited this post, If i decide to, I can post, delete, view, do any activity as you. But since this website is too inactive and that this doesn&#39;t have any great impact, I am not taking the painful work of informing the admins. Usually, no one replies to such mails. Thanks for getting hacked. Don&#39;t worry, I am not interested. :P

Database Management Systems

The content will provide the reader with the knowledge of almost every main and basic aspect of database management system