The latest from the MySQL community
Ideas, releases, practical guides, and perspectives from the people building with MySQL.
sysbench - Linux test bench. Easy as pie to test CPU, memory, threads, mysql, and disk performance.
Full description is available here: http://sysbench.sourceforge.net/docs/
install mysql, mysql-devel wget http://superb-west.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz tar xvzf sysbench*gz cd sysbench* ./configure && make install
mysql tests
This will run 10 separate consecutive mysql tests using an InnoDB table type, each with 100 mysql threads, doing a total of 1000 various SQL operations per test. Then it will print the total time they took to finish:
sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \\
--mysql-host='HOST' --mysql-table-engine=innodb prepare
time perl -e "foreach(1..10){print \`sysbench --max-requests=1000 --test=oltp \\
--mysql-user=USER …[Read more]
The question I often get is how far MySQL may fall behind and how to keep replication from lagging.
The lag you will see will vary a lot from application to the application and from load to load. Plus what is the most important within same application the lag will likely have spikes - most of applications would have typical lag within few milliseconds while there will be rare cases when replication lags behind several seconds or even longer.
There are multiple reasons why application falls behind and why we see those lag spikes:
Slave Server Load - MySQL Replication goes in single thread so it is really vulnerable to the server load. If you get 100 active queries running on the slave overloading it, slave thread will most likely will not get CPU or Disk resources it needs in order to keep going. So if you want to keep replication lag under control you need to keep Slave load under control and avoid load …
[Read more]I presented at ZendCon this week on performance MySQL coding techniques. The session was packed, and I got a bunch of great questions from the audience. I figured I would post the slide deck in both ODP and PDF formats. Feel free to download!
Welcome to the 66th edition of Log Buffer, the weekly review of database blogs, raking up the blogs like so many fallen leaves. Fall back, spring ahead… Remember all the fuss last spring about the change in Daylight Saving Time? Well, it’s back, at least if you neglected it the first time. Thanks to [...]
Dear MySQL and PHP users,
we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.
According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.
The second news is that we will strengthen our commitment into PHP …
[Read more]Dear MySQL and PHP users,
we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.
According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.
The second news is that we will strengthen our commitment into PHP …
[Read more]Earlier today I was building a test-case in which I wanted to put a lot of Unicode data into a database table. The problem is of-course that I don’t have a lot of data, just a small Excel input file.
So I made a Cartesian product with a couple of empty row generators:
It was interesting to see how fast the second join step was generating rows:
Yes, you are reading that correctly: 717 million rows processed in 165 seconds = 4.3 million rows per second.
For those of you that would love to try this on their own machine. Here is an exclusive present for the readers of this blog in the form of a 3.0.0-RC2 preview of 2007/10/12 (88MB zip file). We’ve been fixing bugs like crazy so it’s pretty stable for us, but it’s still a few weeks until we release RC2. Don’t do anything crazy with this drop! This is purely a …
[Read more]Dear MySQL and PHP users,
we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.
According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.
The second news is that we will strengthen our commitment into PHP …
[Read more]We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.
The main query which showed this problem was similar to this:
PLAIN TEXT SQL:
- SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1 AND t2.val = 5 LIMIT 1206,18;
- mysql> EXPLAIN SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1 AND t2.val = 5 LIMIT 1206,18\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE …