The latest from the MySQL community

Part 1 covered Network Edition backup features, today’s snips apply to all editions.

First among the lesser known additions: We recently provided the possibility for a nice performance boost to some environments by adding the ability to turn on batched indexing in ZCS 5.0.3 (you can even fine tune it at the localconfig, COS, and account level). We’re not talking about when you re-index an entire account here, this is a change to the index-as-received model; now new items can sit in a ‘queue’ (really a ‘indexing deferred’ flag on the mail_items table of the pertaining mboxgroup database in MySQL) to run all at once when it reaches the zimbraBatchedIndexingSize threshold, saving you from all the tiny disk thrashing. It might not be …

Watch for Zend, MySQL, Asterisk, PHP and Apache conferences coming to a city near you! READ MORE

I met GNU for the first time fifteen years ago. I was working as a consultant in support of a criminal investigation, and as part of my duties I had to analyze a database hosted on a SCO server.
As often happens, the database was proprietary, and it did not include any facility to analyze data. I needed to build an application to explore the data thoroughly.
Most of my working libraries were written in C, which I used in other operating systems. So I contacted SCO and asked to buy a C compiler. I was told that it would cost me quite a lot (I was prepared for that, although not for the price they told me, but since I was going to expense it, I would not care), and that it would take one month to get the software. I did not have one month at my disposal. I needed to nail down the evidence for the investigation immediately. So I started asking around. I was doing some side work for …

I've made a patch that makes EXPLAIN show conditions that are attached to various points of the query plan. If you run EXPLAIN CONDITIONS (or EXPLAIN CONDS) statement, the output besides the usual EXPLAIN resultset will have a second resultset that will show

• Conditions attached to individual tables
• Conditions that are applied before/after join buffering
• Table and index conditions that were pushed down into the storage engine
• ... and so forth (I believe it prints out all possible conditions that are there)

It looks like this:

mysql> explain conds select * from City, Country where City.Country=Country.Code and City.Name like 'C%' and Country.Continent='Asia' and Country.Population>5000000;
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
| id | select_type | table   | type | …

Tuning MySQL Performance with MySQLTuner

MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

If there is one thing that I terribly miss after moving to this country, that would be the variety of Mangoes in India. I've been asked at the United States Customs if I'm importing any mangoes. The fruit's flavor, fragrance and color is just great so I don't blame them at all. There is even International Mango Festival conducted every year since 1987 in Delhi. The festival features more than 550 varieties for visitors to view and taste.

I can talk at lengths about the fruit Mango but this entry is about Mango*. So what is it ?

Tuning MySQL Performance with MySQLTuner

MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

Some months ago I helped out in another project in which they had some performance problems. They had a very big table and the index of the table was bigger than the table itself. As every change in the table causes MySQL to recalculate/reload the index, this can take some seconds for such big tables.
So, I thought it would be a good idea to split that big table into very small ones. This should reduce the overhead of reloading big indices and instead reload only very small parts. And the next thought was: Is it possible to use the "new" MySQL-Partitioning for that?
Continue reading "Is MySQL-partitioning useful for very big real-life-problems?"

Lets start a mysqld instance on a host machine :

bash-3.1$ ./mysql/libexec/mysqld --defaults-file=./mysql/etc/my.cnf
080902 11:55:50 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
080902 11:55:50 [Note] Event Scheduler: Loaded 0 events
080902 11:55:50 [Note] ./mysql/libexec/mysqld: ready for connections.
Version: '5.1.24-rc'  socket: '/tmp/mysql_9090.sock'  port: 9090  Source distribution

As you can see I have used 'defaults-file' option to specify the options file to the
server. This is to avoid the conflict if there are more mysqld instances running on the
same machine.
So, MySQL server is up and running and listening to port 9090 as specified in my.cnf file.

Note : Default port for mysqld is 3306.

Now, we can try connecting to this server both locally as well as remotely.

…

Lets start a mysqld instance on a host machine :

bash-3.1$ ./mysql/libexec/mysqld --defaults-file=./mysql/etc/my.cnf
080902 11:55:50 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
080902 11:55:50 [Note] Event Scheduler: Loaded 0 events
080902 11:55:50 [Note] ./mysql/libexec/mysqld: ready for connections.
Version: '5.1.24-rc'  socket: '/tmp/mysql_9090.sock'  port: 9090  Source distribution

As you can see I have used 'defaults-file' option to specify the options file to the
server. This is to avoid the conflict if there are more mysqld instances running on the
same machine.
So, MySQL server is up and running and listening to port 9090 as specified in my.cnf file.

Note : Default port for mysqld is 3306.

Now, we can try connecting to this server both locally as well as remotely.

…