This is short chapter which gives some clue what to do with concurrent transactions.

Chapter 7. Concurrent transactions.

Yet another frequent example of similar problem is "Lock wait timeout
exceeded" error while you use InnoDB tables. Most often running query
SHOW ENGINE INNODB STATUS is enough to find the problem, because it
will show last transactions. But output of this command does not
contain information about all queries in the same transaction, but only
about last one. What to do if SHOW ENGINE INNODB STATUS doesn't provide
all information?

...

 Rest of the chapter is here

So lets imagine you have the server handling 100.000 user accounts. You can see the CPU,IO and Network usage is below 10% of capacity - does it mean you can count on server being able to
handle 1.000.000 of accounts ? Not really, and there are few reasons why, I'll name most important of them:

Contention - This is probably the most obvious one. MySQL (and systems in general) do not scale perfectly with numbers of CPUs and number of concurrent requests. Reduced efficiency of CPU cache, Mutex contention and database lock contention all come here. Some of them are preventable and can be reduced by code changes, such as there have been a lot of advanced in scalability of MySQL by improving locking code design, others, such as row level locks would require application changes to allow more concurrent process. The scalability numbers depend a lot on the system scale, software and workload.

Data size …

Hey DBAs! Are you seeking more efficient ways of shifting through your data to aid your business operations? Two popular Business Intelligence products have community Open Source software are Pentaho and JasperSoft. And both work with MySQL.

Both are easy to download and install. Both will use a JDBC connector to connect to MySQL. But how easy are the two to configure and run a simple report against a running instance of MySQL?


Setting up a JDBC connection with JasperSoft or Pentaho is pretty much like using any other JDBC connection.
The …

I am by now a few days late, but I meant to point out once the milestone had been reached:

MySQL Bug #11660, “Expose either SQLState, mysql_error() or other diagnostics in stored procedures” was opened on June 30, 2005. It has now been open for more than five years with no fix in the server, no fix in sight, and no real workaround. This makes it very difficult to recommend the use of MySQL stored procedures for most serious applications, as it is somewhere between very cumbersome and impossible to handle errors properly. How can something so important go so long without a fix?

…

In facebook stream you’ll see the time period at the bottom of the stream. For example: 4 minutes ago, 2 days ago, 3 weeks ago…. In our recent project we have to show similar time fashion for our application’s activity stream. So I write a function to retrieve the time duration. In our mysql database, [...]

…

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27:

Binaries

MySQL binaries are found in /usr/bin.

$ ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root  314568 Feb 16 17:45 /usr/bin/mysql
-rwxr-xr-x 1 root root  110776 Feb 16 14:39 /usr/bin/mysqlaccess
-rwxr-xr-x 1 root root   35144 Feb 16 17:45 /usr/bin/mysqladmin
-rwxr-xr-x 1 root root  112944 Feb 16 …

This year we're participating in OSCON as a Sponsor and organizing some BOFs. I will be on the conference 21 and 22 if you're interested to chat.
Here is the list of currently scheduled BOFs which I'll be hosting:
Running Databases on Flash Storage
Sphinx Search 2010
XtraDB, XtraBackup, Maatkit, Percona Server
See you there.

Entry posted by peter | No comment

Add to: …

Many people use the MySQL MEMORY storage engine for applications where they don’t need their data to be highly available or even survive restarts. Where this works best is for applications that are dominated by reads and (obviously) where the data doesn’t need to survive any kinds of problems (or where it can be recovered from another source). Typical applications are :

• Caching of small tables to reduce the latency of database look-ups.
• Simple session management (i.e. with no analytics or reporting on the session data).
• Buffering of multiple updates for batched inserts into tables managed by other MySQL storage engines.  (Note requires additional custom application development).

While great for some applications (at least initially), as the application requirements start …

There is often confusion as to how it can be claimed that MySQL Cluster delivers in-memory performance while also providing durability (the “D” in ACID). This post explains how that can be achieved as well as how to mix and match scalability, High Availability and Durability.

MySQL Cluster deployment options

As an aside, the user can specify specific MySQL Cluster tables or columns to be stored on disk rather than in memory – this is a solution for extra capacity but you don’t need to take this performance hit just to have the data persisted to disk. This post focuses on the in-memory approach.

There is a great deal of flexibility in how you deploy MySQL Cluster with in-memory data – allowing the user to decide which features they want to make use of.

The simplest (and least common) topology …

Back in 2006 I was a member of the standards and guidelines workgroup in Joomla, and during my involvement there I produced the Joomla 1.5 database schema as a DB Designer EER diagram, so people could view the database in a visual way. I recently also set up the Joomla 1.6 database schema, this time using MySQL Workbench which is a better tool for the job, and also GPL and available on multiple platforms. During my work with setting up the 1.6 schema, I came across lots of oddities and lacks of normalization, and a severe lack of naming conventions and guidelines became obvious.

Improvement suggestions

I have listed a few of these below, and please add comments to this post if you find some of your own or you disagree with me, and …