Starting from PostgreSQL 8.4, there is a valuable addition in PostgreSQL features list: Per function statistics. Prior to this feature , there wasn’t an easy way to find out most time consuming functions in the database.
For time spent on single call, it’s easy to find out by executing function manually but it was too cumbersome and in some cases impossible to find out total time spent by function for the given time period. Enabling full logging and aggregating duration for each call by reading large log files was the only way to get that statistic. Now, you can find this details by querying a single view pg_stat_user_functions ! Awesome!
To enable function tracking you need to enable “track_functions” postgresql.conf parameter. The default is none, which disables function statistics tracking. Specify pl to track only procedural-language functions, all to also track SQL and C language functions.
#track_functions = none # none, pl, all
Once you have track_functions enabled in your database, you can get details by querying pg_stat_user_functions view:
select * from pg_stat_user_functions;
It provides following details:
Self time is the amount of time spent in the function itself, total time includes the time spent in functions it called. Time values are in milliseconds.
Here is the easy solution to track and find candidate that requires tuning!!
If you’ve ever gotten a page about database connections are blocked, or phone call or email from an annoyed user whose transaction just won’t go through, or from a developer who can’t understand why application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked.
Omniti’s labs project Tasty Treats for PostgreSQL provides useful tools for proactive database monitoring . email_locked_queries is the new addition to this toolset . This tool can be handy for proactive lock monitoring in your database to find the blocked and blocking sessions and it sends out email notification if the there is a connection waiting for lock(s) for more than 10 minutes. It’s trivial to change the query if you want to be notified at different threshold.
Let’s test the query tool and see the results by creating locking scenario:
postgres=# create table testlock(foo text,bar text);
postgres=# insert into testlock values (‘lock’,’me’);
INSERT 0 1 postgres=# insert into testlock values (‘find’,’me’);
INSERT 0 1
postgres=# select * from testlock;
foo | bar
find | me
lock | me
postgres=# update testlock set foo='hang' where foo='lock';
And Session 2, try to update same row:
postgres=# update testlock set foo='escapeme' where foo='lock';
This statement will hang, blocked by the lock that Session 1 is holding on the row.
I ran the query tool and I got this in my email:
-[ RECORD 1 ]
locked_mode | ShareLock
blocked_pid | 9468
blocked_user | postgres
blocked_statement | update testlock set foo='escapeme' where foo='lock';
blocked_query_age | 00:10:28.98475
blocking_pid | 8056
blocking_user | postgres
blocking_statement | IDLE in transaction
blocking_query_age | 00:10:24.403767
blocking_xact_age | 00:10:58.529881
Let me know if you have suggestions to make it more better.
Today, I gave Security talk on Achieving PCI Compliance at PgWest 2010, San Francisco. Slides are available to download.
I found very good docs to avoid bitmap indexes on OLTP environment to avoid deadlocks:
I hope this will help!!
I found these lessons shared by someone! He learned from a 90 year old person … Good read!!
1. Life isn’t fair, but it’s still good.
2. When in doubt, just take the next small step.
3. Life is too short to waste time hating anyone.
4. Your job won’t take care of you when you are sick. Your friends and parents will. Stay in touch.
5. Pay off your credit cards every month.
6. You don’t have to win every argument. Agree to disagree.
7. Cry with someone. It’s more healing than crying alone.
8. Save for retirement starting with your first paycheck.
9. When it comes to chocolate, resistance is futile.
10. Make peace with your past so it won’t screw up the present.
11. Don’t compare your life to others. You have no idea what their journey is all about.
12. If a relationship has to be a secret, you shouldn’t be in it..
13. Take a deep breath. It calms the mind..
14. Get rid of anything that isn’t useful, beautiful or joyful.
15. Whatever doesn’t kill you really does make you stronger.
16. It’s never too late to have a happy childhood. But the second one is up to you and no one else.
17. When it comes to going after what you love in life, don’t take no for an answer.
18. Burn the candles, use the nice sheets, wear the fancy lingerie. Don’t save it for a special occasion. Today is special.
19. Over-prepare, then go with the flow.
20. Be eccentric now. Don’t wait for old age to wear purple.
21.. The most important sex organ is the brain.
22. No one is in charge of your happiness but you.
23. Frame every so-called disaster with these words ”In five years, will this matter?”
24. Always choose life.
25. Forgive everyone everything.
26. What other people think of you is none of your business.
27. Time heals almost everything. Give time, time.
28. However good or bad a situation is, it will change.
29. Don’t take yourself so seriously. No one else does.
30. Believe in miracles.
31. Don’t audit life. Show up and make the most of it now.
32. Growing old beats the alternative — dying young.
33. Your children get only one childhood.
34. All that truly matters in the end is that you loved.
35. Get outside every day. Miracles are waiting everywhere.
36. If we all threw our problems in a pile and saw everyone else’s, we’d grab ours back.
37. Envy is a waste of time. You already have all you need.
38. The best is yet to come.
39. No matter how you feel, get up, dress up and show up.
41. Life isn’t tied with a bow, but it’s still a gift.
If you are working in IT industry, you must have come across these terms RAID10 and RAID5.
Let’s learn about RAID…
RAID 10 = Combining features of RAID 0 + RAID 1. It provides optimization for fault tolerance. RAID 0 helps to increase performance by striping volume data across multiple disk drives. RAID 1 provides disk mirroring which duplicates your data.
In RAID5, 4th disk is used for parity bit.
Database: We choose RAID10 over RAID5 for better performance.
One of the twitter friend asked me a question . It was related to switching production to standby database and standby database to new production under oracle environment. I came across this situation before 4 years. I provided this link and it’s nicely solved his problem.
Keep it bookmarked, if you need in future!
Welcome to slowquery!!
This is my knowledge warehouse but query slowly!!
Looking forward to share useful tricks, tips etc..
Thanks for visiting!!
Open Source databases still needs solid presence in enterprise world. One of the myths about open source databases is that they aren’t secure. To eliminate this myth we have to share our experience related to security aspect of PostgreSQL database. At OmniTi , we help businesses to achieve PCI compliance or provide payment processor:PayPI . PayPI runs on PostgreSQL database.
I feel that Security aspect of the PostgreSQL database isn’t marketed as it should be. Who doesn’t want secured applications and databases ? The answer is everyone cares about security but we can’t able to find many talks about security in the open source conferences. PgWest 2010
has wide variety of talks
. Thanks to conference committee to include number of talks about security aspects of application and database. One of them is my
session that will talk about achieving PCI compliance with PostgreSQL
. If your system transmit, process or store credit card data then it should be PCI compliance system. Please don’t miss this opportunity to share your knowledge & experience during the talk. Even if your system doesn’t need PCI compliance, we can still discuss about security in general and share knowledge about features related to security.
If you are planning to attend PgWest 2010 at San Francisco from Nov 2, 2010 to Nov 4, 2010, looking forward to see you in-person.
conference is around the corner (Sept 30th) and there are plenty of industry leading speakers
and most advance technology sessions
to choose from list. Most probably I will be attending second day of two days conference. I spent a lot of time to decide on most interesting sessions to attend from wide variety of sessions. In the past, for other conferences, I found easy to choose from two parallel sessions but it’s not the case at Surge. It’s one of the most difficult task to choose one from two parallel talks.
I hope my list will help you to outline your sessions or at least encourage you to think now!
This is my outline of sessions to attend at Surge Conference on Day -2:
- Enterprise solutions from commodity components: The Promise and the Peril – Bryan Cantrill
- Don’t bet the farm on your cache – Brian Akins
- Top 10 Lessons Learned from Deploying Hadoop in a Private Cloud – Rod Cope
- Design for Scale – Patterns, Anti-Patterns, Successes and Failures – Christopher Brown
- From disaster to stability: scaling challenges of my.opera.com -Cosimo Streppone
- Availability, the Cloud and Everything -Joe Williams