I recently helped to analyze a performance issue with one of our MySQL customers, which highlighted the need to be cautious when sizing the query cache, and to validate whether it works well with your workload. The customer was evaluating a new 2-socket, 8 core, X64 system running Solaris, as a replication slave in an environment consisting of older systems running Linux. When they introduced the new slave, they found that it was unable to keep up with its replication load during their peak periods, and it took hours to catch up once the traffic dropped. The older systems running Linux had no trouble keeping up, so the obvious assumption was that there was something wrong with the new system, with Solaris, or with ZFS. MySQL Query Cache Sizing : Performance Profiles
How FriendFeed uses MySQL to store schema-less data - Bret Taylo Background We use MySQL for storing all of the data in FriendFeed . Our database has grown a lot as our user base has grown. We now store over 250 million entries and a bunch of other data, from comments and "likes" to friend lists. As our database has grown, we have tried to iteratively deal with the scaling issues that come with rapid growth. We did the typical things, like using read slaves and memcache to increase read throughput and sharding our database to improve write throughput.
MySQL and Virtualization
Cluster: Two server setup
Samuel Huckins: Exploring the power of the mysql client One of the things that surprised me at the MySQL Bootcamp was the focus on using the mysql CLI client. I used to only use this, since the GUI-based Query Browser was rather unstable on Linux. I just got tired of it crashing and losing my queries after a while. But once I started using Vista and gave it another try, it seemed to be rock-solid. In the class, however, we only used the command-line client .
Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump: shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out. -- Grab the users with global permissions, -- with permissions to the databases you want, -- and tables/stored procedures in it. mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.user u WHERE u.Select_priv='Y' UNION SELECT u.* FROM mysql.user u, mysql.db d WHERE d.Db IN('db1', 'db2', ...) Backing up permissions for individual databases
Monitoring MySQL Memory is a rather critical task because you can’t limit MySQL’s usage of physical memory resources. Improperly configured servers running MySQL can crash because you don’t understand memory usage. MySQL uses memory in a number of different ways. Using the Oracle analogy, you can divide the mysqld memory usage into main areas of: Are you monitoring RSS & VSZ?
SQLFairy - The SQL Translator
Enforcing Foreign Keys Programmatically in MySQL
Even the most well-designed database, functioning on its own or as part of an application, needs to be maintained. This hour is part of a 3-hour set of administrative lessons that will give you pointers for optimizing and tuning your database so that you get the most bang for your buck. Think of your database like a garden -- basic water and sunlight will allow it to grow just fine, but a little extra plant food will help it flourish. Hour 20: Optimizing and Tuning Your Database Optimizing and Tuning Your MySQL Database MySQL Startup Options
Building a Data Warehouse with MySQL and Perl by Sam Tregar 04/19/2007 Introduction Data warehousing is the practice of building databases specifically to support complex ad-hoc querying by end-users. It isn't a new concept, but it doesn't seem to be a common technique in the Perl database community.
by Kris Buytaert 02/16/2006 Today's enterprise depends on the availability of mail and web services. Failure is never far away, whether it be a hardware failure or a human error. We have to try to make an infrastructure as highly available as possible. When building highly available clusters, people often choose one extra physical machine per service, creating an A-B, fail-over schema. Building a High-Availability MySQL Cluster
AB :: MySQL Administrator Best Practices
mytop - a top clone for MySQL About mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI , and Term::ReadKey installed.
184.108.40.206 Query Cache Confi
A Practical Look at the MySQL Query Cache
MySQL :: A Practical Look at the MySQL Query Cache