A dead simple trick to detect top unindexed or badly indexed mysql queries
The ingredients are:
1. tcpdump – check your local package distributor ( apt-get, pkg_add, etc.)
2. a great maatkit tool, mk-query-digest – use wget http://www.maatkit.org/get/mk-query-digest to fetch the simple perl script, then chmod +x it, and move to a dir in path
When the stage is set, cast the following mystic spell:
tcpdump -i eth0 port 3306 -s 65535 -x -n -q -tttt | mk-query-digest \
--type tcpdump --filter '($event->{No_index_used} \
|| $event->{No_good_index_used})'
Of course replace eth0 with the proper interface and do similarly with the port if mysql listens on a non-default one.
Let it run for a while, possibly redirecting its stdout to a log file, then ctrl-c the beast and study the output. You will be surprised how useful this can prove.
Handling a huge amount of fulltext searches part 2 – the internals
In part one, I generally drafted the ingredients needed for the system, now it’s time to have a deeper look at the internal workings, processes and connections.
The system consists of two components, the so-called backend and the frontend. The frontend is a layer between the users and the data – in the traditional model-view-controller (MVC) paradigm, it covers both the controller and the view part. It receives user input (the searches), gets the data from the backend, processes it, and transforms the result to a form which is user-, or further processing friendly.
The backend does the heavy lifting (full text searching, caching). Now let’s see how they build up.
Handling a huge amount of fulltext searches
How do you handle a massive number of fulltext searches? MySQL? Been there, done that. It’s a no-go for average servers. PostgreSql with Tsearch2?
See a nice solution cooked from ruby, thin, memcached and sphinx.
