The parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above [...]
Data mart or data warehouse?
July 15, 2010 By 13 Comments
This is part two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate to answer those queries. [...]
Scaling: Consider both Size and Load
July 13, 2010 By 5 Comments
So lets imagine you have the server handling 100.000 user accounts. You can see the CPU,IO and Network usage is below 10% of capacity – does it mean you can count on server being able to handle 1.000.000 of accounts ? Not really, and there are few reasons why, I’ll name most important of them: [...]
Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread [...]
How many partitions can you have ?
December 5, 2009 By 19 Comments
I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most [...]
Tokyo Tyrant – The Extras Part II : The Performance Wall
November 11, 2009 By 3 Comments
Continuing my look at Tokyo Tyrant/Cabinet and addressing some of the concerns I have seen people have brought up this is post #2. #2. As your data grows does Tokyo Cabinet slow down? Yes your performance can degrade. One obvious performance decrease with a larger dataset is you start to increase the likelihood that your [...]
MySQL-Memcached or NOSQL Tokyo Tyrant – part 1
October 15, 2009 By 16 Comments
All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to there problem. Why? Because their other applications use it, so why not the new application? Over the past couple of months I have been doing a ton of work for clients who [...]
Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
Recently I was doing a little work for a client who has MyISAM tables with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they’re ad-hoc (can access [...]
Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE=’CA’ they would create 2 separate indexes on AGE and STATE [...]
Just do the math!
July 6, 2009 By 4 Comments
One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve. Let me start with example to make it clear. Lets say [...]
