Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How to find un-indexed queries in MySQL, using tcpdump

xaprb.com

Post reply on HN
Print view View original

1–10 of 11 posts

Re: How to find un-indexed queries in MySQL, using tcpdump

#5

Post by russss »

a2tech wrote: post #3

This IS a very neat trick-now does anyone know how to find un-indexed queries in PostgreSQL?

Although this is a stunning trick which I will probably use myself, I think finding un-indexed queries is the wrong way of going about things. Sometimes queries are slow even if they do use indexes, so the best route is to find slow queries.

In MySQL finding slow queries is a pain because you STILL have to restart to enable the slow query log. In Postgres you don't (just set log_min_duration_statement to 0 and reload). Then you can use pgFouine (http://pgfouine.projects.postgresql.org) to analyze them - it produces a nice report.

Re: How to find un-indexed queries in MySQL, using tcpdump

#6

Post by nettdata »

russss wrote: post #5
a2tech wrote: post #3

This IS a very neat trick-now does anyone know how to find un-indexed queries in PostgreSQL?

Although this is a stunning trick which I will probably use myself, I think finding un-indexed queries is the wrong way of going about things. Sometimes queries are slow even if they do use indexes, so the best route is to find slow queries. In MySQL finding slow queries is a pain because you STILL have to restart to enable the slow query log. In Postgres you don't (just set log_min_duration_statement to 0 and reload…

Un-indexed queries are not inherently evil... sometimes they are faster than using/maintaining an index.

Queries are all about context; how big of a data set, how much/fast is that data set changing, how often is the query being made, etc.

I've seen some cases where a single table had a crap-load of indexes, one specifically for each query that was being performed. The overhead of maintaining all of those indexes was killing performance.

I find the most effective query tuning is a result of having realistic stress/load testing combined with internal analytics from the database (EXPLAIN or EXPLAIN PLAN). This points out the low hanging fruit, which you can optimize using various methods such as indexes, materialized views, and in some cases, re-arranging the data model to better suit your queries.

It also gives you a better idea of which queries are being hit the most, and which ones it makes sense to spend your time tuning.

Re: How to find un-indexed queries in MySQL, using tcpdump

#7

Post by Erwin »

a2tech wrote: post #3

This IS a very neat trick-now does anyone know how to find un-indexed queries in PostgreSQL?

Use log_duration in postgresql.conf to log long queries, explain ANALYZE them to find out what they're doing. Sometimes a full table sequential scan is faster than using an index -- e.g. if the index would (according to gathered statistics data) hit 90% of the table anyway.

There are also various other gathered statistics you can look at, e.g. cache hits, index hits etc.

Re: How to find un-indexed queries in MySQL, using tcpdump

#9

Post by jotto »

russss wrote: post #5
a2tech wrote: post #3

This IS a very neat trick-now does anyone know how to find un-indexed queries in PostgreSQL?

Although this is a stunning trick which I will probably use myself, I think finding un-indexed queries is the wrong way of going about things. Sometimes queries are slow even if they do use indexes, so the best route is to find slow queries. In MySQL finding slow queries is a pain because you STILL have to restart to enable the slow query log. In Postgres you don't (just set log_min_duration_statement to 0 and reload…

I don't think you have to restart to enable the query log, you should be able to just /etc/init.d/mysql reload
Post reply on HN