We have seen a few instances where upgrades of MySQL 5.1 to 5.5 have resulted in poor performance when using default values. After investigation it would appear that the main culprit appears to be the innodb_thread_concurrency setting.

In MySQL 5.1 the default was 8, while in MySQL 5.5, the default was changed to 0 (unlimited).

So what does innodb_thread_concurrency do and why would it cause performance issues?

The value given to innodb_thread_concurrency sets the number of simultaneous threads the InnoDB engine will create.  If there are more jobs waiting to be processed than there are available thread slots, these jobs will be made to wait until a thread slot becomes free.  So on the face of it, making this unlimited sounds like a good idea - so you won't have any jobs waiting on thread slots.

In an ideal world, unlimited is good.  In the real world, unlimited doesn't exist.  CPUs only have …

MySQL is needlessly slow at accepting new connections. People usually work around that by having various sorts of connection pools, but there’s always a scale at which connection pools are not feasible. Sometimes connection avalanches come unexpected, and even if MySQL would have no trouble dealing with queries, it will have problems letting clients in. Something has to be done about it.

Lots of these problems have been low hanging fruits for years – it ‘was not detected’ by benchmarks because everyone who benchmarks MySQL would know that persistent connections are much faster and therefore wouldn’t look at connection speeds anymore.

Usually people attribute most of slowness to the LOCK_thread_count mutex – they are only partially right. This mutex does not just handle the counter of active running connections, but pretty much every operation that deals with increase or decrease of threads (thread cache, active thread …

Scalability is about application, architecture and infrastructure design, and careful management of server components.

1. Object Relational Mappers

ORMs are popular among developers but not among performance experts.  Why is that?  Primarily these two engineers experience a web application from entirely different perspectives.  One is building functionality, delivering features, and results are measured on fitting business requirements.  Performance and scalability are often low priorities at this stage.  ORMs allow developers to be much more productive, abstracting away the SQL difficulties of interacting with the backend datastore, and allowing them to concentrate on building the features and functionality.

On the performance side the picture is a bit different.  By leaving SQL query writing to an ORM, you are faced with complex queries that the database cannot optimize …

Hanging out in #mysql on freenode the other day, I overheard someone saying that the reason to use connection pools with MySQL is because JDBC connections are expensive to create. That is true out of the box, but mostly because the out of the box behavior of MySQL's JDBC driver is to be standards-compliant. If you know that your DBA and your developers aren't doing crazy things with the database (changing configurations without letting the developers know, going around the "standard" API calls to start/end transactions, etc), then you can get to the point where connection setup is no slower than any other API. Does this mean you shouldn't use a connection pool? NO! (more on this next week).

Here's an iterative overview of the changes made in configuration, and how they affect what queries the driver does on initialization.

First, asking the driver to connect with default configuration results in the following statements being …

I use snmptrapd to catch SNMP traps and then put them into a MySQL database using a pretty generic trap handler. This gives me the opportunity to generate such useful information as: > SELECT HOUR(time) AS "hour", COUNT(*) AS "bounces" FROM snmptraps WHERE hostname = 'XXX' AND time > '2011-07-25' AND trap_oid = 'SNMPv2-MIB::snmpTrapOID.0 IF-MIB::linkDown' […]

Percona Live MySQL Conference and Expo event page, and Baron's announcement of the conference.

Dates to keep in mind:
September 1st - call for speakers opens
November 1st - call for speakers closes
Tuesday April 10th - Thursday April 12th

This week we interview Doron Levari, CEO and co-founder of ScaleBase. ScaleBase transparently scales your database, so you can have many shards without having to code sharding in your application. The sharding is completely transparent to the application. This can be combined with regular replication to have automatic use of a standby if a shard's primary database fails.

…

We’ve written about how a bad economy is indeed good for open source software. We’ve also recognized that with open source software’s maturity and place at the enterprise software table, a bad economy can be a double-edged sword for open source since the failure or fade of large enterprise customers, say big banks, hurts open source vendors right alongside traditional software providers.

What is interesting is that after a couple of years of economic rebuilding, we’ve seen recently how open source is being driven by innovation, particularly in cloud computing, …

Posted recently on the maria-developers mailing list, by Igor Babaev, Principal MariaDB developer at Monty Program is some interesting preliminary results for MariaDB 5.3.0 benchmarked against the DBT-3 benchmarking program.

DBT-3 is a benchmark to test a decision support workload, with a suite of business-oriented queries and concurrent data modifications.

Read Igor’s discoveries, which he ran on a laptop with 4 cores (multi-threaded = 8 cores in total), 8GB RAM and SSD on SuSE, and as Igor says, enjoy his numbers. It is a repeatable benchmark with all settings included. We’d like to see discussion continued on the mailing list!

Resources

In April of 2011, InnoDB team published the early access of NoSQL to InnoDB with memcached, plus several new features as part of MySQL 5.6.2 milestone release. This week, we announced additional early access to new InnoDB features for the community to test, and provide feedback.

There are two release packages from InnoDB team on MySQL Labs: InnoDB full-text search, and InnoDB new features.

InnoDB Full-Text Search

MySQL 5.5 makes InnoDB the default storage engine, so everyone can benefit from ACID-compliant transactions, referential integrity, crash recovery.  However, some users need InnoDB to have built-in full-text search, similar …

Note: this article was originally published on http://blogs.innodb.com on July 27, 2011 by Vinay.

Long awaited InnoDB support for full-text index is available in InnoDB lab release. This post talks about the performance of InnoDB’s full-text support over MyISAM’s full-text support. (There are multiple aspect of full-text index performance, the most important ones are full-text index creation time and query execution time). We are not focusing on performance with “DML” (expect some simple insert on loaded data) and “other supported character sets” (numbers are based on latin1 char-set data).

Numbers are encouraging in terms of ‘create index’  time where InnoDB is comparable or faster than MyISAM. And query execution time are comparable with MyISAM. Over the time, we can expect optimization for query performance.

Please refer  …