To compare Sequential and Index Scan on the table

select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables;

Advertisements
Posted in PostgreSQL DBA Stuff | Leave a comment

To Print Postgresql Control Data

To Print Postgresql Control Data

# /usr/pgsql-9.1/bin/pg_controldata /var/lib/pgsql/9.1/data
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5696805748991827949
Database cluster state: in production
pg_control last modified: Wed 15 Aug 2012 01:40:35 PM PDT
Latest checkpoint location: 77/B2CEB158
Prior checkpoint location: 77/B2C74F50
Latest checkpoint's REDO location: 77/B2CEB158
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/168940
Latest checkpoint's NextOID: 19537920
Latest checkpoint's NextMultiXactId: 1239
Latest checkpoint's NextMultiOffset: 17276
Latest checkpoint's oldestXID: 1669
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Time of latest checkpoint: Wed 15 Aug 2012 01:40:18 PM PDT
Minimum recovery ending location: 0/0
Backup start location: 0/0
Current wal_level setting: minimal
Current max_connections setting: 1024
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value

Posted in Uncategorized | Leave a comment

Mysql: How to tell if a table is MyISAM or InnoDB

If you use SHOW CREATE TABLE, you have to parse the engine out of the query.

Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).

The correct query to use is SHOW TABLE STATUS – you can get information on all the tables in a database:

SHOW TABLE STATUS FROM `database`;

Or for a specific table:

SHOW TABLE STATUS FROM `database` LIKE 'tablename';

One of the columns you will get back is Engine.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db name' AND ENGINE != 'InnoDB';
Posted in Uncategorized | Leave a comment

To Add Postgresql service to auto restart on server start

To add service to boot
/sbin/chkconfig –add postgresql-9.1

To list service in auto restart
/sbin/chkconfig –list postgresql-9.1
postgresql-9.1 0:off 1:off 2:off 3:off 4:off 5:off 6:off

To turn on the service to automatically boot on restart
# /sbin/chkconfig postgresql-9.1 on

—-If needed
To turn off the service when restart
# /sbin/chkconfig postgresql-9.1 off

Posted in Uncategorized | Leave a comment

Configure a box for Kernel and Postgres configuration

shmall set to 90% of memory / 4Kb page size

shmax is set to max memory on the system

Shared Memory Section:

SHMALL is the maximum allocation of shared memory pages on a system.

SHMALL

7351426.8 divided by 4KB  = 1837856.7 (This is set in the sysctl conf file)

 

SHMMAX is the maximum size of a shared memory segment on a Linux system

SHMAX:

Minimum required on 64 bit systems is = 1073741824 (1 GB)

Minimum required on x86 systems is 268435456 (256 MB)

 

SHMMNI remains same as 4096

 

Semaphore section:

SEMMSL, SEMMNS, SEMOPM and SEMMNI.

SEMMNS = SEMMSL x SEMMNI

 

SEMMNI = Number of arrays = ( twice Maximum number of connections) * multiplied by the number of logical partitions on the database server + number of local application connections on the database server

 

SEMOPM = 32

SEMMSL = 250

SEMMNI = ((2 * max connections) * logical partitions on the server + number of local applications (max app server connections) )

SEMMNS = 250 * SEMMNI

 

Message Section:

No changes with the message section

 

To set the values in sysctl.conf file

 

edit /etc/sysctl.conf

 

kernel.sem=SEMMSL SEMMNS SEMOPM SEMMNI

kernel.shmmax=1073741824

kernel.shmall= 1837856

kernel.msgmax=65535

kernel.msgmnb=65535

 

Run sysctl with -p parameter to load in sysctl settings from the default file /etc/sysctl.conf

sysctl -p

Postgresql configuration:

shared_buffers = 2.5GB

work_mem = 32MB

maintenance_work_mem = 256MB

 

# – Checkpoints – Let the checkpoint be default

 

#checkpoint_segments = 3                # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 5min              # range 30s-1h

#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 – 1.0

#checkpoint_warning = 30s               # 0 disables

example:

RAM = 8GB (1048576 * 8 )
shmax = 8GB
shmall = (90% of 8 *1024*1024) / 4 = (90% of 8388608) / 4 = 7549747.2 / 4 = 1887436.8

SHMMNI = 4096
SEMOPM = 32
SEMMSL = 250
SEMMNI = 6000

SEMMSL SEMMNS SEMOPM SEMMNI
250 1500000 32 6000

SHMAX = 8589934592 (in bytes)
SHMALL = 1887436 (in kilo bytes)

kernel.msgmax=65535
kernel.msgmnb=65535

Posted in Uncategorized | Leave a comment

how to clear memory (RAM)

Clear memory

#  sync; echo 3 > /proc/sys/vm/drop_caches

# free -t -m

 

Posted in Uncategorized | Leave a comment

how to retrieve date from a different timezone

SELECT id, name, creation_date AT TIME ZONE ‘PST’ FROM account ORDER BY creation_date DESC LIMIT 25;

Posted in Uncategorized | Leave a comment