In this blog I will talk about how we (the Sun/MySQL Performance Team) eliminated the need for a lock to get better scalability with MySQL 5.1.24+.

While comparing sysbench runs using two different versions of MySQL 5.1, I noticed a big difference in the system utilization. One version had much more idle time than the other. This difference was much more apparent with high thread counts on machines with lots of CPU. A look at the system calls showed a large number of lwp_park system calls. That is, threads were being put to sleep.

bash # $ dtrace -qn 'syscall:::entry{@c[probefunc]=count()}' -n tick-5s'{trunc(@c, 10);exit(0)}'

  p_online                                                       1535
  ioctl                                                          2255
  fcntl                                                          6134
  priocntlsys                                                    6462
  write …

In this blog I will talk about how we (the Sun/MySQL Performance Team) eliminated the need for a lock to get better scalability with MySQL 5.1.24+.

While comparing sysbench runs using two different versions of MySQL 5.1, I noticed a big difference in the system utilization. One version had much more idle time than the other. This difference was much more apparent with high thread counts on machines with lots of CPU. A look at the system calls showed a large number of lwp_park system calls. That is, threads were being put to sleep.

bash # $ dtrace -qn 'syscall:::entry{@c[probefunc]=count()}' -n tick-5s'{trunc(@c, 10);exit(0)}'

  p_online                                                       1535
  ioctl                                                          2255
  fcntl                                                          6134
  priocntlsys                                                    6462
  write …

In this blog I will talk about how we (the Sun/MySQL Performance Team) eliminated the need for a lock to get better scalability with MySQL 5.1.24+.

While comparing sysbench runs using two different versions of MySQL 5.1, I noticed a big difference in the system utilization. One version had much more idle time than the other. This difference was much more apparent with high thread counts on machines with lots of CPU. A look at the system calls showed a large number of lwp_park system calls. That is, threads were being put to sleep.

bash # $ dtrace -qn 'syscall:::entry{@c[probefunc]=count()}' -n tick-5s'{trunc(@c, 10);exit(0)}'

  p_online                                                       1535
  ioctl                                                          2255
  fcntl                                                          6134
  priocntlsys                                                    6462
  write …

I've been doing some benchmarking recently to satisfy the curiosity about 5.1's performance compared with 4.1.  The major question this time revolves around how much additional performance an external RAID array can provide (for us it's typically beyond the 6 drives a Dell 2950 can hold). 

These tests are done on using an MSA-30 drive enclosure with 15k-SCSI drives.  The testing framework is sysbench oltp.  The test names are hopefully fairly obvious:  selects = single selects, reads = range tests, xacts = transaction tests, etc.   Transaction tests are counting individual queries, not transactions.   The "Rdm" tests are using a uniform distribution, whereas the non-'Rdm' tests are 75% of queries are using 10% of the rows.  

…

I've been doing some benchmarking recently to satisfy the curiosity about 5.1's performance compared with 4.1.  The major question this time revolves around how much additional performance an external RAID array can provide (for us it's typically beyond the 6 drives a Dell 2950 can hold). 

These tests are done on using an MSA-30 drive enclosure with 15k-SCSI drives.  The testing framework is sysbench oltp.  The test names are hopefully fairly obvious:  selects = single selects, reads = range tests, xacts = transaction tests, etc.   Transaction tests are counting individual queries, not transactions.   The "Rdm" tests are using a uniform distribution, whereas the non-'Rdm' tests are 75% of queries are using 10% of the rows.  

…

A few days ago I reported on a problem with savepoints, but all that it appeared to be was an extemporaneous warning message. Well, I just found out that InnoDB can actually lose the savepoints! The overall transaction can still be rolled back successfully, but the savepoints themselves are completely gone. This only occurs after the warning: "Got error 153 from storage engine" happens twice in one transaction (if it only happens once, the savepoints still work) which requires that alternating savepoints be set 6 times. See the full report here: http://bugs.mysql.com/bug.php?id=38187.

In previous search benchmarks, I utilized random content generated with Drupal's devel module. In these latest benchmarks, I used an actual sanitized copy of the Drupal.org community website database, with email addresses and passwords removed. The first tests were intended to confirm that Xapian continues to perform well with large amounts of actual data. Additional tests were performed to measure the effect of various MySQL tunings and configurations. The following data was derived from several hundred benchmarks run on an Amazon AWS instance over the past week using the SearchBench module.

These tests confirm that Xapian continues to offer better search performance than Drupal's core search module. Contrary to popular belief, the data also shows that using the InnoDB storage engine for search tables significantly outperforms using the MyISAM storage engine for search tables, especially when your database server has sufficient RAM. The …

One of our developers ran into the strangest error a few days ago, which neither the MySQL manual nor Google searching could find any information on. When he called certain stored procedures in a certain order, he got "Error 153 returned from storage engine." It's been reduced to a very simple test case, and a bug report has been filed. Here's the test case:

DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
`a` int(11) NOT NULL auto_increment,
PRIMARY KEY  (`a`)
) ENGINE=InnoDB;

begin;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
show warnings;
rollback;

The very last "savepoint" generates the warning "Got error 153 from storage engine". Any other MySQL'ers use savepoints and …

There's been a lot of rumors floating around internally at Yahoo that it's best to turn off some of your CPU cores when using Innodb, especially if you have a machine with > 4 cores.  At this point there's no question in my mind that Innodb doesn't perform much better when you double your cores from 4 to 8, but I really wanted to know if 8 actually performed worse. 

To test, I used a Dell 2950 with 6 drives and a simple mysqlslap test script.  There's basically no I/O going on here, just a small table in memory being queried a lot.  To be fair, I actually got this test from Venu.  I used maxcpu=4 in my grub.conf to limit the cpus (I also tested with tasksel and it seemed to have the same effect as maxcpu).

read more

There's been a lot of rumors floating around internally at Yahoo that it's best to turn off some of your CPU cores when using Innodb, especially if you have a machine with > 4 cores.  At this point there's no question in my mind that Innodb doesn't perform much better when you double your cores from 4 to 8, but I really wanted to know if 8 actually performed worse. 

To test, I used a Dell 2950 with 6 drives and a simple mysqlslap test script.  There's basically no I/O going on here, just a small table in memory being queried a lot.  To be fair, I actually got this test from Venu.  I used maxcpu=4 in my grub.conf to limit the cpus (I also tested with tasksel and it seemed to have the same effect as maxcpu).

read more