There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]
MySQL caching methods and tips
April 4, 2011 By 5 Comments
“The least expensive query is the query you never run.” Data access is expensive for your application. It often requires CPU, network and disk access, all of which can take a lot of time. Using less computing resources, particularly in the cloud, results in decreased overall operational costs, so caches provide real value by avoiding [...]
Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on variables, or look for content in the Percona documentation or forums. So I present to you what is now my newest Firefox toolbar bookmark: an option and [...]
When should you store serialized objects in the database?
January 21, 2010 By 47 Comments
A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically.
Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
There was a discussion on LinkedIn one month ago that caught my eye: Database search by “within x number of miles” radius? Anyone out there created a zipcode database and created a “search within x numer of miles” function ? Thankful for any tips you can throw my way.. J A few people commented that [...]
SystemTap – DTrace for Linux ?
September 14, 2009 By 17 Comments
Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but [...]
High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]
ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking [...]
MySQL Full Text Search in Action
February 7, 2008 By 9 Comments
Preparing to move I’m selling stuff on GumTree which is UK based clone of Craigslist offering similar functionality but with Ads Similarly to Craigslist GumTree is powered by MySQL but unlike craigslist it looks like they are shy in tuning their MySQL Full Text Search index configuration and setting proper ft_min_word_len value. Searching for Audi [...]
Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
Monty Taylor posted interesting investigation of the fact read_buffer_size variable affects connection speed. This is not something you would expect right ? me too. Not only global user data is expected to be cached on startup but even if it is not why would you do full table scan to fetch single user information ? [...]
