Tech Touch Online

Lets see how a customer table is created in different database

In Oracle:

         create table customer
         (
         cust_id number,
         cust_name varchar2(200),
         cust_gender char(1),
         registration_date date,
         );

In MySQL:
         create table customer
         (
         cust_id int,
         cust_name varchar(200),
         cust_gender char(1),
         registration_date datetime,
         );

In MSSQL:

         create table customer
         (
         cust_id int,
         cust_name varchar(200),
         cust_gender char(1),
         registration_date datetime,
         );

In PostgreSQL:

         create table customer
         (
         cust_id int8, [or cust_id integer]
         cust_name varchar(200),
         cust_gender char(1),
         registration_date date,
         );

Most of our work will be related to postgresql.conf file during our tour of performance tuning.
So first of all you have to backup the postgresql.conf file. There are hundreds of parameters in
postgresql.conf file. Most of the website regarding performance tuning focus on the following.

Summary:
1. shared_buffers [related to buffer cache]
           i.Should be set to 25% of the system memory on Unix and Linux system
          ii.Larger settings for shared_buffers usually require a corresponding
              increase in checkpoint_segment
         iii.We may need to configure the linux kernel
2. work_mem [related to sort]
         i. Should be set  between 4MB to 64MB
        ii. For 100 concurrent connections, it can be 10MB         
3. maintenance_work_mem [related to regular maintenance index, vacuuming]
        i. should be set to 5% of the system memory
       ii. but not more then 500MB.
4. effective_cache_size [related to query planner/optimizer]
        i. should be set 50%-75% of physical memory
5. wal_buffers [related to transaction commit]
        i. should be set to 1MB to 16MB
       ii. but larger values can result in extra fsync calls or to request more System V shared memory
6. Tuning Checkpoint Parameters [if needed]
        i. checkpoint_segments [related to bulk data load]
       ii. can be set to 30
        -> checkpoint_completion_target: Once you've increased this parameter,
            it also makes sense to
            increase checkpoint_completion_target to 0.9
        -> checkpoint_timeout: 5 min to 15 min
7. synchronous_commit [related to commit]
             i.  set to off for best performance
8. random_page_cost and seq_page_cost[related to control the planner's estimate]                    
              i. if your database is significantly larger than physical memory,
                 you might want to try setting these parameters to 2 and 1
             ii. Never set random_page_cost less than seq_page_cost,
                 but consider setting them equal (or very close to equal)

Othes :
     1. Use a current version
     2. ANALYZE database so that postgres keeps statistics about database to execute queries correctly
     3. Run VACUUM on database tables frequently
     4. EXPLAIN ANALYZE your slow statements

To Configure the linux kernel

    1. http://www.postgresql.org/docs/8.4/static/kernel-resources.html

    2. http://spinroot.com/spin/multicore/setup.html

    3. http://www.depesz.com/2012/07/12/waiting-for-9-3-dramatically-reduce-system-v-shared-memory-consumption/

Remarks: sample query to test :

      select count(emailaddress) from (
      select length(emailaddress),emailaddress from companies
      where emailaddress in (select emailaddress from emailcustomers)
      and   heading in (select heading from adminheadings)
      group by emailaddress
      having length(emailaddress)>5
      )a;

      In a PC with 2GB RAM this query takes 33603.661ms without changing the parameters.
      But after changing the parameters that takes only 5751.443 ms.
      So the performance is increased by five times.
   
   
source :  http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html
      http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance
      http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
      http://www.revsys.com/writings/postgresql-performance.html
      http://linuxfinances.info/info/quickstart.html
      http://www.varlena.com/GeneralBits/Tidbits/perf.html

start of  tested the test tutorial of the site
A. Install and configure git in your PC

1. Install gitk and git-core from Synaptic Package Manager.
2. Create a free account in https://github.com.
          Set your username cention-[Your First Name]-[Your Last Name]. For example my username is cention-madhuri-debnath.
          Set your emailaddress as your cention webmail account email address
          Set your password as cention webmail account password
3. Run the following command:
         $ git config --global user.email "your email address"
         $ git config --global user.name "your user name"
4. Generate SSH key for github by following the instructions from the link
         http://help.github.com/linux-set-up-git/
      $ cd ~/.ssh
      $ ls
      $ ssh-keygen -t rsa -C "abul_bashar@cention.se"
      $ only Enter
      $ only Enter
      $ ls -l
      $ ctrl+h -> open in editor 'id_rsa.pub' -> copy all -> paste to
  'https://github.com/settings/ssh' SSH keys + title=cention mail
      $ ssh -T git@github.com

B. GIT commands

1. to initialized empty Git repository in /.git/ 
   $ git init

2. To see git status
   $ git statu­s

3. to add a new file
   $ git add octoc­at.txt
   3.1. to add file of similar type
    $ git add '*.tx­t'

4. to commit the change
   $ git commi­t -m "Add cute octoc­at story­"

5. to download or synchronize pc with the server copy
   $ git pull
   5.1  to download the repository
    $git pull git@github.com:cention-assad-zaman/gittestingrepo.git

6. to up the file in the server
   $ git push
   $ git push --all
   $ git push orgin master

7. Push an existing repository from the command line
   $ git remote add origin git@github.com:cention-assad-zaman/gittestingrepo.git
   $ git push -u origin master

8. steps to add new branch in the server
   8.1     create branch as of origin
    $ git checkout -tb feature-add-email-tracking origin/master
   8.2     to add the new files
    $ git add modules/cention_workflow/outbound.osm
   8.2  to commit the new files
        $ git commit -m "feature outbound.osm link tracking for email campaign"
   8.3  to update the server with the change
        $ git push origin feature-add-outboundosm-link-tracking-email-campaign

9. to see git the branches
   $ git branch
   9.1 to see all git branches
       $ git branch -a :: to see all branches

10. to remove a file from being commited or to change the branch
    $ git checkout filename or brachname

11. to clone a repository
    $git clone -o origin git@github.com:cention-assad-zaman/gittestingrepo.git   
       
       

1. To navigate to the root directory  $ cd /

2. To navigate to the home directory  $ cd or $ cd ~

3. To see the file or folder listing  $ ls 

4. To delete files $ rm file_name  
   To delete directory $ rm ­R
5. To make directory $ mkdir new_dir

6. To display file system disk space $ df -­h

7. To display amount of free and used space in RAM $ free ­m

8. System network interface $ ifconfig

9. To add group $ addgroup newgroup 

10. Add user $ adduser new_user

11. To assign password $ passwd

12. Add user assad to new group $ adduser assad newgroup

13. To get Information $ info 

14. To escape editor Press 'q' / Press Esc, :+wq = write quie write quie / :q = quit Exit, if no change

15. Change directory or file mode $ chmod xxx file_name
    X=4: for read only.
    X=2: for write only.
    X=1: for execute only.
16. To change group attribute of a file or directory $ chgrp group_name file_name 

17. Install software in ubuntusudo $ apt­get install software_name
   
18. Clear the terminal $ clear

19. To display the name of your current directory $ pwd

20. To view process currently running $ ps aux

21. Commant to start the service $ start 

22. stop Commant to stop the service

23. To lock user account $ sudo passwd ­l user_name
    To unlock user account $ sudo passwd ­u user_name

24. To enable root directory $ sodo passwd root

25. To create a file named filename1 $ touch filename1

26. Display date $ date 
    Display date in s $ date +"%H:%M:%S"
    Display calender $ cal

27. Display current user $ whoami 

28. Display system information $ uname

29. Logged on information $ who 

30. For how long the system has been running $ uptime 

41. To display text $ echo 'text display'
42. To see the path $ whereis bash
                    $locate
43. To display standard path $ which wesnoth

44. Used to concentrate more than one files $ cat /etc/mtab

45. Text editor mode $less /etc/mtab 

46. Super user $ sudo su ­

47. Find out processes $ ps ­elf | less

48. Show system configuration $ lshw

49. Find out open ports $ netstat

40. Stop mysql $ /etc/init.d/mysql stop or service mysql stop

51. Start mysql $ /etc/init.d/mysql start or service mysql start

52. To update ubuntu $ apt­get update
    To upgrade $ apt­get upgrade

53. To rename file $ cp filename1 filename2

54. To rename folders $ mv filename1 filename2

55. Networking stop / internet stop $ sudo /etc/init.d/networking stop 

56.  Networking stop / internet start $ sudo /etc/init.d/networking start

57. To copy file or folders $ cp source destination

58. Folder copy $ cp -r /data/cmail /data/assad  [cmail folder will be copied to assad folder ]

59. Folder/file move $ mv source destination

60. Secure copy [from remote pc] $ scp source destination 
    if the test folder does not exists then the file is saved as 'test' in root(/)
    scp cention@192.168.0.105:/home/cention/Documents/myth.odt /home/subarna/Desktop/assad/
    scp cention@192.168.0.105:/home/cention/Documents/myth.doc_0.odt .
    (if the destination is the default location so, . (dot) is enough as destination)

61. Remove file $ rm filename 

62 To make a shortcut $ ln -­s source destination
   e.g.$ ln -­s /home/cention/Documents/kommuninfo/portal /var/www/