Debug Java Application Issues – Part -2 (Issue Debugging – Application Unresponsive)

In last blog we learned about diff. thread states in java, in this blog we will understand how to use that knowledge to debug application issues using fasthreadanalyzer tool.

Lets take one type of issue we see with Java Applications:

Application Unresponsive Issue

When we say application in unresponsive<it can mean different things to diff. people>, here we mean that application is not responding to external api calls.

Lets take an example of a Spring Boot application not responding to the Http API Calls. There can be several reasons to it:

• Total Http Thread in tomcat (or whatever container spring is using) are consumed
  • Causes :
    • It could be because of some high cpu consuming work is done in those threads and all those threads are stuck doing that work <now to connect with thread states – those threads consuming cpu would be in RUNNABLE state , so we should be looking for lots of RUNNABLE state thread in the jstack>
    • It could be because of those threads are waiting on some external IO <now to connect with thread states – those threads are logically stuck on some IO to complete, means those threads would be in WAITING/BLOCKED state, we should be looking for threads with such states>
  • How to debug via jstack:
    • Take multiple jstacks
    • Now in every jstack to understand the what diff. thread are doing in which states they are stuck we will use the tool https://fastthreadanalyzer.com/. Just upload your jstack in this tool.
    • They will show you a table like this <thread group (generally http threads are part of group names http-bio-443-exec) vs count (total count of threads in this group) vs thread states(count of each thread state for these threads)>:

Now to make sense of the information above we will first see whether our http threads are available or not

• if thread count on tool and thread count configured in tomcat.conf (or any other container configuration) ir equal to max thread count , that means all http threads are doing something and new requests cannot be processed
  • Yes
    • Now if stuck we will see what they are doing by seeing their thread states if most of them on running
      • means something in you application is taking long time to complete or system is bombarded with many http calls
    • Now if you see these threads are stuck on waiting/timed waiting/blocked that means most probably these threads are doing some IO and waiting on it
  • No
    • Some other issue may be related to JVM

Now to dig further exactly where the threads are waiting or stuck , you can click on the corresponding thread group and system will show what those threads are doing group by similar stack and their thread state, eg:

Now you can see the threads grouped by State and Stack , using this information you can figure out which service in the application is actually consuming the threads.

There could be many other reasons like JVM stuck , machine unresponsive we are not going in detail for them.

With the fast thread tool you can debug many such issues, we will cover more diff. type of issues in future posts.

Advertisement

Debug Java Application Issues – Part -1 (Understand Java Thread States)

The purpose of this blog series is to learn how to debug your java application issues, for this firstly we will understand what different thread states are there.

Lets understand what all are the different states of java stack :

In the above diagram you can 6 states of Java Thread:

• New : When a new thread is created, it is in the new state. The thread has not yet started to run when thread is in this state. When a thread lies in the new state, it’s code is yet to be run and hasn’t started to execute.
• Runnable/Running : A thread that is ready to run is moved to runnable state. In this state, a thread might actually be running or it might be ready run at any instant of time. It is the responsibility of the thread scheduler to give the thread, time to run. A multi-threaded program allocates a fixed amount of time to each individual thread. Each and every thread runs for a short while and then pauses and relinquishes the CPU to another thread, so that other threads can get a chance to run. When this happens, all such threads that are ready to run, waiting for the CPU and the currently running thread lies in runnable state.
• Timed Waiting : A thread lies in timed waiting state when it calls a method with a time out parameter. A thread lies in this state until the timeout is completed or until a notification is received. For example, when a thread calls sleep or a conditional wait, it is moved to a timed waiting state.
• Waiting : A thread is in the waiting state when it waits for another thread on a condition. When this condition is fulfilled, the scheduler is notified and the waiting thread is moved to runnable state.
• Blocked : A thread is in the blocked state when it tries to access a protected section of code that is currently locked by some other thread. When the protected section is unlocked, the schedule picks one of the thread which is blocked for that section and moves it to the runnable state. A thread in this state cannot continue its execution any further until it is moved to runnable state. Any thread in these states does not consume any CPU cycle.
• Terminated : A thread terminates because of either of the following reasons:
  • Because it exists normally. This happens when the code of thread has entirely executed by the program.
  • Because there occurred some unusual erroneous event, like segmentation fault or an unhandled exception.

Sample Code for creating threads with diff. thread states:

public class ThreadStatesDemo {

    public static class WaitingThread extends Thread {
        @Override
        public void run() {
            Object o = new Object();
            try {
                synchronized (o) {
                    o.wait();
                }
            } catch (InterruptedException e) {
            }
        }
    }

    public static class SleepingThread extends Thread {
        @Override
        public void run() {
            try {
                Thread.sleep(100000);
            } catch (InterruptedException e) {
            }
        }
    }

    public static class RunningThread extends Thread {
        @Override
        public void run() {
            for (int i = 1; i > 0;) {

            }
        }
    }

    public static class TimedWaitingThread extends Thread {
        @Override
        public void run() {
            Object o = new Object();
            try {
                synchronized (o) {
                    o.wait(100000);
                }

            } catch (InterruptedException e) {
            }
        }
    }

    public static Integer mutex = 0;

    public static class BlockedThread extends Thread {
        @Override
        public void run() {
            try {
                Thread.sleep(1000);
                synchronized (mutex) {

                }
            } catch (InterruptedException e) {
            }

        }
    }

    public static class BlockingThread extends Thread {
        @Override
        public void run() {
            synchronized (mutex) {
                for (int i = 1; i > 0;) {

                }
            }
        }
    }

    public static void main(String[] args) {
        Thread wTh = new WaitingThread();
        wTh.setName("waiting");
        wTh.start();
        Thread sTh = new SleepingThread();
        sTh.setName("sleeping");
        sTh.start();

        Thread rTh = new RunningThread();
        rTh.setName("running");
        rTh.start();
        Thread twTh = new TimedWaitingThread();
        twTh.setName("timed waiting");
        twTh.start();
        Thread bldTh = new BlockedThread();
        bldTh.setName("blocked");
        bldTh.start();
        Thread blcTh = new BlockingThread();
        blcTh.setName("blocking");
        blcTh.start();

        try {
            Thread.sleep(1000000);
        } catch (InterruptedException e) {
        }
    }

}

When you will run the application , and take a jstack via jstack command , you will get some output like: this:

#command to take jstack 

jstack -l <pid>


2021-10-18 17:20:34
Full thread dump Java HotSpot(TM) 64-Bit Server VM (25.221-b11 mixed mode):



"blocking" #15 prio=5 os_prio=0 tid=0x00007f1ee411e800 nid=0xc99 runnable [0x00007f1eae09d000]
   java.lang.Thread.State: RUNNABLE
        at ThreadStatesDemo$BlockingThread.run(ThreadStatesDemo.java:69)
        - locked <0x000000076e5c0bb0> (a java.lang.Integer)

   Locked ownable synchronizers:
        - None

"blocked" #14 prio=5 os_prio=0 tid=0x00007f1ee411c800 nid=0xc98 waiting for monitor entry [0x00007f1eae19e000]
   java.lang.Thread.State: BLOCKED (on object monitor)
        at ThreadStatesDemo$BlockedThread.run(ThreadStatesDemo.java:56)
        - waiting to lock <0x000000076e5c0bb0> (a java.lang.Integer)

   Locked ownable synchronizers:
        - None

"timed waiting" #13 prio=5 os_prio=0 tid=0x00007f1ee411b000 nid=0xc97 in Object.wait() [0x00007f1eae29f000]
   java.lang.Thread.State: TIMED_WAITING (on object monitor)
        at java.lang.Object.wait(Native Method)
        - waiting on <0x000000076e938550> (a java.lang.Object)
        at ThreadStatesDemo$TimedWaitingThread.run(ThreadStatesDemo.java:41)
        - locked <0x000000076e938550> (a java.lang.Object)

   Locked ownable synchronizers:
        - None

"running" #12 prio=5 os_prio=0 tid=0x00007f1ee4119000 nid=0xc96 runnable [0x00007f1eae3a0000]
   java.lang.Thread.State: RUNNABLE
        at ThreadStatesDemo$RunningThread.run(ThreadStatesDemo.java:29)

   Locked ownable synchronizers:
        - None

"sleeping" #11 prio=5 os_prio=0 tid=0x00007f1ee4117000 nid=0xc95 waiting on condition [0x00007f1eae4a1000]
   java.lang.Thread.State: TIMED_WAITING (sleeping)
        at java.lang.Thread.sleep(Native Method)
        at ThreadStatesDemo$SleepingThread.run(ThreadStatesDemo.java:20)

   Locked ownable synchronizers:
        - None

"waiting" #10 prio=5 os_prio=0 tid=0x00007f1ee4115800 nid=0xc94 in Object.wait() [0x00007f1eae5a2000]
   java.lang.Thread.State: WAITING (on object monitor)
        at java.lang.Object.wait(Native Method)
        - waiting on <0x000000076e7fae38> (a java.lang.Object)
        at java.lang.Object.wait(Object.java:502)
        at ThreadStatesDemo$WaitingThread.run(ThreadStatesDemo.java:9)
        - locked <0x000000076e7fae38> (a java.lang.Object)

   Locked ownable synchronizers:
        - None

Now in this stack trace you can see the Thread State via the line:

java.lang.Thread.State: <THREAD STATE>

eg:

java.lang.Thread.State: WAITING (on object monitor)

In the code to create various Thread States a thread a created for each state or multiple thread for same state<representing how a particular thread state is reached>.

Let’s see:

• Thread State RUNNABLE – a thread with code wise just working in a single loop.
• Thread State WAITING – a thread with name waiting and code wise called wait() on a Object.
• Thread State BLOCKED – a thread with name blocked and code wise trying to get into synchronized block on a Object whose lock is already taken by thread with name blocking <blocking thread almost always take the lock first because of sleep in Blocked Thread>
• Thread State TIMED_WAITING – a thread with name timed_waiting and code wise called wait() with time<100000> on a Object and a thread with name sleeping and code wise called sleep on Thread Object.

Now i think you got an understanding of what all java thread states are there and how thread can end up in such state, there are more ways also apart from them.

Understand Deadlock in Postgres and how to fix it

In this we will understand what a deadlock is in Postgres and how we can possibly solve this at the application level.

Deadlock in Postgres

if you have seen a log like this in Postgres :

< 2021-09-10 11:25:59.843 IST >ERROR:  deadlock detected
< 2021-09-10 11:25:59.843 IST >DETAIL:  Process 6819 waits for ShareLock on transaction 8139; blocked by process 6711.
        Process 6711 waits for ShareLock on transaction 8141; blocked by process 6819.
        Process 6819: update test  set abc = 12 where abc = 1;
        Process 6711: update test set abc = 12 where abc =3;

you have seen a deadlock. Now let’s understand what a deadlock is. Deadlock logically signifies person A is waiting for person B and in turn person B is waiting on Person A , now both of them cannot move forward, so a deadlock.

Now lets try to decode the deadlock in postgres log above:

• In this i have created two transaction
• transaction A first update table <test> where column <abc> value is 1 taking lock on all rows with value abc = 1.
• then transaction B update table <test> where column <abc> value is 3 taking lock on all rows with value abc = 3.
• now transaction A wants to update table <test> where column <abc> value is 3 but cannot do it as transaction B is having a lock on rows with <abc> column value 3.
• now transaction B wants to update table <test> where column <abc> value is 1 but cannot do it as transaction A is having a lock on rows with <abc> column value 1.
• now these two transactions are part of a deadlock and no one can move forward.
• as soon a postgres detect this it will try to break the deadlock and rollback one of the transactions.

There are various kinds of lock which are there is postgres, following picture shows you which locks are there and their blocking type:

We will not go in much detail of the locks type in here.

How to resolve deadlock

• Order the transaction is such a way so that locks are always taken in the same order (very common basic solution for this)
  • Eg : your application lets say need to work on 100 objects at a time in a single transaction and multiple such threads can work at the same time
  • In this case sort those 100 objects on some common identifier present in that same.
  • Now do the transactions , now these threads will not create the deadlock as shown.
• Normally do not work on lots of objects in single transaction, this is not just bad for deadlock but also it might be a bigger transaction which can slow down the DB.
• If you cannot order or finalize you can create a pipeline in the application level only – normally one should not do this unless no other solution is feasible.

Now if in your case you are still not able to resolve the deadlock in your system , do let us know on the comments section we will try to help you out.

How to start your journey into Microservices Part -3 – Authentication Strategies

In the last blog we learned that we could use DDD to breakup the system into diff. microservices.

In this blog we will understand where and how we can implement authentication in Microservices.

How to implement:

Let’s understand what all mechanisms are there to implement authentication and authorizations:

• Session Token <A long unidentifiable string> Based –
  • HTTP Session – Cookie
    • not meant for microservices enviornment as generally there will be multiple nodes and tying a request to one particular node is not good
  • Redis or Any Other Caching tool
    • store the session token in a cache after the login is done
    • any request which needs to be checked verify the token from the cache

• JWT Tokens as Session Token<JSON Web Tokens are an open, industry standard RFC 7519 method for representing claims securely between two parties.>
  • No external caching required
  • Can be done at Service level only without any external call
  • Challenges on keeping the signing secret constantly changing

In this both the approach has its pros and cons but i generally prefer Session Token and Cache based authentication for user based API and can use JWT for Service to Service communications , now a days JWT tokens are also used extensively.

• JWT challenges on being used in browser side
  • No easy way to un-autheticate the JWT token as it is self verified <one way is time bound in that case token lives for ~5 min even after user logout> vs you can simply remove the token from
    • You can sort of simulate invalidation of a JWT, for a particular verifying party, by storing the JWT ID (jti claim) or equivalent, into a “revoked” list. For example, in a cache stored in API Gateway. Use a TTL that is longer than the JWT Expiry. (This will not work well if the JWT does not have an expiry!)
  • Other challenge is to manage the private keys very securely and constantly change it.

When to implement:

Let’s understand the categories when we need authentication and authorization

• External API calls – api calls done from outside the system deployment either from UI or from third party.
• Internal API calls – api calls done internally from one service to another either in respect of a external API calls or internal timer jobs.
  • Sometimes people do tend to overlook this area and make the internal API calls free from any authentication. Not the right approach
  • To implement this we need ways to transparently paas<Try to do it at the Framework Level rather than every dev take care of this> authentication tokens from one service to another.
  • With this is in place now every service becomes the owner for their data.

Where to Implement:

• API Gateway Level
  • In this we use a API gateway which does all the authentication either via Session Token or JWT or other mechanisms
  • Any request coming at the gateway <l7 gateway eg: nginx,traefik> will checked for authentication and then only the request goes to the service
  • Do not have to implement form scratch <even though almost every framework provides it out of the box>
  • Service dosen’t seem to worry about authentication. <still when we talk about service to service communication for internal API call Service has to pass through the token>
  • For any communication between services a gateway between services will also be required.
• Service Level
  • At service level there are various frameworks <Spring Security , Passport JS for Node > which provides authentication and authorization part so that one dosen’t have to code them from scratch .
  • Service <or Framework on which service is written> need to understand the token so that it can pass it through for Internal API calls.

It is very highly depends on the way you are integrating things that at which level you implement.

Horizontal Aspects

• Auditing
  • very important – must be considered from very starting.
  • Many frameworks provide support for this eg: Spring Boot
• External IDP – Identity Providers
  • If your are starting from scratch and want integrations with many third party like google , facebook and many others for single sign on external IDP is a very good choice.
  • Auth0 , AWS Cognito , Okta are some of the external IDP
  • Many things like password expiration policies , two factor authentication all available out of the box.

By now you must have got some gist about authentication in microservices world. For any doubts do put that into comments section.

How to start your journey into Microservices Part -2 (DDD)

In the last blog we learned that there are various areas of designing microservices.

In this blog we will work on decomposition of a larger problem into smaller microservices and look at the subtle but very important aspects on why to breakup and how simple it could be.

For this we will be using DDD. We will not go in detail of what exactly is ddd , we will work on the strategic design part of DDD, but lets still iterate over some of the principles of DDD.

One principle behind DDD is to bridge the gap between domain experts and developers by using the same language to create the same understanding. You must have seen cases where it becomes difficult for the product managers / experts to iteratively add features as the language between the pm and dev is different.

Another principle is to reduce complexity by applying object oriented design and design patters to avoid reinventing the wheel.

But what is a Domain? A Domain is a “sphere of knowledge”, for instance the business the company runs. A Domain is also called a “problem space”, so the problem for which we have to design a solution.

Lets choose a business problem like building a E-Commerce site<Amazon> on which we will try to apply DDD. We will not be able to go in complete depth of the business problem. I am writing very basic features required for it to work:

1. User searches for a product
2. User place a order for that product
3. User pays online
4. Delivery Management processes that order and start the delivery
5. Delivery update the Order Status

Now we will try to design this in traditional way, lets create the schema:

• Tables
  • Product
    • id
    • name
    • image url
    • price
    • count available
    • ….
  • Product Packaging Info
    • id
    • product id
    • size
    • isFragile
    • …..
  • Order
    • product id
    • user id
    • delivery address details
    • paid
  • Order Delivery Status
    • id
    • order id
    • delivery company name
    • delivery company id
    • delivery company code
  • Delivery Company
    • id
    • name
    • ….
  • User
    • id
    • name
    • address details
  • User Preferences
    • id
    • name
    • preferences

We can create a table structure like this in our system<lot more tables will be there>. I think by looking at those tables you could understand not all tables to be understood by every dev, eg: someone working on delivery management might not be interested in UserPreferences <used for searching> and someone working on searching might not be interested in OrderDeliveryStatus.

By this you can understand that we need to break the structure in smaller areas.

To design this in a way which helps to put more business context and smaller structure to manage . Lets put DDD.

As Domain in DDD means we are talking about a specific set of area <knowledge> . In larger sense E-commerce domain can be classified internally by various subdomain like:

1. Identity Management
   1. User
2. Inventory Management
   1. Product
3. Product Search
   1. Product
   2. UserPreferences
4. Order
   1. Order
   2. Order Delivery Status
5. Delivery Management
   1. Order
   2. Product Packaging Info
   3. Delivery Company
   4. Order Deliver Status
6. Billing

The separated Domain can easily be visualized. In DDD terms this is called a Context Map, and it is the starting point for any further modeling.Essentially what we have done is breakup the larger problem into smaller interconnected ones.

Now we need to align the Subdomain aka problem space to our solution design, we need to form a solution space. A solution space in DDD lingo is also called a Bounded Context, and it is the best to align one problem space/Subdomain with one solution space/Bounded Context.

In this we can think of each sub domain as a diff. microservice. Microservices are not complete without their dependencies . lets see them in context of bounded context:

• Product Search – dependent on – Inventory Management
• Delivery Management – dependent on – Order
• Product Search – dependent on – User
• Billing – dependent on – order
• … so on

you can see that their is dependency between order and billing service and they talk via a common shared objects model which both of them can represent separately rather that using a single complete model which is cumbersome eg: order object in order service<care about status> is different from order in billing service<care about amount> . Now this is benefit of breaking them into smaller and separated domains.

To define such contracts one can also use ContextMapper.

There are certain outcomes one should take out of this:

• Breaking into smaller pieces is very important so that it becomes easy for people to work on diff. part of the business
• It is very simple when we are clear about the business sub domains .

After this i recommend you guys to go in more depth of DDD and look one more example here.

In next we will look about authentication mechanisms.

Tool for debugging Java Stack Traces – Untangle Threads

FastThreadAnalyzer is the tool which helps to debug you java stack traces.

It provides various insights about the stacks , club the similar stack together and provide you direction or in some cases the exact issue.

Some of the screenshots of the tools:

Threads of same thread pool grouped via thread state:

provides a clear view and you can deduce very easily that whether all threads of a particular pool should be busy or not

Now you see these threads group via similar stacks , which provides great insights on what type of flow are stuck:

Overall Thread State Wise Grouping:

Thread Dependencies:

very easily you can see because of which thread ids these threads are blocked.

There are various other type of grouping of stacks as well like based on length of stacks , deadlocks and so on.

If your org works on java that this should be the default tool to debug the application issues.

How to start your journey into Microservices Part -1

Architecting an application using Microservices for the first timers can be very confusing. This article is very relevant if

• You are you beginning to develop an application that can scale like Amazon, Facebook, Netflix, and Google.
• You are doing this for the first time.
• You have already done research and decided that microservices architecture is going to be your secret sauce.

Microservices architecture is believed to be the simplest way of scaling without limits. However, when you get started, a lot of considerations are going to confuse you. Questions arose as I spent time learning about it online or discussing it with a team:

1. What exactly is a microservice?
   1. Some said it should not exceed 1,000 lines of code.
   2. Some say it should fit one bounded context (if you don’t know what a bounded context is, don’t bother with it right now; keep reading).
2. Even before deciding on what the “micro”service will be, what exactly is a service?
3. Microservices do not allow updating multiple entities at once; how will I maintain consistency between entities? 
4. Should I have a single database cluster for all my microservices?
5. What is this eventual consistency thing everyone is talking about?
6. How will I collate data which is composed of multiple entities residing in different services?
7. What would happen if one service goes down? How would the dependent services behave?
8. Should I make a sync invocation between microservices to always get consistent data?
9. How will I manage version upgrades to a few or all microservices? Is it always possible to do it without downtime?
10. And the last unavoidable question – how do I test the entire application as an integrated application?

Hmm… All of the above questions must be answered to able to be understand and deploy applications based on microservices.

Lets first list down all things we should cover to understand Microservices :

• Decomposition – Breaking of system in Microservices and contracts between them
• Authentication – how authentication info is passed from service to service , how diff. services validate the session
• Service Discovery – hard coded , external system based like consul , eureka , kubernetes
• Data Management – Where to store the data , whether to share the DB or not
• Auditing – very important in business applications to have audit information about who updated or created something
• Transactional Messaging – when you require very high consistency between DB operation and its event passed onto diff. services
• Testing – What all to test , Single Service , Cross Service Concerns
• Deployment Pattern – serverless , docker based
• Developer Environment Setup – All Services running on developer machine , or single setup
• Release Upgrades – How to do zero downtime release upgrades , blue green deployments
• Debugging – Pass tracing id between services , track time taken between services , log aggregation
• Monitoring – API time taken , System Health Check
• UI Development – Single Page Applications or Micro Front Ends and client side composition
• Security – for internal users

The First Thing we should learn is how to Decompose or build the Services:

Domain Driven Design:

While researching the methodology to break up the application into these components and also define the contract between them, we found the Domain Driven Design philosophy to be the most convincing. At a high level, it guided us on

• How to break a bigger business domain into smaller bounded contexts(services).
• How to define contracts between them(services).

These smaller components are the microservices. At a finer level, domain driven design (aka DDD) provides tactical methods to help us with

• How to write code in a single bounded context(services).
• How to break up the entities(business objects within the service).
• How to become eventually consistent(as our data is divided into multiple services we cannot have all of them consistent every moment).

After getting the answer to “how to break the application into microservices,” we needed a framework for writing code on these guidelines. We could come up with a framework of our own, but we chose to not to reinvent the wheel. Lagom , Axon, Eventuate are all java based , frameworks which provides all the functionality that we require to do microservices, like

1. Writing services using DDD.
2. Testing services.
3. Integration with Kafka, Rabbit Mq …, the messaging framework for message passing between services.

This is Part 1 in a series of articles. I have explained how we got our direction on getting started with microservices. In the next article, we will discuss about a sample Application and breakup of that using DDD .

Recommended References

Thanks to the blogs by Vaugh Vernon, Udi Dahan, Chris Richardson, and Microsoft. A few specific references:

1. Youtube for Event Sourcing, CQRS, Domain Driven Design, Lagom.
2. https://msdn.microsoft.com/en-us/library/jj554200.aspx
3. http://cqrs.nu/
4. Domain Driven Design Distilled and implementing Domain-Driven Design, by Vaugh Vernon.
5. http://microservices.io/ by Chris Richardson.
6. Event Storming http://eventstorming.com/, by Alberto.

Find tables on which Postgresql doing Sequential Scan

First let’s understand why we should find out the tables for Sequential Scan. Basically what happens is when we design the schema and create indexes, we want that almost in every case the system should use indexes for getting the data, but later somebody writes a query which dosen’t use indexes and do sequential scan and slows down your system at scale.

To solve this part we should first find out on what tables Postgresql is doing sequential scan. For this postgresql provides stats.

Lets do a query on the stats to find tables for sequential scan:

SELECT schemaname, relname,seq_scan, seq_tup_read,seq_tup_read / seq_scan as avg_seq_tup_read FROM pg_stat_all_tables WHERE seq_scan > 0 ORDER BY 5 DESC LIMIT 5;

schemaname | relname  | seq_scan | seq_tup_read | avg_seq_tup_read 
------------+----------+----------+--------------+------------------
 public     | tickets  |        3 |     24000000 |          8000000
 public     | orders5  |       11 |     50000000 |          4545454
 public     | student  |       18 |      5600010 |           311111
 pg_catalog | pg_class |    89061 |     27452103 |              308
 pg_catalog | pg_index |       35 |         1174 |               33
(5 rows)

The above query tells on which tables sequential scan is done , seq_scan provides the count of sequential scan , seq_tup_read tells the total no. of rows read by the system in all the sequential scan and avg_seq_tup_read tells on average how much rows is read by the query.

Now lets see how to figure out whether this is fine or not:

• First list all the tables on which sequential scan should not be running – in our case lets take tickets tables
• Now get the total no. of records in tickets table. – in our case it is – 8000000
• Now check whether system is scanning the entire table or large portion of the table every time
  • as we can see that avg_seq_tup_read is also 8000000 which means system is scanning entire table every time
• If the system scanning entire table and you know this table would be huge in future that means some queries on this table are actually not using the index – which is not good
• check from postgresql logs – the queries on this table which take time (there may be a case you need to enable all query logs for postgresql for some time — generally do not enable this for long , might cause downtime in production , a ok setting would be log any query which is greater than 1 sec)
• Then check using explain analyze whether this query is doing sequential scan – (if in doubt do not run in production system or run in very low load hours)
• After you discovered the query try to create indexes – we have a blogs on understanding indexes and why system not using your index , you can check out these if you are unable to figure out.

Why Postgresql Index not working (Bitmap vs Index) ? – Part 2

In last blog series we learned basics of Indexing. In this blog series part 1 we worked on why query is not using the index. Basically sometimes what happens is, when we run a query we thought it should use a particular index but it actually either use diff. index or do not use index at all and do sequential scan. So in this blogs series we will work on how postgresql choose index.

When we ask Postgres to execute a query it is executed in three steps:

• Parse – to validate the query sytax
• Planner – to plan how the query is executed like whether to do index scan or sequential scan or bitmap scan
• Executor – it will finally execute the planned tasks

Here we are more concerned about the Planner part, the entire purpose why the query needs planning is to reduce the total I/O (reading from disks/memory).

In last part we will work on example where Postgres use the index on the order by column and why it does that.

In this part we will work on why Postgres is doing Bitmap Scan rather than Sequential Scan.

First Lets understand a bit about Index Scan and Bitmap Scan:

• Index Scan
  • Postgres scan the index first and then go to the table and scan the table
  • In this it does random scan of the table row by row
  • It is faster when the index data is sorted in a way which is required by the query.
• Bitmap Scan
  • Postgres scan the index completely and mark all the pages which contains a matching value rather than checking the rows
  • After completed the index scan (mostly entire index) and have list of all the pages which contains the data
  • It then scan all the tables pages (marked one) and again check the validation conditions in the rows.
  • It is chosen over index when cost of random scan with index is higher.

How we can get a hint whether Index scan is preferred or Bitmap Scan

• This is based on the correlation between the order in data and its physical representation(in filesystem)
• Lets understand it by example.
  • First create a table:
    • create table orders as select s as orderno , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;
  • You can we we create a table with a generated series 1-1000000 that is generated sequentially and dumped in the same order as well.
  • Postgresql calculate certain statistics about the column order (increasing or decreasing) with table physical order increasing or decreasing and this data can be see using the following query:
    • First analyze the table : ANALYZE orders;
    • Now run: SELECT attname, correlation FROM pg_stats WHERE tablename = ‘orders1’;
    • you will see correlation for orderno column is 1
    • and this correlation varies from -1 to 1 – desc to asc.
• When the correlation is high Postgres prefers Index scan but when correlation is less postgresql prefer Bitmap.

Lets create a case where Index Scan will be preferred:

First create table and indexes:

create table orders as select s as orderno  , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;

create index orderno_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Now lets check the correlation value :

SELECT attname, correlation FROM pg_stats WHERE tablename = 'orders';
    attname    | correlation 
---------------+-------------
 orderno       |           1
 orderitem     |  0.00561977
 order_created |           1
(3 rows)

As you can see correlation value is 1 , there is high chance Postgresql will use Index Scan:

Now lets query :

explain analyze select * from orders where orderno < 80000   limit 100 ;
                                                             QUERY PLAN             
                                                
------------------------------------------------------------------------------------
------------------------------------------------
 Limit  (cost=0.42..4.22 rows=100 width=45) (actual time=7.494..7.602 rows=100 loops
=1)
   ->  Index Scan using orderno_idx on orders  (cost=0.42..3028.31 rows=79879 width=
45) (actual time=7.492..7.579 rows=100 loops=1)
         Index Cond: (orderno < 80000)
 Planning time: 0.156 ms
 Execution time: 7.653 ms
(5 rows)

As we can see here the Postgres is using Index Scan.

Lets create a case where Bitmap Scan will be preferred:

First create table and indexes:

create table orders as select random()*10000 as orderno  , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,10000000) s;

create index ord_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Here one important change is rather than using s (generated series) we are generating a random number (random()*10000) such that correlation should be less.

Lets see the coorelation:

analyze orders;

SELECT attname, correlation FROM pg_stats WHERE tablename = 'orders5';
    attname    | correlation 
---------------+-------------
 orderno       |  0.00156549
 orderitem     | 0.000700192
 order_created |           1

Here we can see that the correlation is very less.

Now lets query the table:

explain analyze select * from orders where  orderno >9000;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=19145.75..135021.51 rows=1022621 width=49) (actual time=105.159..1042.678 rows=1002305 loops=1)
   Recheck Cond: (orderno > '9000'::double precision)
   Rows Removed by Index Recheck: 5746548
   Heap Blocks: exact=37001 lossy=66089
   ->  Bitmap Index Scan on ord_idx  (cost=0.00..18890.09 rows=1022621 width=0) (actual time=99.446..99.447 rows=1002305 loops=1)
         Index Cond: (orderno > '9000'::double precision)
 Planning time: 0.158 ms
 Execution time: 1067.465 ms


explain analyze select * from orders where  orderno >0;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders5  (cost=0.00..228093.00 rows=10000000 width=49) (actual time=0.032..1013.239 rows=10000000 loops=1)
   Filter: (orderno > '0'::double precision)
 Planning time: 0.282 ms
 Execution time: 1257.460 ms
(4 rows)

Now we can see the system used Bitmap Scan.

Also one more important thing to remember here is that system will sometimes will switch to Sequential Scan also rather than Bitmap Scan when the number of rows selected are very high.

So guys in this tutorial we learned about how Postgres select index primarily Bitmap vs Index.

Why Postgresql Index not working ? – Part 1

In last blog series we learned basics of Indexing. In this blog series we will understand on how to understand why query is not using the index. Basically sometimes what happens is that we assume when we run a query it should use a particular index but it actually either use diff. index or do not use index at all and do sequential scan. So in this blogs series we will work on understanding this case by case.

When we ask Postgres to execute a query it is executed in three steps:

• Parse – to validate the query sytax
• Planner – to plan how the query is executed like whether to do index scan or sequential scan or bitmap scan
• Executor – it will finally execute the planned tasks

Here we are more concerned about the Planner part, the entire purpose why the query needs planning is to reduce the total I/O (reading from disks/memory).

In first part we will work on example where Postgres use diff. index and primarily on the order by column and why it does that.

Lets understand it by example.

First create a table orders with indexes:

create table orders as select s as orderno, md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;

create index orderno_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Now you see we have three columns orderno integer , orderitem text , order_created as date and two index one on orderno and other on order_created.

Now lets query :

we will do two query one which contains sorting on order_created and one without it , lets see which index which query use:

explain analyze select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.54 rows=100 width=45) (actual time=0.016..0.038 rows=100 loops=1)
   ->  Index Scan Backward using order_created_idx on orders  (cost=0.42..37829.43 rows=920121 width=45) (actual time=0.015..0.033 rows=100 loops=1)
         Filter: (orderno > 80000)
 Planning time: 0.170 ms
 Execution time: 0.051 ms
(5 rows)



explain analyze select * from orders where orderno < 80000    limit 100 ;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.22 rows=100 width=45) (actual time=3.121..3.137 rows=100 loops=1)
   ->  Index Scan using orderno_idx on orders  (cost=0.42..3028.31 rows=79879 width=45) (actual time=3.120..3.130 rows=100 loops=1)
         Index Cond: (orderno < 80000)
 Planning time: 0.052 ms
 Execution time: 3.150 ms
(5 rows)

Lets see the first query – we have put a where clause order > 80000 and order by order created .

• In this query normal assumption is it should use Btree index created on orderno as it will help on filtering the data fast
• But actually it is using the Btree index created on order created

Lets understand the reasons for doing so:

• Query with order clause – Very Important to understand this
  • As we have put the order by clause , in our query there will be around 9 million records matching for order > 80000 (as we have created 10 million record from 1 – 10 million)
  • And postgres thought rather than getting all 9 million record in memory and sorting its better we use the sorted index and check for values > 80000
  • there is a higher chance in this of getting values > 80000 in sorted order
• Query without order clause –
  • as we just needs to check for orderno > 80000 and it is already sorted in orderno index so order no index is used.

In this we understood that when we use order by clause the tendency of postgres is to use index which is there on order by field (as in this case order created).

In next blog we will figure out why postgres in doing bitmap scan rather than index scan.