Category: System Administration

Posted on

High Scalability – More Troubles with Caching

As a tasty pairing with Facebook And Site Failures Caused By Complex, Weakly Interacting, Layered Systems, is another excellent tale of caching gone wrong by Peter Zaitsev, in an exciting twin billing: Cache Miss Storm and More on dangers of the caches. This is fascinating case where the cause turned out to be software upgrade that ran long because it had to be rolled back. During the long recovery time many of the cache entries timed out. When the database came back, slam, all the clients queried the database to repopulate the cache and bad things happened to the database. The solution was equally interesting:

So the immediate solution to bring the system up was surprisingly simple. We just had to get traffic on the system in stages allowing Memcache to be warmed up. There were no code which would allow to do it on application side so we did it on MySQL side instead. “SET GLOBAL max_connections=20” to limit number of connections to MySQL and so let application to err when it tries to put too much load on MySQL as MySQL load stabilizes increasing number of connections higher until you finally can serve all traffic without problems.

Peter also suggested a few other helpful strategies:

  • Watch frequently accessed cache items as well as cache items which take long to generate in case of cache miss
  • Optimize Queries
  • Use a Smarter Cache
  • Include Rollback in Maintenance Window
  • Know your Cold Cache Performance and Behavior
  • Have a way to increase traffic gradually
  • Consider Pre-Priming Caches

via more.

Posted on

MySQL does support preparing some DDL statements, However…

Bill Karwin gives some insight into some work arounds when creating functions, triggers and procedures using Zend Framework;

MySQL does support preparing some DDL statements, even in older versions. See http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
for lists of what statements can be prepared.

However, some DDL statements are still not supported as prepared statements, for example CREATE FUNCTION, CREATE TRIGGER, CREATE PROCEDURE.

DELIMITER is not supported as an executable statement at all, whether you prepare it or whether you do an immediate execute. Statements like DELIMITER, PAGER, SOURCE, CONNECT, and QUIT and others are builtins of the mysql command-line client. These commands are not recognized by the MySQL server.

You need to set the DELIMITER only if you’re running the CREATE FUNCTION statement in an SQL script. The default API for SQL statements does not support multiple statements per call. So you don’t have to delimit statements and you don’t have to change the delimiter.

So Nils’s solution should be the following:

1. Don’t worry about DELIMITER, you don’t need it.

2. You must DROP and CREATE in two separate statements.

3. Bypass the default ZF query method. Go directly to the
PDO::query() method when you execute a statement that isn’t preparable. You can access the PDO object using the getConnection() method of your ZF Db adapter:

$db->getConnection()->query( $drop_function_statement );
$db->getConnection()->query( $create_function_statement );

Regards,
Bill Karwin

Posted on

FastCopy – Moving / Copying files Lightning fast on Windows

Anyone that has been working on a Windows based system (Regardless of what version) groans everytime you have to copy or move many files from a drive to another…

I did however find this little gem that takes most of the pain out of it;

FastCopy is the Fastest Copy/Delete Software on Windows.

It supports UNICODE and over MAX_PATH 260 byte pathname files. It automatically selects different method according to whether Source and DestDir are in the same or different HDD.

  • Diff HDD Reading and writing are processed respectively in parallel by another thread.
  • Same HDD Reading are processed until the big buffer fills.

When the big buffer filled, writing are started and processed in bulk.Because Reading/Writing are processed with no OS cache at all, other applications aren’t easy to become heavy.

It can bring out Reading/Writing performance that is close to device limit.Include/Exclude Filter UNIX Wildcard style can be specified. It runs lightly, because MFC is not used. made by Win32 API and C Runtime only

You can modify this software, because all source codes have been opened to the public in the BSD license.

Editors note: And no it’s not an April Fools joke 🙂
Get it via FastCopy.

Posted on

Migrating MySQL latin1 to utf8 – In House Version

This entry is part [part not set] of 4 in the series Migrating MySQL latin1 to utf8

Use this method if at all possible as it will attempt to recover non-English latin1 characters (accents, umlauts) in your existing data.

  • Confirm your database is currently encoded in latin1.
  • Make a fresh backup (ideally using mysqlhotcopy se notes below)
  • Temporarily disable your cronjob so you don’t have anything trying to access the database.
  • The steps below require the ‘iconv’ utility to ensure proper UTF8 encoding. Type ‘iconv –version’ at the shell to check if it’s installed. If not, install it from your platform’s package manager (e.g. apt-get, yum, rpm).

Dump the database schema (use your own user + password + database):

mysqldump -Q -d -u root -p
--default-character-set=latin1 --skip-set-charset
old_database | sed 's/latin1/utf8/gi' > dump.schema.sql

Dump the database data (use your own user + password + database):

mysqldump -Q --insert-ignore -t -u root -p
--default-character-set=latin1 --skip-set-charset
old_database | iconv -c -f utf8 -t utf8 > dump.data.sql

Create a new UTF-8 database:

CREATE DATABASE newdb_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;

Import into the new database:

mysql -u root -p --default-character-set=utf8
newdb_utf8 < dump.schema.sql; mysql -u root -p --default-character-set=utf8 newdb_utf8 < dump.data.sql;
Notes:

Some helpfull hints

mysqlhotcopy:
Reference Here
Usage: (to local filesystem)

mysqlhotcopy -u backups -p`cat ~backups/.db.shadow`
--addtodest --noindices old_database ~backups/dbs/

Usage: (to SCP)

mysqlhotcopy -u backups -p`cat ~backups/.db.shadow`
--addtodest --noindices
--method='scp -c arcfour -C -2' old_database backups@remotehost:~backups/dbs/

Posted on

MySQL :: Managing Hierarchical Data in MySQL

Mike Hillyer wrote a very good article on Managing Hierarchical data in MySQL, defenitely worth a read;

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

via MySQL :: Managing Hierarchical Data in MySQL.

Posted on

Migrating MySQL latin1 to utf8 – Preparation

This entry is part [part not set] of 4 in the series Migrating MySQL latin1 to utf8

Before undertaking such migration the first step is a lesson in understanding more about how latin1 and utf8 work and interact in MySQL. latin1 in a common and historical character set used in MySQL. utf8 first available in MySQL Version 4.1 is an encoding supporting multiple bytes and is the system default in MySQL 5.0

via Migrating MySQL latin1 to utf8 – Preparation

Posted on

Jayson Minard: Yes, I Crashed the Site!

Jayson Minard wrote a very good article on upgrading a production site and what can go wrong and what we can learn from it.

Yesterday, I performed an upgrade to a third-party package used with Zend Developer Zone. It has an automated schema update system which silently performs actions on the database that had a large impact on ZDZ and related sites causing an outage. So, there are good lessons from my post-mortem that I would like to share with the community.

The Start of the Problem

First, let us look at the actual list of actions that started the issue:

1. The upgrade does a schema check on first load

2. The upgrade then corrects the schema to be valid for the new release (performing table changes via DDL)

3. The upgrade then may modify large amounts of existing data, or delete large amounts of old data

These schema and data updates can cause huge potential issues when the database and tables are used concurrently by the online site. First, the DDL changes will lock the affected tables. And for some storage engines (i.e. MyISAM in MySQL) the modifications or deletes will also cause table locks, and in other engines they could cause contention on locked rows, and in other engines cause things like rollback segments to overflow. The online site then waits behind the locks or contention causing threads in the web server to be held until no more threads are left to serve actual user requests. No more threads, no more site.

Learn from the problem; via Yes, I Crashed the Site!.

Posted on

How to Enable GodMode on Windows 7

Windows 7 interface (and desktop) is rather clean and minimalist. Of course, you can find everything you need in the famous and well-known Control Panel. However this solution won’t allow you to see and easily use all those nested settings included in the main options. Did you know that you can become a God? Well, at least on Windows 7!

Read more about it on WebTalk

Proudly powered by WordPress