beer planet

Updated: January 4th, 2009

I want to get opinions from outside of my daily circle of people on the best server hardware to use for MySQL. I remember from the conference somebody (Pipes?) mentioning a particular Dell server with multiple disk RAID10 that could supposedly be had for about $6k but I completely misplaced the model number (Frank, did you get my email?).

I know that a multi-disk RAID array with a bunch of fast disks (15k RPM?) is probably the most important method of improving performance, followed by the amount of RAM, so I'm trying to find the best combination/balance of the two. However, server prices on the Internet range so much that I don't even know where to begin to tell a good deal from a bad one. I don't think SSDs can play a role here, because we need at least 200GB of usable space per machine. For comparison, we currently use the following spec: Dual quadcore Intel, 16GB RAM, 200GB RAID1 + 1TB RAID1 using SATA drives (eww?) in a 2U rack (a bit too chunky, isn't it?) made by Zantaz. It performs quite nicely but I think it chokes on the amount of writes way too early.

So, what does everyone think the best configuration is under $10,000? Bonus points if you can provide a link to the vendor site or at least a model number!

• Jay Pipes, Tobias Asplund
• Finding out the number of rows that would have been returned (MyISAM and InnoDB)
• SQL_CALC_FOUND_ROWS and FOUND_ROWS()
• COUNT(*)
• MEMORY table
• if query cache is on, then it makes no difference
• if it's off
• Memory MyISAM is fastest
• FOUND_ROWS() is slightly slower than count(*)
• more in the slides that I'll add later
• quite a lot of humor, these guys are fun
• query union vs index_merge union
• SELECT … WHERE a UNION SELECT … WHERE b
  vs
  SELECT … WHERE a AND b
• index_merge wins
• composite index vs index merge
• composite index is faster
• of course, multiple indexes are more flexible than composite index
• sort union vs composite index
• …
• unix time (int unsigned) vs datetime
• query cache disabled
• 100k rows
• now and 20 years back
• retrieval range between 50 and 365 days
• all data fits into memory
• UNIX_TIMESTAMP is worst
• DATETIME in the middle
• strtotime() fastest
• is BENCHMARK() a good simulation test?
• NOW()
• MONTH(NOW())
• MONTH('YYYY-MM-DD')
• DATE_FORMAT()
• SUBSTRING()
• all about the same if php benchmark script is used
• BENCHMARK() is very unreliable, the results vary a lot
• X in Y how many times?
• 3 different methods on mysql forge
• 2 UDFs and 1 using native functions
• query cache off
• 1K rows
• all rows have "/"
• count the number of "/"
• Marcelo's solution "COUNT_CHAR_OCCURRENCE": 4th place
• Garrett Hill's solution "countstring" (it's technically slightly wrong, because it doesn't account for multibyte characters): 2nd place
• Roland Bouman's solution using mysql functions: 1st place
• Jay's way: function in php: 3rd place
• MEMORY engine HASH index vs BTREE index
• BTREE is faster
• HASH uses less memory

• Speaker: Mikael Ronstrom, PhD, the creator of the Cluster engine
• Explains the cluster structure
• Aspects of performance
  • Response times
  • Throughput
  • Low variation of response times
• Improving performance
  • use low level API (NDB API), expensive, hard
  • use new features in MySQL Cluster Carrier Grade Edition 6.3 (currently 6.3.13), more on this later
  • proper partitioning of tables, minimize communication
  • use of hardware
• NDB API is a C++ record access API
  • supports sending parallel record operations within the same transaction or in different transactions
  • asynchronous and synchronous
  • NDB kernel is programmed entirely asynchronously
• Looking at performance
  • Fire synchronous insert transactions - 10x TCP/IP time cost
  • Five inserts in one synchronous transaction - 2x TCP/IP time cost
  • Five asynchronous insert transactions - 2x TCP/IP time cost
• Case study
  • develop prototype using MySQL C API - performance X, response time Y
  • develop same functionality using synchronous NDB API - performance 3X, response time ~0.5Y
  • develop same functionality using asynchronous NDB API - performance 6X, response time ~0.25Y
• Conclusion on when to use NDB API
  • performance is critical, need speed, response time, etc
  • queries are not very complex
• Conclusion on when not to use NDB API
  • when design time is critical
  • when complex queries are executed, the MySQL optimizer may handle them better
• New features of MySQL Cluster Carrier Grade Edition 6.3.13
• polling based communication
• CPU used heavily even at lower throughput
• avoids interrupt and wake-up delays for new messages
• some good results in benchmarks
• decreases performance when CPU is the limiting factor
• 10% performance improvement on 2, 4, and 8 data node clusters
• 20% improvement if using Dolphin Express
• epoll replacing select system calls (Linux)
• improved performance 20% on a 32-node cluster
• send buffer gathering
• real-time scheduler for threads
• lock threads to CPU
• distribution awareness
• 100-200% improvement when application is distribution aware
• avoid read before Update/Delete with PK
• UPDATE t SET a=const1 WHERE pk=x;
• no need to do a read before UPDATE, all data is already known
• ~10% improvement
• old 'truths' revisited
• previous recommendation was to run 1 data node per computer
• this was due to bugs, which are now fixed
• partitioning tricks
• if there is a table that has a lot of index scans (not primary key) on it, partitioning this table to only be in one node group can be a good idea
• partition syntax for this: PARTITION BY KEY (id) (PARTITION p0 NODEGROUP 0);
• new performance features in MySQL Cluster 5.0
• lock memory in main memory - ensure no swapping occurs in NDB kernel
• batching IN (…) with primary keys
• 100x SELECT FROM t WHERE pk=x;
• SELECT * FROM t WHERE pk IN (x1, …, x100)
• IN-statement is around 10x faster
• use of multi-INSERT
• similar 10x speedup
• new features in MySQL Cluster CGE version 6.4 (beta, only available in bitkeeper for now)
• multi-threaded data nodes - currently no benefit using DBT2 but 40% increase in throughput for some NDB API benchmarks
• DBT2 improvements to follow later
• use of hardware, CPU choice
• Pentium D @ 2.8Ghz -> Core 2 Duo @ 2.8Ghz => 75% improvement
• doubling L2 cache doubles thread scalability
• choice of Dolphin Express interconnect increases throughput 10-400%
• scalability of DBT2 threads
• 1-2-4 threads - linear
• 4-8 threads - 40-70%
• 8-16 threads - 10-30%
• decreasing scalability over 16 threads
• current recommendation by Mikael himself: use twice as many SQL nodes as data nodes
• future software performance improvements
• batched key access - 0-400% performance improvement
• improvement scan protocol - ~15% improvement
• incremental backups
• optimized backup code
• parallel I/O on index scans using disk data
• Niagara-II benchmark from 2002
• simple read, simple update, both transactional
• 72-CPU Sunfire 15k, 256GB RAM
• CPUs: ultra sparc-III @ 900Mhz
• 32-node NDB Cluster, 1 data node locked to 1 CPU
• db size 88GB, 900 mil records
• simple reads 1.5mil reads per second
• simple update 340,000 per second
• Everyone is overwhelmed, so no questions are asked