There's a relatively little known feature about Linux IO scheduling that has a pretty significant effect in large scale database deployments at least with MySQL that a recent article on MySQL Performance Blog prompted me to write about. This may have an effect on other databases and random I/O systems as well, but we've definitely seen it with MySQL 5.0 on RHEL 4 platform. I have not studied this on RHEL 5, and since the IO subsystem and the Completely Fair Queue scheduler that is default on RHEL kernels has received further tuning since, I can not say if it still exists.

Though I've heard YouTube discovered these same things, …

Properties:

Description:

This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL …

The MySQL User's conference will be held in less than a month from now!!!

This year there is quite a good number of sessions on adding your own functions and procedures, such as:

• 
  
• Advanced Stored Procedures
  
• A Tour of External Language Stored Procedures for MySQL
  
• Code Generators for MySQL Plugins and User Defined Functions (UDFs)
  
• …

We’ve been running into a problem with one client:

SELECT COUNT(*) FROM tbl;

takes 0.25 seconds on one db, and 0.06 seconds on another.

Consistently. That’s a fourfold difference.

There aren’t any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it’s a hardware difference.

The table fits into memory so it’s not a disk issue, and the only other difference among the hardware is that the slower machine has Solaris virtualization in place in the form of “containers” (cpu is the same, etc). Is this something that’s known to cause issues with speed? The “tbl” in question is an InnoDB table, if that means anything. Is there something like the “speed” of RAM?

Note that Sun has already been …

The SnapLogic.org site runs on open source software, including a lot of Python. The bottom of the stack is RedHat Linux, the web server is Apache, the database engine is MySQL, and the main applications are Mailman, Subversion, Trac, Wordpress, and a custom Django application.

All of these pieces play together pretty well, and over the past year we’ve only had a few minor problems to fix. But over the past couple of weeks, we’ve really been hitting Trac hard as we work through our upcoming release, and Trac performance was bad. Really Bad. Like, ‘30 seconds to open a ticket‘ bad.

It’s hard to say if this was a sudden performance drop because we crossed some magic threshold in database size, or if it was slowing getting worse and we really noticed it when we shifted from design to coding. In any case, there are only 1000 tickets in the system so we knew something was wrong.

We turned on the …

Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO
…

Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO
…

Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO
…

Recently we put together a consolidation benchmark to see how an open-source stack performs against the proprietary stack from Microsoft. Solaris, MySQL, and Sun Web Server running on the open-source UltraSPARC T2 processor were pitted against a Microsoft SW stack running on a 4-socket QC Xeon server. This benchmark highlights the continued trend to incorporate MySQL open-source databases and how it works under virtualization (Solaris Zones).

The Sun SPARC Enterprise T5220 (1.4 Ghz UltraSPARC T2 processor) and Solaris Containers managing a consolidation of Open-Source Software components (MySQL Database and Sun Java System Web Server) provided 2.4 times better performance than the HP DL580 system (four Xeon quad-core processors) and a major virtualization software, Microsoft Windows 2003 Server EE, Microsoft SQLserver database and Microsoft IIS webserver.

The Sun SPARC Enterprise T5220 using the MySQL database in Solaris zones is …

Recently we put together a consolidation benchmark to see how an open-source stack performs against the proprietary stack from Microsoft. Solaris, MySQL, and Sun Web Server running on the open-source UltraSPARC T2 processor were pitted against a Microsoft SW stack running on a 4-socket QC Xeon server. This benchmark highlights the continued trend to incorporate MySQL open-source databases and how it works under virtualization (Solaris Zones).

The Sun SPARC Enterprise T5220 (1.4 Ghz UltraSPARC T2 processor) and Solaris Containers managing a consolidation of Open-Source Software components (MySQL Database and Sun Java System Web Server) provided 2.4 times better performance than the HP DL580 system (four Xeon quad-core processors) and a major virtualization software, Microsoft Windows 2003 Server EE, Microsoft SQLserver database and Microsoft IIS webserver.

The Sun SPARC Enterprise T5220 using the MySQL database in Solaris zones is …