Postgresql Database basic Tune up

Postgresql is a robust database. it can server thousand of request per minute, if tune properly.

Some basic config you can do for tune the database are documented below.

Memory use :

shared_buffers = 2GB
# RAM/4 up to 8 GB

work_mem = 32MB
#Non shared memory use for sort , etc
# 8 MB to 32 Mb: web
# 128MB to 1 GB: reporting
# limit : Ram/(max_connection/2)

effetive_cache_sie = 6GB
# 3/4 of RAM

wal_buffers = 64Mb
# just set it

maintenance_work_mem = 512MB
# RAM/32
# more for reporting , use by analyze and autovacuum

checkpoint_segments = 64
# make WAL bigger
# space / 32 MB

checkpoint_completion_target = 0.9

stats_temp_directory = '/mnt/ramdisk'
#help with latency

random_page_cost = 1.5
# for AWS, SSD , decision for using index / io

effective_io_concurrency = 4
#for AWS, SSD, RAID

Logging Part , can use with pgBadger to read all the logs and view statistics :

log_connections = on
log_disconnections = on
log_temp_files = 1kB
log_lock_waits = on
log_checkpoints = on
log_min_duration_statement = 0

What we can do for optimize postgresql :

- Do less querying
- fix resoiurce-hungry requests
- get adequate hardware
- scale your infrastructure
- tune the config
- do caching on your application

source :
https://www.youtube.com/watch?v=dBeXS5aFLNc&list=PLE7tQUdRKcyaRCK5zIQFW-5XcPZOE-y9t

Read More

Microsoft Exchange 2007 Logging

With Microsoft Exchange 2007 everything should be running smooth after first install and for new administrator with Exchange this is great. But when you need to get the logs of the server transaction, the default settings was not enough.

Let say the log retention, size of the logs, and other things will be need to adjusted according your organization requirements. This will also affect the server sizing and performance. Thats why sometimes click and pray doesn't works well.

Here my journey on Exchange 2007 email tracking.

I want to know if message tracking enabled, using exchange management shell :

Get-TransportServer | fl *messagetracking*

you can see is it enabled, max age, max directory size, max file size, and the file path.

how to enable / disable it ? here the command :

Set-TransportServer SERVERNAME –MessageTrackingLogEnabled $false or $true

How if we want to log using different path/disk ? note that path should local to exchange server

Set-TransportServer SERVERNAME –MessageTrackingLogPath "D:\TrackingLogs"

and make sure the security as administrator have Full control, Systems need full control, Network services needs Read, Write, and Delete Subfolders and Files.

How about the max directory size ?

Set-TransportServer Servername –MessageTrackingLogMaxDirectorySize 500MB

and for max log files :

Set-TransportServer CorpExch –MessageTrackingLogMaxFileSize 5MB

And the maximum Log file Age by default are 30 days in exchange 2007. To modify :

Set-TransportServer SERVERNAME –MessageTrackingLogMaxAge DD.HH:MM:SS

So let say your organization needs 3 month logs to be retreived, set :

Set-TransportServer CorpExch –MessageTrackingLogMaxAge 90.00:00:00

And for subject logging, it is by default enabled in exchange 2007, you can disable it by :

Set-TransportServer CorpExchangeServre –MessageTrackingLogSubjectLoggingEnabled $false

And how if want to see detail transaction log for send and receive of smtp log? you need to enable it manually as it not enabled by default.

To enable outbound SMTP logging use Set-SendConnector "Connector Name" -ProtocolLoggingLevel verbose. By default it is set to none.

To enable inbound SMTP logging use Set-RecieveConnector "Connector Name" -ProtcolLoggingLevel verbose.


So there was my journey. If need logs more than default 30 days, you better enable it.

Read More

Running CGI script on Nginx Web Server on FreeBSD

Yes CGI is an old technology. I encounter to be able run an old CGI script for testing on my development project. And i am using Nginx stack rather than install apache. So we can run CGI script with Nginx web server. Here how i do it and documenting it here.

on FreeBSD 9.0 server, we using fcgiwrap application.

#cd /usr/ports/www/fcgiwrap/
#make install clean

Then enable it to run in /etc/rc.conf  :

#echo "fcgiwrap_enable='YES'" >> /etc/rc.conf

Then start the fcgiwrap. it will be available in /var/run/fcgiwrap/fcgiwrap.sock

To use it, just redirect the script to be run with fcgiwrap.sock.

Here the nginx setup.

server {

      listen  80;
      server_name    login.freehostspotsystem.com;

      location / {
         root /usr/local/www/super/system/free;
         index index.html index.html;
      }
       
     location /cgi-bin/ 
     {
         gzip off;
         root /usr/local/www/super/system/free;
         fastcgi_pass unix:/var/run/fcgiwrap/fcgiwrap.sock;
         
         include /usr/local/etc/nginx/fastcgi_params;
         fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;

     }

And put your cgi file in /usr/local/www/super/system/free/cgi-bin/login.cgi

so when user go to http://login.freehotspotsystem.com/cgi-bin/login.cgi  , user will got the page.

Read More

Make fabric using port other than standard ssh port

For deploying source code to production or UAT server , the fastest way is using fabric which we can automate, as we are developer are lazy :).

Fabric can run all the same command you do to a server or a lot of server automaticaly, no typo and more workflow type of update. Don't do it manualy by typing ssh login and run command. what if you update to 100 production server ? are you still a diligent developer ?

Fabric come for the help and i use it for many of repetitive task which i can automate. But problem is arise when i cannot access the production server directly. This because the security team !!! why they make it even harder ?

So they need you upload your code only from a single master SSH server, called cleanroom, which have connection to all the production server. So the security team will got the logs only from the cleanroom. so smart but not so good news for developer like me.

But you remember, port forwarding in SSH ? this come to the rescue, handy command line tools. Even you can only connect to the "Cleanroom", you can connect as you are go directly to the production machine.

So here are my setup i would like to share with you. sst...don't let the security guy now about your smart idea !!!

So let say the environment like this :

1. local dev machine , your pc : 192.168.1.10
2. Cleanroom machine , ip : 192.168.200.10
3. Production machine , ip : 192.168.111.10

you can only SSH to the cleanroom machine. and you only need SSH to your production machine to deploy your code.

step 1 :
Make a connection to your cleanroom and port forward to your dev machine.

smartdev@bsdstack$: ssh -L 8080:imsmart@192.168.111.10:22 securenow@192.168.200.10

So this will create a local port at 8080 listening and will forward it to production machine port 22 via cleanroom machine.

step 2 :

smartdev@bsdstack$: fab -H 127.0.0.1:8080 update_code

second step will run the fabric command, go to localhost port 8080 which will be connect to your production machine.

you can try also using ssh to your production machine

smartdev@bsdstack$ ssh 127.0.0.1 -p 8080

this will open ssh connection to your production machine.

Now i can do my fabric command center again, i am rocks !!!

Well, hope this help anyone looking for a solution.

Read More

Django Choice form filtered base on logged username

With Django we have form which have many feature to faster development. I usually use Django Model Form, which get the meta data from a model and spit out a form ready to use and rendered to client in HTML form.

Now i need to get a choice field in django , but the choice field get from a foreign key, and need to filter it base on the username logged in to system.

So the purpose is to get a list of choice which only available for the specific user, let say the user belong to VIP user group. So user only see the choice only available to VIP user group defined before by admin.


So lets get the how to do it. Here are the model code :

  class Usergroup(models.Model):
    """Models for User Group"""
    group_name = models.CharField(max_length=64, null = False, default = '')
    owner = models.CharField(max_length=64, null = False, default = ''  )
    update_by = models.CharField(max_length=250,null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
            db_table = 'Usergroup'
            ordering = ['-created_at']
            verbose_name_plural = 'usergroup'
            permissions = (
                ("view_usergroup", "Can see detail of the User Group"),
                ("list_usergroup", "Can list all the User Group"),
            )

    def __unicode__(self):
        return self.group_name

    @models.permalink
    def get_absolute_url(self):
        return ('user_group', (),{'group_name':self.group_name})

here are the form code :

   class AdminVIPUsersModelForm(forms.ModelForm):
     """For new and edit model"""
     def __init__(self, *args, **kwargs):
        super(AdminVIPUsersModelForm, self).__init__(*args, **kwargs)
        for name, field in self.fields.items():
            if field.widget.__class__ == forms.widgets.TextInput:
                if field.widget.attrs.has_key('class'):
                    field.widget.attrs['class'] += ' form-control input-medium'
                else:
                    field.widget.attrs.update({'class':'form-control input-medium'})

     class Meta:
         model = VIPuser
         fields = ('user_name','passwd','user_group','access_type','owner')
     user_name = forms.CharField(widget=forms.TextInput(attrs={ \
                    'class':'form-control input-medium'}),label='User Name' \
                    ,help_text="Used for login on Wifi")
     passwd = forms.CharField(widget=forms.PasswordInput(attrs={ \
                    'class':'form-control input-medium'}),label='Password' \
                    ,help_text="Passoword for user login from HotSpot")
     user_group = forms.ModelChoiceField(widget=forms.Select(attrs={ \
                    'class':'form-control input-medium'}),label='Group' \
                    ,help_text="Group the user belong", \
                    queryset = Groupchoices.objects.all())
     access_type = forms.CharField(widget=forms.TextInput(attrs={ \
                    'class':'form-control input-medium'}),label='Access Type' \
                    ,help_text="User access type description")
     owner = forms.CharField(widget=forms.TextInput(attrs={ \
                    'class':'form-control input-small'}),label='Owner' \
                    ,help_text="The creator of the group and will used only \
                    by the creator")

And in the views.py :

        form = AdminVIPUsersModelForm()
        form.fields["user_group"].queryset = Usergroup.objects.filter(owner=request.user.username)

This usecase will be used when there is foreign key relation on each table.

Hope this help someone as i documented for my future reference in this blog.

Read More

Check windows process with Nagios Monitoring

I need to check a process running on windows machine from a monitoring server. The monitoring using Nagios. I want to get alert when the process is not running on the windows machine.

This can be achieved by using Nagios NRPE client++ .

Assume NRPE client++ already installed.
To test on nagios machine do this :

./check_nt -H 10.10.10.10 -p 12489 -v PROCSTATE -d SHOWALL -l w3wp.exe

If running, it will return :

w3wp.exe: Running

if not running, it will return :

w3wp: not running

this so simple is it?

Read More

Monitoring DNS with a vbscript

DNS on windows usually come with Active directory service. But also can used to query external Domain name as long as the server have internet connection.

Hostname entries in DNS also rarely changed and some use local cache with TTL from server. Somehow, we need to monitor a change in DNS query result with alert when these changes happend.

Let say it is other Domain name which not in our control, we want to know immidiately when the Address changed in DNS. And also alert with email that the query result changed with the query info inside the email.

Within windows 7 as client, we can do this using a VBScript and a task scheduler, and access to SMTP mail server to sent out the alert.

Below are the script to be used. The script will query the domain to be monitor, and check if the result as expected. then it will sent email with the query result, and change subject if host name is changed for the monitored domain.

strEmailTo="anyong@superbadmin.com"
strEmailFrom="dnsmonitor@superbadmin.com"
strDomain="superdomain.com"
strDNSServer="192.168.1.111"
strSMTPServer="192.168.1.232"
iSMTPPort=25
strLogs = "c:\scripts\dnsmon\logs\"
Const ForAppending = 8 'for loggin
Dim strLogFile, objFSO, strDate, objLogFile

arrRecords = Array( _
 "Name:    " & strDomain ,_
 "Address:  " &  "172.10.120.121" _
)

Set objShell = CreateObject("WScript.Shell")
'Set objNSLookup = objShell.Exec("nslookup -q=MX " & strDomain & " " & strDNSServer)
Set objNSLookup = objShell.Exec("nslookup " & strDomain & " " & strDNSServer)
Set objStdOut = objNSLookup.StdOut
strOutput = objStdOut.ReadAll

strMsg = "NSLookup Output: " & VbCrLf & VbCrLf & strOutput & VbCrLf & "Test Results:" & vbCrLf

bChanged = False
For Each strRecord In arrRecords
 bRecordChanged = False
 If InStr(strOutput, strRecord) = 0 Then
  bRecordChanged = True
  bChanged = True
 End If
 strMsg = strMsg & VbCrLf & "Changed: " & bRecordChanged & " - " & strRecord
Next

strMsg = strMsg & VbCrLf & VbCrLf & "Overall result of change: " & bChanged & VbCrLf & "############################"

'Logging Part
strLogFile = strLogs & "superdomain.com-check-logs-" & Year(strDate) & "-" & Month(strDate) & "-" & Day(strDate) & ".log" 'for log file
set objFSO = CreateObject("Scripting.FileSystemObject")
set objLogFile = objFSO.OpenTextFile(strLogFile, ForAppending, True)
objLogfile.WriteLine Now & " ## " &strMsg 
objLogFile.Close
set objLogFile = Nothing
set objFSO = Nothing
'//////////
'end logging

'WScript.Echo strMsg

'Context.LogMessage strMsg
'bChanged = True
'If bChanged = True then
If True then
 Set objEmail = CreateObject("CDO.Message")
 objEmail.From = strEmailFrom
 objEmail.To = strEmailTo
 if bChanged = True then
  objEmail.Subject = "[Mon] superweb.com DNS Changed"
 else
  objEmail.Subject = "[Mon] superweb.com DNS Change"
 End If
 objEmail.Textbody = "Checked : " & Now & VbCrLf & strOutput
 objEmail.Configuration.Fields.Item _
     ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 objEmail.Configuration.Fields.Item _
     ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPServer
 objEmail.Configuration.Fields.Item _
     ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = iSMTPPort
 objEmail.Configuration.Fields.Update
 objEmail.Send
 'Context.SetResult = 1
Else
 'Context.SetResult = 0
End If

This simple vbscript can be executed on windows and need have access to smtp server to sent out emails.

Read More