Archive for January, 2014

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


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 :

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

Configuring sqsh – Interactive database shell- replacement for isql

January 7th, 2014 No comments

Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for the venerable ‘isql’ program supplied by Sybase.

Sqsh is much more than a nice prompt, it is intended to provide much of the functionality provided by a good shell, such as variables, aliasing, redirection, pipes,back-grounding, job control, history, command substitu-tion, and dynamic configuration. Also, as a by-product of the design, it is remarkably easy to extend and add functionality.

Sqsh was developped by Scott C. Gray, and is currently maintained by Michael Peppler ( and also by Martin Wesdorp (

You can download sqsh from Source Forge.

Sqsh is held under the GNU General Public License (GPL) and therefore may be freely distributed under the terms of this license.

Basic instructions for Setup :

1. Download the sqsh from here.

2. Extract  the sqsh tar :
3. After extract , you will have following directories :
4.  Move to sqsh directory and create the installation dir with permission to sybase user (in my case installation dir /opt/sqsh). After that execute ./configure –prefix=”/opt/sqsh”sqsh-4
5. Run the make in same dir as below sqsh-5
6. Now finally run the make install sqsh-6
7. Install the manual pages for sqsh as
8. Move to /opt/sqsh/bin and try to connect data server using sqsh
[sybase@LinuxServer ~]$ cd /opt/sqsh
[sybase@LinuxServer sqsh]$ cd bin
[sybase@LinuxServer bin]$ pwd
[sybase@LinuxServer bin]$ ls -ltr
total 588
-rwxr-xr-x. 1 sybase sybase 599071 Jan  7 15:51 sqsh
[sybase@LinuxServer bin]$ sqsh -v
[sybase@LinuxServer bin]$ sqsh -U sa -S LINUX_PROD
sqsh-2.4 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2013 Michael Peppler and Martin Wesdorp
This is free software with ABSOLUTELY NO WARRANTY
For more information type ‘\warranty’
[4] LINUX_PROD.master.1> select @@servername,getdate();
 ———————————————————— ——————-
 LINUX_PROD                                                   Jan  7 2014  3:58PM

(1 row affected)
[5] LINUX_PROD.master.1>

You can configure sqsh prompt using /opt/sqsh/etc/sqshrc.  Like in sqshrc file I changed my prompt as ;
#\set prompt='[$histnum] ${DSQUERY}.${database}.${lineno}> ‘
\set prompt=’${DSQUERY}.${database}.${lineno}> ‘

Now see the prompt of sqsh with history , also I suppressed the banner message using -b
[sybase@LinuxServer bin]$ sqsh -b -SLINUX_PROD -Usa
LINUX_PROD.master.1> select @@servername,getdate()
LINUX_PROD.master.2> go
———————————————————— ——————-
LINUX_PROD                                                   Jan  7 2014  4:06PM
(1 row affected)

Link from Rob Tricks with sqsh & Manual Page for sqsh

Happy New Year !!!

January 2nd, 2014 No comments

Happy New Year 2014!!

In 2014, we’ll talk a lot about Sybase ASE on version 15.7.x.

We will also talk about data replication with SAP Sybase Replication Server, SAP Sybase IQ and SAP HANA.

Stay Tuned in new year !!