Tuesday, April 24, 2018

Nginx Reverse Proxy Formulae

It should be simple but it's just not that simple, that's the reason I'm writing down some example for the proxy pass configuration here.

The reference link can be found here.


The keyword is "If the URI is specified along with the address, it replaces the part of the request URI that matches the location parameter.....  If the address is specified without a URI, or it is not possible to determine the part of URI to be replaced, the full request URI is passed (possibly, modified)."

1.If you specify any URI along with the server address it will replace the request URI part on the location
2. If you don't specify any URI, the full request URI on the location is appended to the target URL

Without further ado, here's some examples:
Assume nginx is setup on localhost and will redirect to www.example.com.

Configuration #1
location /some/path {
      proxy_pass http://www.example.com/link;
URL: http://localhost/some/path
Passed to: http://www.example.com/link
URL: http://localhost/some/path/hello
Passed to: http://www.example.com/link/hello
URL: http://localhost/some/path/
Passed to: http://www.example.com/link/

Configuration #2
location /some/path {
      proxy_pass http://www.example.com/;
URL: http://localhost/some/path
Passed to: http://www.example.com/
URL: http://localhost/some/path/hello
Passed to: http://www.example.com/hello
URL: http://localhost/some/path/
Passed to: http://www.example.com/

Configuration #3
location /some/path {
      proxy_pass http://www.example.com;
URL: http://localhost/some/path
Passed to: http://www.example.com/some/path
URL: http://localhost/some/path/hello
Passed to: http://www.example.com/some/path/hello
URL: http://localhost/some/path/
Passed to: http://www.example.com/some/path/

Configuration #4
location /some {
      proxy_pass http://www.example.com/;
URL: http://localhost/some
Passed to: http://www.example.com/
URL: http://localhost/some/path/hello
Passed to: http://www.example.com/path/hello
URL: http://localhost/some/
Passed to: http://www.example.com/
URL: http://localhost/some/hello?name=me
Passed to: http://www.example.com/hello?name=me

Thursday, September 03, 2015

Estimate database usage - Oracle

One question always haunts me a bit, "Could you provide the database machine requirement?".
Here's one method I often use to estimate the size for Oracle database:
select SEGMENT_NAME, SUM(BYTES)/(1024*1024) BYTES from user_extents
where segment_type = 'TABLE'
and segment_name like 'ACT_%'

select SEGMENT_NAME, SUM(BYTES)/(1024*1024) BYTES from user_extents
where segment_type = 'TABLE'
and segment_name like 'ACT_%'

Tomcat JMX Monitoring

Monitoring Tomcat's resources could be done using the JConsole app which already included in the JDK. In order to monitor Tomcat, we also need to prepare the Tomcat first.

To configure Tomcat in *nix environment, you need to add the parameter below when starting tomcat:

To add the configuration above, one of the way is as below:
1. Open your catalina.sh
2. Add below line (example port is 12300)

3. Restart your tomcat
4. If you need to access it from other computers, make sure the firewall configuration  allows the configured port to be accessed from outside
5. In case the remote connection is not working, try to add below line in the configuration

- https://tomcat.apache.org/tomcat-7.0-doc/monitoring.html
- http://docs.oracle.com/javase/6/docs/technotes/guides/management/jconsole.html

Tuesday, August 11, 2015

Resize LVM Partition in Virtual Box

Here's a note on how to resize LVM partition in VirtualBox
1. VBoxManage modifyhd vdi_path --resize new_size_in_mb
2. Resize the disk.
    a. Run the VM
    b. Open terminal/console and execute command below
        + df
           Write down the logical mapping, in my case it is /dev/mapper/vg_iplus-lv_root
        + fdisk -l
           Write down the Device name, in my case /dev/sda2
 + fdisk /dev/sda
     The next steps will delete the partition to resize, recreate the partition and make it primary. Then resize the partition to the new size and write the changes.
 + Still in fdisk, delete the partition
     input: d
 + Still in fdisk, select the device to partition number. In my case it is 2 (/dev/sda2)
     input: 2

  + Still in fdisk, create new partition and select the same partition as in previous steps (e.g. 2)
     Input: n
     Input: 2

  + Set the size for the new partition, set it to occupy all available space (or as preferred)
      Input: enter (twice to set default size)

  + Commit the changes
     Input: w

   + Reboot server
      Command: reboot

   + Resize the physical volume
      Command: pvresize /dev/sda2
     /dev/sda2 refers to the partition changed previously

   + Verify the new size, make sure the new size is applied
       Command: pvscan

   + Extend the logical volume to take all free space
      Command: lvextend -l +100%FREE /dev/mapper/fedora-root

   + Resize the file system
      Command: resize2fs /dev/mapper/vg_iplus-lv_root

   + Verify the new changes
      Command: df

And we're done! :)


Sunday, June 21, 2015

Find blocking query - Oracle

If you have a query that stops and it seems that it will never end, you might have another query blocking it. Here's one help to find out which query blocking it.

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;


Saturday, August 23, 2014

SQLServer tips

This page would be a growing list for things related to SQLServer. Mostly kept for the rainy season :)

1. How to find most expensive queries (for cache query plans only) 
(ref: http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/)

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

2. Queries taking longest elapsed time
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

3. Queries doing most I/O

(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()

4. Find blocking and blocked queries (sessions)
Use sp_who2

Friday, August 15, 2014

Create swap file for your VPS

Just got a VPS for your own use? RAM only 512MB? or maybe you are accustomed to out of memory error and need a way to increase your memory.
Then you got to have a swap file for your VPS, the size of which will depend on your usage.

One thing to remember, swap file is created on your storage. So don't expect it to be as fast as your RAM.

Here are the steps you can follow (works on Ubuntu 12.04):
1. Check for existing swap file, if you already have one then all are good.
    swapon -s

2. Create your file for the swap file
sudo dd if=/dev/zero of=/swapfile bs=1024 count=256k

3. Set it as swap file
sudo mkswap /swapfile

4. Register the swap file
sudo swapon /swapfile

5. Make it permanent on the system
sudo nano /etc/fstab

And add the following line at the end:
/swapfile       none    swap    sw      0       0 

6. Set the swappiness
echo 10 | sudo tee /proc/sys/vm/swappiness
echo vm.swappiness = 10 | sudo tee -a /etc/sysctl.conf

7. Protect the file
sudo chown root:root /swapfile 

sudo chmod 0600 /swapfile

That's all, enjoy your VPS.


Monday, July 07, 2014

Configure reverse ssh tunneling

When you have a need to access your local PC from the internet, you might be interested in this blog.

1. Root access to a public server (or any kind of access as long as you can do things below :).
2. SSH client

Now, let's begin with the server:
1. Edit sshd_config located in /etc/ssh/ and add the line below
GatewayPorts clientspecified

2. Restart sshd
sudo service ssh restart

That's all for the server.

To the local PC we go:
1. Create reverse ssh tunneling using ssh client.
Open a terminal/console to run the ssh client. In windows you might use Putty.
ssh user@server -R server:serverport:localdestination:localport

2. Test the connection by using telnet
telnet server serverport
Expect no connection refused :)

The steps above is very simple but requires some configuration changes in the public server.
There's a more secure way but requires more effort on the client side which means only geek will be able to access it :) I'll discuss it later on a separate blog.

Setting up vnc for ubuntu desktop

If your installation is ubuntu desktop and you need to remote it using vnc, the steps below might help you to do it:
1. Update your package list
sudo apt-get update

2. Install Gnome desktop environment essential component and Gnome session manager
sudo apt-get install gnome-core gnome-session-fallback

3. Install vnc server
sudo apt-get install vnc4server

4. Start the vnc server
Note: find out the console port being assigned to you. There is a way to start up the connection with a pre defined port number (e.g. vncserver :1)

Example: the assigned port is 1 (5901)
New 'server:1 (server)' desktop is server:1

Starting applications specified in /home/user/.vnc/xstartup

Log file is /home/user/.vnc/user:1.log

5. Edit the xstartup file located /home/user/.vnc/ as below.
At first, when you open the vnc connection there will be only a terminal. You will need to start the gnome-session for the vnc connection.

# Uncomment the following two lines for normal desktop:
#exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
#x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#x-window-manager &

gnome-session --session=gnome-classic &

6. Restart the vncserver by killing it first then starting it up again
vncserver -kill :1

To access vnc from the client, you need to install vnc viewer app. 
The sample url for vnc: server_url:5901
the port number is assigned when you start the vnc connection.

Up to now, for ubuntu server I use Xfce desktop environment instead of gnome. It is lightweight and somehow I still can't setup the gnome env under vnc :)

Reference link: