Hi all,

I was asked this question in an e-mail — feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here’s the question:

Basically when we open more than one connection- we’re opening 5, and we do processing in mysql (innodb tables) the server cpu and memory max out and the processing grinds to a halt. One connection at a time is able to run well- even with lots of data. But once we try to process data under 5 concurrent connections, mysql gets bogged down to the point where it’s barely usable.

I’ve researched this and found a number of opinions, including whether this is just poor performance due to where the we are in the 5.1 life cycle (i.e. energies have gone into fixing big bugs not …

I downloaded Maatkit 1877 and installed it on a data archive server, then did a parallel dump of the data. I also did a regular dump of the same data using mysqldump. This isn’t a benchmark or even a real comparison, just me playing with Maatkit to get a feel for it and see how it can make my life easier. First, some stats about the database:

mysql> select table_name, table_rows from information_schema.tables where table_schema = 'somedb';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| table1 | 24758808 |
| table2 | 33713412 |
| table3 | 221633443 |
| table4 | 56869734 |
| table5 | 14017081 |
| table6 | 77559278 |
| table7 | 48453337 |
+--------+------------+

mysql> call database_size_profile('somedb')\G
*************************** 1. row ***************************
…

Just a quick note to say we have reached the production stage of the book project. Production is the process of transforming our OpenOffice.org files into the final page layout using a professional typesetting program. As you can probably guess, this is later than we would have wished. This also means we won’t have the book for sale at the upcoming MySQL Conference and Expo. We will have a display copy at the O’Reilly booth at the conference, and you will be able to pre-order the book at a discount at that booth.

Indeed, this is usually a big no-no. But I have a customer where this is used, validly. Except it wasn't quite right.

Information from branches is replicated back to HQ, and since a slave can't have multiple masters, they have one slave instance for each branch. Each branch uses its own database names and the mysql system db is not replicated, so no conflict resolution is necessary (which would be the other problem with multiple masters).
Log in to any instance, and you will see all databases. And yes you can access them. But in this case they didn't always deliver the correct results.... lots of missing rows, generally. Cause? The instance configs didn't contain the --external-locking option. Added that in, and now it's working fine.

Note that this is unique to MyISAM, don't ever ever try this with InnoDB or any other engine!!
Basically, the external locking option makes MySQL server use filesystem locks …

Today I have released Q4M version 0.3. Changes from the previous are as follows.

Support for message relaying

Messages submitted to Q4M tables can be forwarded to another Q4M table on a different MySQL server. The transfer is trustable, there would be no message loss or duplications even on a server or a network failure. It is also possible to build a message broker above the API used for relaying.

Prioritized subscription to multiple tables

Clients can now subscribe to more than one table at once. The subscription priority can be specified, thus it is able to create a priority queue using multiple tables.

For more information, please refer to the Q4M homepage.

Yea, yea, yea... best practices.I spent about a week troubleshooting issues on a MySQL 5.1 replication issue where certain transactions causing duplicate key on index errors stopped replication. Easy enough to fix, right? mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> start slave;Hmm... nice until it happens almost every minute...So, after reading every link I could find on the internet,

Working on performance optimization project (not directly MySQL related) we did a test - creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back.

The performance difference was huge - we could read single file at 80MB/sec while fragmented files only deliver about 2MB/sec - this is a massive difference.

The test was done on EXT3 and it looks like it does not do very good job preventing file fragmentation for large amount of growing files.

It would be interesting to see how other filesystems deal with this problem, for example XFS with delayed allocation may be doing better job.

I also would like to repeat the test with MySQL MyISAM tables and see how bad the difference would be for MySQL but I would expect something along those lines.

Interesting enough it should not be …

North Bridge, a leading Venture Capital firm has launched a survey on the Future of Open Source. Results from the survey will be announced at a panel session at InfoWorld's OSBC conference in San Francisco March 25-26. The session is hosted by Michael Skok of North Bridge and panelists include Marten Mickos from MySQL, John Roberts from SugarCRM and Mark Shuttleworth from Canonical among others.... READ MORE

MySQL being acquired by Sun has of course put a lot of focus on the success of MySQL so far, but it is also interesting to think what success will mean as part of Sun too. In this light a recent interview of MÃ¥rten by Business Review Online about the acquisition was inspiring. (As words from MÃ¥rten usually are!)

"It's a marathon, not a sprint," he said. "If a 15-year-old downloads MySQL now, when do we get our money? In about 15 years' time when he is head of IT at a company and he loves MySQL. But in many cases it will happen sooner than that."

"In open source we say fail fast, scale fast. Many web 2.0 ideas will fail, but when Google or Facebook [two of MySQL's biggest customers] get it right they suddenly need to scale like crazy," Mickos said. "Open source is the only model where they can scale fast on exactly the …

You can hack the MySQL server in three ways:

• Changing the server code. This is straightforward. Download the code, and make the changes you need. If you need the change only for a limited period, this is enough. If the change must be persistent, then you should try to make it accepted. The Code Contribution Program may be for you.
• Creating a plugin or a UDF. Using the plugin interface or the UDF API you can extend the server in many ways. UDFs require that you (and the user) be able to compile the additional code. Plugins require compiling code only from the author.
• Mixing the existing hooks. This means being creative bu stretching the intended …