Vim tutorial: Made Easy

Some quick basics on working with your file.

• vi file: open your file in vim
• :w: write your changes to the file
• :q!: get out of vim (quit), but without saving your changes (!)
• :wq: write your changes and exit vim
• :saveas ~/some/path/: save your file to that locationvim

[ NOTE: While :wq works I tend to use ZZ, which doesn’t require the “:” and just seems faster to me. You can also use :x ]

• ZZ: a faster way to do :wq

Resource Link:

https://danielmiessler.com/study/vim/

Best Vidoe Tutorial: Step by Step

 https://linuxconfig.org/vim-tutorial

All commands at a glance:

http://www.yolinux.com/TUTORIALS/LinuxTutorialAdvanced_vi.html

Practice Here: 

http://www.openvim.com/

SQL Performance Tuning: Indexing - Made easy

Indexing Rule:

i) The more indexes you have, the slower INSERTs will become because more writes will need to happen to keep the indexes updated.
ii) Make sure to run VACUUM ANALYZE to keep data statistics up to date — as well as recover disk space.
iii) Make sure you ANALYZE when creating a new index, otherwise Postgres will not have analyzed the data and determined that the new index may help for the query.
iv) Joins are also a better solution than subqueries — Postgres will even internally “rewrite” a subquery, creating a join, whenever possible, but this of course increases the time it takes to come up with the query plan. So be a pal and use joins instead of subselects.
v) Always prefer doing an INNER JOIN instead of a LEFT OUTER JOIN
vi) HTU: Especially as more joins are added to a query, left joins limit the planner’s ability optimize the join order.
vii) Postgres can use an index when doing some_string LIKE 'pattern%' but not for some_string LIKE '%pattern%'.  PostgreSQL can use a b-tree index for prefix searches (eg LIKE 'TEST%') with LIKE or SIMILAR TO if the database is in the C locale or the index has text_pattern_ops.
viii) There are a number of operators available for pattern matching in PostgreSQL. LIKE, SIMILAR TO and ~ are covered in this chapter of the manual.(http://www.postgresql.org/docs/current/interactive/functions-matching.html)

If you can, use LIKE (~~), it's fastest.
If you can't, use a regular expression (~), it's more powerful.
Never user SIMILAR TO. It's utterly pointless. More on this further down.

RL: http://stackoverflow.com/questions/12452395/difference-between-like-and-in-postgres
http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql
ix) By default, Postgres locks writes (but not reads) to a table while creating an index on it.
x) A unique index guarantees that the table won’t have more than one row with the same value. It’s advantageous to create unique indexes for two reasons: data integrity and performance. Lookups on a unique index are generally very fast.
xi) Distinction between unique indexes and unique constraints:
Unique indexes can be though of as lower level, since expression indexes and partial indexes cannot be created as unique constraints. Even partial unique indexes on expressions are possible.

For Migration:

i) concurrent indexes must be created outside a transaction.
ii) it’s a good idea to isolate concurrent index migrations to their own migration files.

RL: https://robots.thoughtbot.com/how-to-create-postgres-indexes-concurrently-in

What is partial index?

Partial indexes

+

A partial index is an index with a WHERE clause. It will only index rows that match the supplied predicate. You can use them to exclude values from an index that you hardly query against.

+

For example, you have an orders table with a completed flag. The sales people want to know what orders over $100,000.00 haven’t been completed because they want to collect their bonuses, so you build a view in your app to show them just that (and negotiate a cut on the bonus). You could create the following index:

+

CREATE INDEX orders_incomplete_amount_index
   on orders (amount) WHERE complete is not true;

+

Which will be used by queries of the form:

+

SELECT * FROM orders
  where amount > 100000 AND complete is not true;

Why is my query not using an index?

There are many reasons why the Postgres planner may choose to not use an index. Most of the time, the planner chooses correctly, even if it isn’t obvious why. It’s okay if the same query uses an index scan on some occasions but not others. The number of rows retrieved from the table may vary based on the particular constant values the query retrieves. So, for example, it might be correct for the query planner to use an index for the query select * from foo where bar = 1, and yet not use one for the query select * from foo where bar = 2, if there happened to be far more rows with “bar” values of 2. When this happens, a sequential scan is actually most likely much faster than an index scan, so the query planner has in fact correctly judged that the cost of performing the query that way is lower.

Efficient Use of PostgreSQL Indexes

Postgres supports many different index types:

• B-Tree is the default that you get when you do CREATE INDEX. Virtually all databases will have some B-tree indexes. The B stands for Balanced, and the idea is that the amount of data on both sides of the tree is roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same ballpark. B-Tree indexes can be used for equality and range queries efficiently. They can operate against all datatypes, and can also be used to retrieve NULL values. Btrees are designed to work very well with caching, even when only partially cached.
• Hash Indexes are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, need to be manually rebuilt after crashes, and are not replicated to followers, so the advantage over using a B-Tree is rather small.
• Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.
• Generalized Search Tree (GiST) indexes allow you to build general balanced tree structures, and can be used for operations beyond equality and range comparisons. They are used to index the geometric data types, as well as full-text search.

Resource Link: Efficient Use of PostgreSQL Indexes

PostgreSQL Performance Considerations

There are a number of variables that allow a DBA to tune a PostgreSQL database server for specific loads, disk types and hardware. These are fondly called the GUCS (Global Unified Configuration Settings) and you can take a look via the pg_settings view. There are also a few of things that you can do in your application to get the most out of Postgres:

Know the Postgres index types

+

By default CREATE INDEX will create B-tree indexes which will serve well for most cases where we use equality, inequality and range operators. However there are cases where you can build different indexing strategies with GiST (Generalized Search Tree) indexes. For example, Postgres ships with built in GiST operator classes for geometric operators — for dealing with the geometric types like point, box, polygon, circle, and others. There are more interestingGiST index examples in the contrib packages for things like textual search, tree structures, and more.

Consider multicolumn indexes, when it makes sense

+

When your query filters the data by more than one column, be it with the WHERE clause orJOINs, multicolumn indexes may prove useful. If you create an index on columns (a, b), the Postgres planner can use it for queries

+

WHERE a = 1
WHERE a = 1 AND b = 2

+

However, it will not use it for queries using:

+

WHERE a = 1 OR b = 2
WHERE b = 2

+

But Postgres also has the ability to use multiple indexes in a single query. This may come in handy if you are using the OR operator, but will also make use of it for AND queries. So it boils down to what the most common case is according to your application’s read patterns and optimize for that, either with an an index on (a, b) and another on (b), or two separate single column indexes.

Partial indexes

+

Simply put, a partial index is an index with a WHERE clause. It will only index rows that match the supplied predicate. You can use them to exclude values from an index that you hardly query against.

+

For example, you have an orders table with a completed flag. The sales people want to know what orders over $100,000.00 haven’t been completed because they want to collect their bonuses, so you build a view in your app to show them just that (and negotiate a cut on the bonus). You could create the following index:

+

CREATE INDEX orders_incomplete_amount_index
   on orders (amount) WHERE complete is not true;

+

Which will be used by queries of the form:

+

SELECT * FROM orders
  where amount > 100000 AND complete is not true;

Don’t over index

+

Part of maintaining a healthy database is going back and making sure you don’t have any unused indexes. It’s common to add indexes to address a specific performance issue for a particular query, but in many cases indexes start to pile up becoming dead weight. Remember that the more indexes you have, the slower INSERTs will become because more writes will need to happen to keep the indexes updated.

Keep statistics updated

+

Make sure to run VACUUM ANALYZE to keep data statistics up to date — as well as recover disk space. In addition, Postgres ships with a built in auto-vacuum daemon whose purpose is to automate the execution of VACUUM ANALYZE. You should read up on considerations for setting the auto-vacuum daemon’s frequency according to your database size and usage characteristics.

+

Make sure you ANALYZE when creating a new index, otherwise Postgres will not have analyzed the data and determined that the new index may help for the query.

Use more joins

+

Postgres is perfectly capable of joining multiple tables in a single query. In a running app, queries with five joins are completely acceptable, and will help bring in the data required by your app, reducing the number of trips to the database. In most cases, joins are also a better solution than subqueries — Postgres will even internally “rewrite” a subquery, creating a join, whenever possible, but this of course increases the time it takes to come up with the query plan. So be a pal and use joins instead of subselects.

Prefer INNER JOINs

+

If the cardinality of both tables in a join is guaranteed to be equal for your result set, always prefer doing an INNER JOIN instead of a LEFT OUTER JOIN. A lot of research and code has gone into optimizing outer joins in Postgres over the years. But the reality is that especially as more joins are added to a query, left joins limit the planner’s ability optimize the join order.

Know how to understand the EXPLAIN output

+

Paste the output of explain analyze [some query] into explain.depesz.com to help identify the most costly nodes in the query plans.

+

Understanding EXPLAIN output is a very extensive topic, but these are some general guidelines when reading plans:

+
• Are the cost estimates vs. actuals close, or are there discrepancies? Typically a sign of not having ANALYZEd recently.
+
• Is an index not being used? The planner may be choosing not to use it for good reason.
+
• Is there query using the some_string LIKE pattern? If so, make sure the pattern is anchored at the beginning of the string. Postgres can use an index when doingsome_string LIKE 'pattern%' but not for some_string LIKE '%pattern%'
+
• Have you vacuumed recently? Have you indexed foreign keys?
+
• Are there table scans that should use an index instead? Not all table scans are bad — there are cases where it will perform better than an index scan
+
• Good database schema design yields better query plans. Read up on database normalization.

Never try to optimize queries on your development machine

+

The Postgres planner collects statistics about your data that help identify the best possible execution plan for your query. In fact, it will just use heuristics to determine the query plan if the table has little to no data in it. Not only do you need realistic production data in order to analyze reasonable query plans, but also the Postgres server’s configuration has a big effect. For this reason it’s required that you run your analysis on either the production box, or on a staging box that is configured just like production, and where you’ve restored production data.

Experimentation is key

+

There are no hard and fast rules to a perfectly optimized system. The best advice is to try out different configurations, use a tool like NewRelic to find out what the bottlenecks are, and liberally try out different combinations of indexes and queries that yield best results for your particular situation.

Resource Link: PostgreSQL Performance Considerations

Joel Test

Joel Test score: 12 out of 12

The Joel Test is a twelve-question measure of the quality of a software team.

• Do you use source control?
• Can you make a build in one step?
• Do you make daily builds?
• Do you have a bug database?
• Do you fix bugs before writing new code?
• Do you have an up-to-date schedule?
• Do you have a spec?
• Do programmers have quiet working conditions?
• Do you use the best tools money can buy?
• Do you have testers?
• Do new candidates write code during their interview?
• Do you do hallway usability testing?

How to avoid deadlock in Java Threads

How to avoid deadlock in Java? is one of the question which is flavor of the season for multi-threading, asked more at a senior level and with lots of follow up questions. Even though question looks very basic but most of developer get stuck once you start going deep.

Interview questions starts with "What is deadlock?"
Answer is simple, when two or more threads are waiting for each other to release lock and get stuck for infinite time, situation is called deadlock . It will only happen in case of multitasking.

How do you detect deadlock in Java ?

Though this could have many answers , my version is first I would look the code if I see nested synchronized block or calling one synchronized method from other or trying to get lock on different object then there is good chance of deadlock if developer is not very careful.

Other way is to find it when you actually get locked while running the application , try to take thread dump , in Linux you can do this by command "kill -3" , this will print status of all the thread in application log file and you can see which thread is locked on which object.

Other way is to use jconsole, it will show you exactly which threads are get locked and on which object.

Write a Java program which will result in deadlock?

Once you answer this , they may ask you to write code which will result in deadlock ?
here is one of my version

/**
 * Java program to create a deadlock by imposing circular wait.
 * 
 * @author WINDOWS 8
 *
 */
public class DeadLockDemo {

    /*
     * This method request two locks, first String and then Integer
     */
    public void method1() {
        synchronized (String.class) {
            System.out.println("Aquired lock on String.class object");

            synchronized (Integer.class) {
                System.out.println("Aquired lock on Integer.class object");
            }
        }
    }

    /*
     * This method also requests same two lock but in exactly
     * Opposite order i.e. first Integer and then String. 
     * This creates potential deadlock, if one thread holds String lock
     * and other holds Integer lock and they wait for each other, forever.
     */
    public void method2() {
        synchronized (Integer.class) {
            System.out.println("Aquired lock on Integer.class object");

            synchronized (String.class) {
                System.out.println("Aquired lock on String.class object");
            }
        }
    }
}

If method1() and method2() both will be called by two or many threads , there is a good chance of deadlock because if thread 1 acquires lock on Sting object while executing method1() and thread 2 acquires lock on Integer object while executing method2() both will be waiting for each other to release lock on Integer and String to proceed further which will never happen.

This diagram exactly demonstrate our program, where one thread holds lock on one object and waiting for other object lock which is held by other thread.

How to avoid deadlock in Java?

Now interviewer comes to final part, one of the most important in my view; How do you fix deadlock? or How to avoid deadlock in Java?

If you have looked above code carefully then you may have figured out that real reason for deadlock is not multiple threads but the way they are requesting lock , if you provide an ordered access then problem will be resolved , here is my fixed version, which avoids deadlock by avoiding circular wait with no preemption.

public class DeadLockFixed {

    /**
     * Both method are now requesting lock in same order, first Integer and then String.
     * You could have also done reverse e.g. first String and then Integer,
     * both will solve the problem, as long as both method are requesting lock
     * in consistent order.
     */
    public void method1() {
        synchronized (Integer.class) {
            System.out.println("Aquired lock on Integer.class object");

            synchronized (String.class) {
                System.out.println("Aquired lock on String.class object");
            }
        }
    }

    public void method2() {
        synchronized (Integer.class) {
            System.out.println("Aquired lock on Integer.class object");

            synchronized (String.class) {
                System.out.println("Aquired lock on String.class object");
            }
        }
    }
}

Now there would not be any deadlock because both methods are accessing lock on Integer and String class literal in same order. So, if thread A acquires lock on Integer object , thread B will not proceed until thread A releases Integer lock, same way thread A will not be blocked even if thread B holds String lock because now thread B will not expect thread A to release Integer lock to proceed further.

Read more: http://javarevisited.blogspot.com/2010/10/what-is-deadlock-in-java-how-to-fix-it.html#ixzz4ED8fr5if