The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1

...

[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...

In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+

Be sure to add the right options to the [mysqld] section.

What I didn’t know until yesterday was that some programs read from …

People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware.

If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.

We define “large data set” as over 100 Gb, mostly because smaller data sets have an easier time with the available memory on a machine (even if it’s only 8 Gb) and backups are less intrusive — InnoDB Hot Backup and Xtrabackup are not really “hot” backups, they are “warm” backups, because there is load on the machine to copy the data files, and on large, active servers we have found that this load impacts query performance. As for how active a database is, we’ve found that equates to a peak production load of over 3,000 queries per second on a transactional …

If you need to create a big database into MySQL Cluster with:

• A lot of tables indexes, columns, and tables
• A lot of records
  

there are a few things to think about:

• If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
  CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
  This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
  
• Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
  
  • Each table you create will use one table object. …

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

Today’s special found in a 13GB MySQL server error log.

090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101071 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101051 search iterations)! Consider
InnoDB: increasing …

This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.

What can you find on the web?

A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material—the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.

So, what is swappiness?

Towards the end of the email thread quoted in the article, you’ll find this definition (sort of):

> I’ve read the source for where …

Sometimes you just have to laugh at the crazy things that can kill a good evening. I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.So it

UPDATED - I had to update this post after a conversation with RightScale founder and CTO Thorsten von Eicken and for Sun’s Open Cloud announcement, which are both now included below.

There has been some substantial technology and news regarding open source software in cloud computing lately. More proof that open source is reaching into nearly all aspects of enterprise and broader IT, and also reinforcement of the idea that open source software will continue to have a pervasive and disruptive impact on the way organizations of all shapes and sizes do their computing and deal with their data.

First up is RightScale, which as detailed by 451 colleague and Principal Analyst William Fellows, is up and running across the pond on Amazon’s EU EC2. As WiF reports, RightScale started with …

Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.

There was one question during the session that I was not able to answer myself, so I'm asking for your …

The main fixes in cmon 0.13 are:

• filtering of clusterlog
• better graphs interface with filtering
  
• fixed bug in load average graphs. Now the loadavg graphs works
• added replication role, socket when adding a mysql server to be monitored from the web interface.
  
• corrected bug with how often mysql_variables are collected
• fixed problems with graphs
• added images directory
• revamped graphs - now producing files in images directory,
• ajax support for Storage graphs
• divided helpers into smaller files

Upgrade from cmon 0.12

• Stop cmon 0.12 (e.g. killall cmon from the command line)
• Execute this sql script …