Be careful with my findings, I appear to have compile in debug mode, I am redoing the benchmarks. Updated version here.
I recently had to work with many customers having large number of connections opened in MySQL and although I told them this was not optimal, I had no solid arguments to present. More than once, I heard: it is not a problem, those connections are Sleeping! In order to remedy to this situation, I decided to run a series of DBT2 benchmarks while the number of idle connections was varied from 0 to 1500. I was expecting an influence, because those idle threads are presents in internal MySQL list objects that need to be scanned and also the socket SELECT call needs to build a FD bitmap after[Read more...]
It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).
How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it[Read more...]
DiskPageBufferMemoryparameter (similar to innodb_bufferpool), in order to avoid disk seeks. It is much (understatement) faster to fetch data from the
DiskPageBufferMemory. Next time, I will explain about other counters you can get from
CREATE TABLE `t1` ([Read more...]
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,
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 256kB the behavior changes and becomes slower. After reading a post from Ronald Bradford (here), I decide to verify and benchmark performance while varying the size of the sort_buffer. It is my understanding that the sort_buffer is used when no index are available to help the sorting so I created a MyISAM table with one char column without an index:[Read more...]
Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.
One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.
A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
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 is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500..."
What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it[Read more...]
RedoBufferin config.ini) looked like:
mysql< select * from ndbinfo.logbuffers;[Read more...]
| node_id | log_type | log_id | log_part | total | used |
| 3 | REDO | 0 | 1 | 50331648 | 196608 |
| 3 | REDO | 0 | 2 | 50331648 | 294912
Baron makes an excellent point in Why you should ignore MySQL’s key cache hit ratio — ratio is not the same as rate. Furthermore, rate is [often] the important thing to look at.
This is something that, at Pythian, we internalized a long time ago when thinking about MySQL tuning. In fact, mysqltuner 2.0 takes this into account, and the default configuration includes looking at both ratios and rates.
If I told you that your database had a ratio of temporary tables written to disk of 20%, you might think “aha, my database is slow because of a lot of file I/O caused by writing temporary tables to disk!”. However, that 20% ratio may actually mean a[Read more...]
Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?
Benefits of connection pooling
Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL[Read more...]
There was a discussion on LinkedIn one month ago that caught my eye:
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..
A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:[Read more...]
The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.
mysql> SHOW INNODB STATUS\G .... Database pages 2318457 Old database pages 855816 Modified db pages 457902 Log flushed up to 10026890404067 Last checkpoint at[Read more...]
There are a few parameters that may be worth looking at when first installing InfiniDB; NumBlocksPct for managing memory, PmMaxMemorySmallSide for optimizing join behaviors, and MaxOutstandingRequests which changes how individual queries share resources.
The NumBlocksPct parameter manages the amount of memory on each server allocated to store data blocks and is expressed as a % of server memory. For example, to allocate 5 GB for the data buffer cache for a server with 8GB memory, set thRead More...