http://goo.gl/EmKxy0

Archive

Posts Tagged ‘Interview Ques’

Huge Page Support in Linux – To Increase Database Performance.

January 19th, 2014 No comments

Memory Management internally uses TLB cache to map the Virtual address to physical address.
If the TLB cache is small (TLB Miss) (since page size is small), it will need to refer the Page table. Page Table look ups are costly as compare to TLB cache.
That’s reason the applications ( Like Database) which have heavy memory demand can be configured to Huge TLB Pages so that Page Table access can be reduced  and overall application performance can be increased.
Linux has had support for huge pages since around 2003 where it was mainly used for large shared memory segments in database servers.
ASE Database performance can be increased bt 2-7% by using huge page on Linux Platform. You can check Huge Page Support on Linux :

cat /proc/meminfo | grep Huge
HugePages_Total: XXX
HugePages_Free:  XXX
HugePages_Rsvd:   XXX
Hugepagesize:     2048 kB

Source: http://linuxgazette.net/155/krishnakumar.html

From a memory management perspective, the entire physical memory is divided into “frames” and the virtual memory is divided into “pages”. The memory management unit performs a translation of virtual memory address to physical memory address. The information regarding which virtual memory page maps to which physical frame is kept in a data structure called the “Page Table”. Page table lookups are costly. In order to avoid performance hits due to this lookup, a fast lookup cache called Translation Lookaside Buffer(TLB) is maintained by most architectures. This lookup cache contains the virtual memory address to physical memory address mapping. So any virtual memory address which requires translation to the physical memory address is first compared with the translation lookaside buffer for a valid mapping. When a valid address translation is not present in the TLB, it is called a “TLB miss”. If a TLB miss occurs, the memory management unit will have to refer to the page tables to get the translation. This brings additional performance costs, hence it is important that we try to reduce the TLB misses.

On normal configurations of x86 based machines, the page size is 4K, but the hardware offers support for pages which are larger in size. For example, on x86 32-bit machines (Pentiums and later) there is support for 2Mb and 4Mb pages. Other architectures such as IA64 support multiple page sizes. In the past Linux did not support large pages, but with the advent of HugeTLB feature in the Linux kernel, applications can now benefit from large pages. By using large pages, the TLB misses are reduced. This is because when the page size is large, a single TLB entry can span a larger memory area. Applications which have heavy memory demands such as database applications, HPC applications, etc. can potentially benefit from this.

Source : https://lwn.net/Articles/374424/

Memory Mgmt uses Translation Look Buffer(TLB) Cache to map Virtual to physical address, The amount of memory that can be translated by this cache is referred to as the “TLB reach” and depends on the size of the page and the number of TLB entries.

If the TLB miss time is a large percentage of overall program execution, then the time should be invested to reduce the miss rate and achieve better performance.
Using more than one page size(Huge Page) was identified in the 1990s as one means of reducing the time spent servicing TLB misses by increasing TLB reach.
Broadly speaking, database workloads will gain about 2-7% performance using huge pages whereas scientific workloads can range between 1% and 45%.
Huge pages are not a universal gain, so transparent support for huge pages is limited in mainstream operating systems
it is possible that huge pages will be slower if the workload reference pattern is very sparse and making a small number of references per-huge-page.
Many modern operating systems, including Linux, support huge pages in a more explicit fashion, although this does not necessarily mandate application change. Linux has had support for huge pages since around 2003 where it was mainly used for large shared memory segments in database servers such as Oracle and DB2

Isolation Levels by example

December 28th, 2013 No comments

For Earlier post on Isolation Level please refer here.

Understanding Isolation Level “1” : Avoids Dirty Reads (Default isolation level for ASE)

Transaction T1 (Session 1)modifies a data item. Another transaction T2 (Session 2)then reads that data item before T1 performs a COMMIT or ROLLBACK. If T1 then performs a ROLLBACK, T2 has read a data item that was never committed and so never really existed.

 Session1  Session 2   Remarks
1> select @@isolation “Isolation Level”
2>go
 Isolation Level
 —————
               1
(1 row affected)
1> insert into pmtmaster values(1,100)
2> go
1> begin tran
2> update pmtmaster set id2=200 where id1=1
3> go
1>
   

In Session1, updating the row with id2=1
 
 1> print “Session 2″
2> go               
Session 2           
1> select @@isolation “Isolation Level”
2> go
 Isolation Level
 —————
               1
(1 row affected)
1> select * from pmtmaster where id1=1
2> go
^C^C
[CanCan]
 In Isolation Level 1, that is default mode, we can not read dirty data as it is still not committed by other tran.
     So Isolation Level 1, avoids dirty reads.

Understanding Isolation Level “0”

Session1 Session 2 Remarks
1> select @@isolation “Isolation Level”
2>go
Isolation Level
—————
1
(1 row affected)
1> insert into pmtmaster values(1,100)
2> go
1> begin tran
2> update pmtmaster set id2=200 where id1=1
3> go
1>
   Same as Above , In Session1, updating the row with id2=1
 
1> print “Session 2″2> goSession 2
1> set transaction isolation level 0
2> go
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
0
(1 row affected)
1> begin tran
2> select * from pmtmaster where id1=1
2> go
id1         id2
———– ———–
1         200
(1 row affected)
 
 Now with isolation level 0 , I am trying to read data and it is allowing dirty reads.
 rollback    If Session 1  rollbacks, session will have inconsistent.

Understanding Isolation Level “2” : Avoid Repeatable Reads

What is Repeatable Reads?

Transaction T1 (session 1) reads a data item. Another transaction T2 (session 2) then modifies or
deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified                                      value or discovers  that the data item has been deleted.

Session1 Session 2 Remarks
 

1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
1
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 Transaction T1 (session 1) reads a data item.
 Session1 Continues..  

 1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
1> commit
2> go
 Another transaction T2 (session 2) then modifies.
 1> select * from pmtmaster where id2=200
2> go
 id1         id2
 ———– ———–
           1         200
(1 row affected)
 
 If T1 then attempts to reread the data item, it receives a modified value/different result set in same transaction. This is issue in repeatable reads. Lets review how can we avoid it.

How to avoid Repeatable Reads?

 

1> set transaction isolation level 2
2> go
1> select @@isolation “Isolation Level”
2> go
 Isolation Level
 —————
               2
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
 id1         id2
 ———– ———–
           1         200
           2         200
(2 rows affected)
To avoid Repeatable read problem, enable the isolation level 2
 Session1 Continues…
 1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
^C^C
[CanCan]
 Now Transaction T2 will not allow to modify the restult set which was read earlier tran 1(Session 1)
 1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 Still T1 will get same number of rows.

but still it has problem of Phantom Read?

Transaction T1 reads a set of data items satisfying some . Transaction T2 then creates data items that satisfy T1’s and commits. If T1 then repeats its read with the same ,  it gets a set of data items different from the first read.

1> begin tran
2> insert into pmtmaster values (3,200)
3> go
1> commit
2> go  
You can not modifies the result set but still you can insert new values and affect the result set.
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
2(1 row affected)
1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
3         200
(3 rows affected) 
 Now you can see the same session 1 is still returning different number of result set. This is called as Phantom Reads.
   
 To Avoid Phantom Reads enable isolation level 3 as below.

Understanding Isolation Level “3″ : Avoid Phantom Reads

 1> set transaction isolation level 3
2> go
1> select @@isolation “Isolation Level”
2> go
Isolation Level
—————
3
(1 row affected)
1> begin tran
2> select * from pmtmaster where id2=200
3> go
id1         id2
———– ———–
1         200
2         200(2 rows affected)
To Avoid Phantom Reads , Lets enable isolation level 3
 
1> begin tran
2> update pmtmaster set id2=300 where id1=2
3> go
^C^C
[CanCan] 
 Now you can not modify the result set.
  1> begin tran
2> insert into pmtmaster values (3,200)
3> go
^C^C
[CanCan]
 You can not create data items to affect the result set
 1> select * from pmtmaster where id2=200
2> go
id1         id2
———– ———–
1         200
2         200
(2 rows affected)
 
 All the time you will get same result set.

Need of In-memory Technology : SAP HANA

May 6th, 2013 No comments

Challenge 1: Massive Data Growth

Massive amounts of data is being created every year and as per he IDC EMC report data growth would be 40K Exabytes by 2020 :

http://germany.emc.com/collateral/about/news/idc-emc-digital-universe-2011-infographic.pdf

http://www.emc.com/collateral/analyst-reports/idc-the-digital-universe-in-2020.pdf

Capture 2

Challenge 2: Fast access to business decision making information.

Business & People want fast exact and correct answer of all questions from this massive amount of data.

Challenge 3: Current Technologies Can not deliver with this massive data growth.

Historical DBMS :

Historically database systems were designed to perform well on computer systems with limited RAM, this had the effect that slow disk I/O was the main bottleneck in data throughput. Consequently the architecture of these systems was designed with a focus on optimizing disk access, e. g. by minimizing the number of disk blocks (or pages) to be read into main memory when processing a query.

New Hardware Architecture ( up to or more 128 Cores of CPU and 2TB of RAM)

Computer architecture has changed in recent years. Now multi-core CPUs (multiple CPUs on one chip or in one package) are standard, with fast communication between processor cores enabling parallel processing. Main memory is no-longer a limited resource, modern servers can have 1 TB of system memory and this allows complete databases to be held in RAM. Currently server processors have up to 80 cores, and 128 cores will soon be available. With the increasing number of cores, CPUs are able to process increased data per time interval. This shifts the performance bottleneck from disk I/O to the data transfer between CPU cache and main memory

Hana1

Need of In-memory Technology SAP HANA :

From the discussion above it is clear that traditional databases might not use current hardware most efficiently and not able to fulfill current and future business need.

The SAP HANA database is a relational database that has been optimized to leverage state of the art hardware. It provides all of the SQL features of a standard relational database along with a feature rich set of analytical capabilities.

Using groundbreaking in-memory hardware and software, HANA can manage data at massive scale, analyze it at amazing speed, and give the business not only instant access to real time transactional information and analysis but also more flexibility. Flexibility to analyze new types of data in different ways, without creating custom data warehouses and data marts. Even the flexibility to build new applications which were not possible before.

HANA Database Features

Important database features of HANA include OLTP & OLAP capabilities, Extreme Performance, In-Memory , Massively Parallel Processing, Hybrid Database, Column Store, Row Store, Complex Event Processing, Calculation Engine, Compression, Virtual Views, Partitioning and No aggregates. HANA In-Memory Architecture includes the In-Memory Computing Engine and In-Memory Computing Studio for modeling and administration. All the properties need a detailed explanation followed by the SAP HANA Architecture.

Source : www,sap.com and emc and idc reports.

 

Migrating SAP Sybase ASE from AIX to Linux

May 1st, 2013 No comments

Always consider to migrate the Development environment first , then UAT. Before moving to production Perform Regression testing on UAT enviornment.

Please consider to create the script to perform update stats,xp_postload(drop and re create index) for each and every database.

Steps for an ASE Database( You can repeat same steps for other databases) :

Step 1: Run the consistency checks in ASE database in Source (AIX) environment, to make sure that everything is fine.

Step 2: Put the database in single user mode.

Step3: Make sure there is no user activity on the Source Database .

Step 4: Run the sp_flushstats in the database.

Step 5: Take the backup of the database in Source (AIX) environment.

Step 6: Ftp the Files to Target environment. (AIX to Linux)

Step 7: Create and build the dataserver and databases in target Linux environment with exactly same configuration.
You might require to change some of the config param in Linux environment for performance point of view. ( Lets not discuss it here, as it is out of context).

Step 8: Also migrate the Login, roles from source server to target server

Step 9: Load the database in Linux environment.
(If there were user activity during dump process, load will be fail.)

Step 10: Online the database. If the target ASE version is new with source, It will also perform upgrade in this step.

Step 11:  Fix the corrupt indexes using the xp_postload. If the Database size is more than 20G, try drop and re-create index , in this case xp_postload would not be effective.

Step 12: Update the stats on all tables.

Step 13:  If there is replication setup in your environment, please setup replication after that.

Issue Faced:

1. If there is any user online during backup process, your load will fail( in the step for cross platform conversion).

2. After online database, we seen the -ve values in sp_helpdb output for few databases. There are two ways to fix this :

i) Try to run dbcc

dbcc usedextents(<DB name or DB ID>, 0, 1, 1)

ii) Use the Traceflag  7408 and 7409 in Run Server file and reboot the instance. It will not take much time as compare first option.

Traceflag 7408 : Force the server to scan *log segment* allocation pages; to recalculate free log space rather than use saved counts at boot time.
Traceflag 7409 : Force the server to scan *data* segment allocation pages; to recalculate free data page space rather than use saved counts at boot time.

Please let me know if you are planning for migration and need any assistance.

SAP Sybase ASE Q&A Bank

April 29th, 2013 1 comment

Wait is over Now !! 

Please download the Complete ebook for SAP Sybase ASE Q&A Bank Version 1.0  as below:

Pic

 

 

 

 

 

 

 

 

 

 

 

 

 

Replication Server Interview Questions

December 20th, 2012 No comments

Guys,
Came across below Replication Server Questions, might help you for yours interview preparation. It also includes few ASE questions.
Also Updated the Interview Question Page.
Thanks,

1. What is the difference between MSA and Warm standby ?

2. How can we perform fail over in Case of Warm Stand by and MSA?

3. What will happen, if the queue has data in case of fail over to warm standby?

4. For standby point of view which one is better warm standby or MSA?

5. How can we sync the warm standby database ? If there is some data in queue, what will you do?

6. What is the config parameter you can use to tune a rep agent?

7. Suppose there are 20,000 trans which are getting fail and you need to skip all these trans using error class. Please let me know high level overview to setup
error class?

8. How the multiple replication server can improve the performance as compare to single replication server ?

9. Suppose the query is running slow , how will you investigate?

10. Suppose tempdb log segment is full, how will you troubleshoot?

11. Suppose for a very big database , backup takes 4 hrs to complete . During the backup, due to heavy activity from user log is full, after 2 hours ? What will you do now?

12. How can we guess, in how much time queue will be drain?

13. Suppose queue is getting full, what can be point of bottleneck and why?

14. Suppose log for a database is getting full, how will you trouble shoot? Why log increase is not a better idea? ( Server version is below 15.7)

15. How will you check the latency and what is the latency ?

16. Suppose you want to sync a table in a warm stand replication , How will you do?

17. What are the various factor which can effect rep agent performance?

18. How can you make sure rep agent is running fine ( means scanning is going well in Primary site)?

19. How can we see the number of commands in a transaction in queue?

20. Suppose you have 5000 locks at primary side , and your one delete statement ( which is effecting 7000 rows)went fine. Now in case of Replicate dataserver ( has same number of locks), you are getting out of lock situation? Why it was successful at primary side , what it did not go well in replicate side, despite of primary and replicate have same number of locks?

21. What are the situations when we require to drain the queues?

22. In unix , suppose one dba modifies some of files including dataserver binary to non sybase user, and you want to fix this situation asap? What you will do?

23. Suppose there is thread down issue in replication for target side, and you fetched that its down due to duplicate row? How can we make sure , is it due to duplicate row or duplicate key?

SAP D&T Academy Video- How to Log Trans in the Replication Exception Table?

December 20th, 2012 No comments

SAP D&T Academy Video: How to install sybsyntax db in ASE server

November 22nd, 2012 2 comments

Please follow the following video for installing the sybsyntax db in ASE server.

 

 

SAP D&T Academy Video:How to create login in Sybase ASE

November 22nd, 2012 No comments

Please follow the following video for creating login in Sybase ASE

 

SAP D&T Academy Video :How to set database options in sybase ASE

November 22nd, 2012 No comments

Please follow the following video for setting sybase database options.