Mysql

Tuning MySQL - On the Path. In the past two weeks, I have been called in for some near-emergency tuning of two rails applications that recently went into production.

One was for a state-level government agency, the other was for a startup that recently went into production and found performance problems as their app grew in popularity. In both cases, the first place I looked was the innodb table settings for mysql - and in both cases, I found things that could immediately help the application in question. I'm going to walk you through that thought process now, potentially teach you something about your own rails app, and hopefully improve the performance of your rails application.

Innodb - the background While this advice comes from two deployed rails applications, the advice I'm giving here applies to any application using mysql and the innodb storage engine. One of the coolest things about mysql is its ability to swap in different storage engines. Innodb - the knobs and dials. Choosing innodb_buffer_pool_size. November 3, 2007 by Peter Zaitsev39 Comments My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description.

Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough. You also may choose to set buffer pool as if your database size is already larger than amount of memory you have – so you do not forget to readjust it later. The third important memory consumer would be OS cache. Indexing the Web—It’s Not Just Google’s Business. Interface responsiveness is one of many details web developers must consider in their quest to deliver a good user experience.

An application that responds quickly enhances the user’s sense of control. In working to maximize application speed, though, it’s easy to look in the wrong places. We optimize images and try to reduce page sizes. We compare the performance of web server software, programming languages, frameworks, and hardware, even though the differences in those tools may be minimal. There’s another, often-overlooked element, however, that can affect performance more than almost anything else: database design. Web databases do much more than passively store information.

Does it really matter? Yes! Articles articles_categories categories comments users Imagine that our blog is relatively new. First, let’s do a simple query to find all the articles by a particular author, using his e-mail address as the search term. Not surprisingly, this query runs very quickly. 12.4.5.29 SHOW PROFILES Syntax. Unit Testing A Database. In a recent job interview I was asked the question regarding Unit Testing/Automated Testing of a Database?

An interesting question and indeed an interesting problem. I thought it was a good topic to describe what I’ve done in the past, and where I would go for a more complete testing environment given the opportunity of a entire XP project. This is the approach I have implemented successfully in the past. It’s not a complete solution, however at the time with the client it provided appropriate coverage. I don’t use a framework such as dbUnit to load data via XML, or specifically test data. Within this process an automated build test would first reset the database to a known set of data. This does lead to two paths necessary for creating a schema, but this can also be tested adequately in an automated way. I also split my application tests suites that use the sample data into two buckets. 1 Introduction to the MySQL Test Framework. Mysql-master-master - Project Hosting on Google Code. MMM 1.x [MMM for MySQL Wiki]

Introduction MMM (MySQL Master-Master Replication Manager) is set of flexible scripts to perform monitoring and management of MySQL Masrter-Master replication cluster configurations.

Details Generally, MMM offers MySQL monitoring and automatic failover for master-master pairs, it also has scripts for data backups, resynchronization between nodes etc. In two node master-master setup, MMM uses five IPs: single permanent IP for each node that is never changed, 2 _reader_ IPs (read-only) and 1 _writer_ IP (updates). Last three IPs are migrating between the nodes depending on node availability. Normally (no replication failures, no replication delay etc) active master has 2 IPs (reader and writer), standby master - 1 IP (reader).

Otimização de Consultas MySQL - Parte 01 - Thiago Ferreira. MySQL query optimization. Top 87 MySQL performance tips.