Simultaneous reading and writing has to be controlled carefully so that readers don’t see inconsistent results. MyISAM allows concurrent inserts and reads under some conditions, and it lets you “schedule” some operations to try to block as little as possible.

Before we look at MyISAM’s concurrency settings, it’s important to understand how MyISAM deletes and inserts rows. Delete operations don’t rearrange the entire table; they just mark rows as deleted, leaving “holes” in the table. MyISAM prefers to fill the holes if it can, reusing the spaces for inserted rows. If there are no holes, it appends new rows to the end of the table.

Even though MyISAM has table-level locks, it can append new rows concurrently with reads. It does this by stopping the reads at the last row that existed when they began. This avoids inconsistent reads.

However, it is much more difficult to provide consistent reads when something is changing the middle of the table. MVCC is the most popular way to solve this problem: it lets readers read old versions of data while writers create new versions.

MyISAM doesn’t support MVCC, so it doesn’t support concurrent inserts unless they go at the end of the table.

You can configure MyISAM’s concurrent insert behavior with the concurrent_insert variable, which can have the following values:

0 MyISAM allows no concurrent inserts; every insert locks the table exclusively.

1 This is the default value. MyISAM allows concurrent inserts, as long as there are no holes in the table.

2 This value is available in MySQL 5.0 and newer. It forces concurrent inserts to append to the end of the table, even when there are holes. If there are no threads reading from the table, MySQL will place the new rows in the holes. The table can become more fragmented than usual with this setting, so you may need to optimize your tables more frequently, depending on your workload.

You can also configure MySQL to delay some operations to a later time, when they can be combined for greater efficiency. For instance, you can delay index writes with the delay_key_write variable. This involves the familiar tradeoff: write the index right away (safe but expensive), or wait and hope the power doesn’t fail before the write happens (faster, but likely to cause massive index corruption in the event of a crash because the index file will be very out-of-date). You can also give INSERT, REPLACE, DELETE, and UPDATE queries lower priority than SELECT queries with the low_priority_updates option. This is equivalent to globally applying the LOW_PRIORITY modifier to UPDATE queries.

Finally, even though InnoDB’s scalability issues are more often talked about, MyISAM has also had problems with mutexes for a long time. In MySQL 4.0 and earlier, a global mutex protected any I/O to the key buffer, which caused scalability problems with multiple CPUs and multiple disks. MySQL 4.1’s key buffer code is improved and doesn’t have this problem anymore, but it still holds a mutex on each key buffer. This is an issue when a thread copies key blocks from the key buffer into its local storage, rather than reading from the disk. The disk bottleneck is gone, but there’s still a bottleneck when accessing data in the key buffer. You can sometimes work around this problem with multiple key buffers, but this approach isn’t always successful. For example, there’s no way to solve the problem when it involves only a single index. As a result, concurrent SELECT queries can perform significantly worse on multi-CPU machines than on a single-CPU machine, even when these are the only queries running.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

1xx — Positive Preliminary Reply
These status codes indicate that an action has started successfully, but the client expects another reply before it continues with a new command.
• 110 — Restart marker reply.
• 120 — Service ready in nnn minutes.
• 125 — Data connection already open; transfer starting.
• 150 — File status okay; about to open data connection.


2xx — Positive Completion Reply
An action has successfully completed. The client can execute a new command.
• 200 — Command okay.
• 202 — Command not implemented, superfluous at this site.
• 211 — System status, or system help reply.
• 212 — Directory status.
• 213 — File status.
• 214 — Help message.
• 215 — NAME system type, where NAME is an official system name from the list in the Assigned Numbers document.
• 220 — Service ready for new user.
• 221 — Service closing control connection. Logged out if appropriate.
• 225 — Data connection open; no transfer in progress.
• 226 — Closing data connection. Requested file action successful (for example, file transfer or file abort).
• 227 — Entering passive mode (h1,h2,h3,h4,p1,p2).
• 230 — User logged in, proceed.
• 250 — Requested file action okay, completed.
• 257 — “PATHNAME” created.


3xx — Positive Intermediate Reply
The command was successful, but the server needs additional information from the client to complete processing the request.
• 331 — User name okay, need password.
• 332 — Need account for login.
• 350 — Requested file action pending further information.


4xx — Transient Negative Completion Reply
The command was not successful, but the error is temporary. If the client retries the command, it may succeed.
• 421 — Service not available, closing control connection. This may be a reply to any command if the service knows it must shut down.
• 425 — Cannot open data connection.
• 426 — Connection closed; transfer aborted.
• 450 — Requested file action not taken. File unavailable (for example, file busy).
• 451 — Requested action aborted: Local error in processing.
• 452 — Requested action not taken. Insufficient storage space in system.


5xx — Permanent Negative Completion Reply
The command was not successful, and the error is permanent. If the client retries the command, it receives the same error.
• 500 — Syntax error, command unrecognized. This may include errors such as command line too long.
• 501 — Syntax error in parameters or arguments.
• 502 — Command not implemented.
• 503 — Bad sequence of commands.
• 504 — Command not implemented for that parameter.
• 530 — Not logged in.
• 532 — Need account for storing files.
• 550 — Requested action not taken. File unavailable (for example, file not found, no access).
• 551 — Requested action aborted: Page type unknown.
• 552 — Requested file action aborted. Exceeded storage allocation (for current directory or dataset).
• 553 — Requested action not taken. File name not allowed.

Source of Information : Wrox Professional IIS 7

• 0 comments »

1xx — Informational
These status codes indicate a provisional response. The client should be prepared to receive one or more 1xx responses before receiving a regular response.
• 100 — Continue.
• 101 — Switching protocols.


2xx — Success
This class of status codes indicates that the server successfully accepted the client request.
• 200 — OK. The client request has succeeded.
• 201 — Created.
• 202 — Accepted.
• 203 — Non-authoritative information.
• 204 — No content.
• 205 — Reset content.
• 206 — Partial content.


3xx — Redirection
The client browser must take more action to fulfill the request. For example, the browser may have to request a different page on the server or repeat the request by using a proxy server.
• 301 — Moved permanently.
• 302 — Object moved.
• 304 — Not modified.
• 307 — Temporary redirect.


4xx — Client Error
An error occurs, and the client appears to be at fault. For example, the client may request a page that does not exist, or the client may not provide valid authentication information.
• 400 — Bad request.
• 401 — Access denied. IIS defines a number of different 401 errors that indicate a more specific cause of the error. These specific error codes are displayed in the browser but are not displayed in the IIS log:
• 401.1 — Logon failed.
• 401.2 — Logon failed due to server configuration.
• 401.3 — Unauthorized due to ACL on resource.
• 401.4 — Authorization failed by filter.
• 401.5 — Authorization failed by ISAPI/CGI application.
• 401.7 — Access denied by URL authorization policy on the Web server. This error code is specific to IIS 6.0.
• 403 — Forbidden. IIS defines a number of different 403 errors that indicate a more specific cause of the error.
• 403.1 — Execute access forbidden.
• 403.2 — Read access forbidden.
• 403.3 — Write access forbidden.
• 403.4 — SSL required.
• 403.5 — SSL 128 required.
• 403.6 — IP address rejected.
• 403.7 — Client certificate required.
• 403.8 — Site access denied.
• 403.9 — Too many users.
• 403.10 — Invalid configuration.
• 403.11 — Password change.
• 403.12 — Mapper denied access.
• 403.13 — Client certificate revoked.
• 403.14 — Directory listing denied.
• 403.15 — Client Access Licenses exceeded.
• 403.16 — Client certificate is untrusted or invalid.
• 403.17 — Client certificate has expired or is not yet valid.
• 403.18 — Cannot execute requested URL in the current application pool. This error code is specific to IIS 6.0.
• 403.19 — Cannot execute CGIs for the client in this application pool. This error code is specific to IIS 6.0.
• 403.20 — Passport logon failed. This error code is specific to IIS 6.0.
• 404 — Not found.
• 404.0 — (None) — File or directory not found.
• 404.1 — Web site not accessible on the requested port.
• 404.2 — Web service extension lockdown policy prevents this request.
• 404.3 — MIME map policy prevents this request.
• 405 — HTTP verb used to access this page is not allowed (method not allowed).
• 406 — Client browser does not accept the MIME type of the requested page.
• 407 — Proxy authentication required.
• 412 — Precondition failed.
• 413 — Request entity too large.
• 414 — Request-URI too long.
• 415 — Unsupported media type.
• 416 — Requested range not satisfiable.
• 417 — Execution failed.
• 423 — Locked error.


5xx — Server Error
The server cannot complete the request because it encounters an error.
• 500 — Internal server error.
• 500.12 — Application is busy restarting on the Web server.
• 500.13 — Web server is too busy.
• 500.15 — Direct requests for Global.asa are not allowed.
• 500.16 — UNC authorization credentials incorrect. This error code is specific to IIS 6.0.
• 500.18—URL authorization store cannot be opened. This error code is specific to IIS 6.0.
• 500.100 — Internal ASP error.
• 501 — Header values specify a configuration that is not implemented.
• 502 — Web server received an invalid response while acting as a gateway or proxy.
• 502.1 — CGI application timeout.
• 502.2 — Error in CGI application.
• 503 — Service unavailable. This error code is specific to IIS 6.0.
• 504 — Gateway timeout.
• 505 — HTTP version not supported.

Source of Information : Wrox Professional IIS 7

• 0 comments »

Copying, compressing, and decompressing huge files (often across a network) are common tasks when administering MySQL, initializing servers, cloning slaves, and performing backups and recovery operations. The fastest and best ways to do these jobs are not always the most obvious, and the difference between good and bad methods can be significant. It’s common to begin with an uncompressed file, such as one server’s InnoDB tablespace and log files. You also want the file to be decompressed when you finish copying it to the destination, of course. The other common scenario is to begin with a compressed file, such as a backup image, and finish with a decompressed file. If you have limited network capacity, it’s usually a good idea to send the files across the network in compressed form. You might also need to do a secure transfer, so your data isn’t compromised; this is a common requirement for backup images.

Copying Files
The task, then, is to do the following efficiently:
1. (Optionally) compress the data.
2. Send it to another machine.
3. Decompress the data into its final destination.
4. Verify the files aren’t corrupted after copying.


A Naive Example
We begin with a naïve example of how to send an uncompressed file securely from one machine to another, compress it en route, and then decompress it. On the source server, which we call server1, we execute the following:

server1$ gzip -c /backup/mydb/mytable.MYD > mytable.MYD.gz
server1$ scp mytable.MYD.gz root@server2:/var/lib/myql/mydb/

And then, on server2:

server2$ gunzip /var/lib/mysql/mydb/mytable.MYD.gz

This is probably the simplest approach, but it’s not very efficient because it serializes the steps involved in compressing, copying, and decompressing the file. Each step also requires reads from and writes to disk, which is slow. Here’s what really happens during each of the above commands: the gzip performs both reads and writes on server1, the scp reads on server1 and writes on server2, and the gunzip reads and writes on server2.


A One-Step Method
It’s more efficient to compress and copy the file and then decompress it on the other end in one step. This time we use SSH, the secure protocol upon which SCP is based. Here’s the command we execute on server1:

server1$ gzip -c /backup/mydb/mytable.MYD ssh root@server2
"gunzip -c - > /var/lib/mysql/mydb/mytable.MYD"

This usually performs much better than the first method, because it significantly reduces disk I/O: the disk activity is reduced to reading on server1 and writing on server2. This lets the disk operate sequentially.

You can also use SSH’s built-in compression to do this, but we’ve shown you how to compress and decompress with pipes because they give you more flexibility. For example, if you didn’t want to decompress the file on the other end, you wouldn’t want to use SSH compression.

You can improve on this method by tweaking some options, such as adding –1 to make the gzip compression faster. This usually doesn’t lower the compression ratio much, but it can make it much faster, which is important. You can also use different compression algorithms. For example, if you want very high compression and don’t care about how long it takes, you can use bzip2 instead of gzip. If you want very fast compression, you can instead use an LZO-based archiver. The compressed data might be about 20% larger, but the compression will be around five times faster.


Avoiding Encryption Overhead
SSH isn’t the fastest way to transport data across the network, because it adds the overhead of encrypting and decrypting. If you don’t need encryption, you can just copy the “raw” bits over the network with netcat. You invoke this tool as nc for noninteractive operations, which is what we want.

Here’s an example. First, let’s start listening for the file on port 12345 (any unused port will do) on server2, and uncompress anything sent to that port to the desired data file:
server2$ nc -l -p 12345 gunzip -c - > /var/lib/mysql/mydb/mytable.MYD
On server1, we then start another instance of netcat, sending to the port on which
the destination is listening. The -q option tells netcat to close the connection after it
sees the end of the incoming file. This will cause the listening instance to close the
destination file and quit:

server1$ gzip -c - /var/lib/mysql/mydb/mytable.MYD nc -q 1 server2 12345

An even easier technique is to use tar, so filenames are sent across the wire, eliminating another source of errors and automatically writing the files to their correct locations. The z option tells tar to use gzip compression and decompression. Here’s the command to execute on server2:

server2$ nc -l -p 12345 tar xvzf -

And here’s the command for server1:

server1$ tar cvzf - /var/lib/mysql/mydb/mytable.MYD nc -q 1 server2 12345

You can assemble these commands into a single script that will compress and copy lots of files into the network connection efficiently, then decompress them on the other side.


Other Options
Another option is rsync. rsync is convenient because it makes it easy to mirror the source and destination and because it can restart interrupted file transfers, but it doesn’t tend to work as well when its binary difference algorithm can’t be put to good use. You might consider using it for cases where you know most of the file doesn’t need to be sent—for example, for finishing an aborted nc copy operation.

You should experiment with file copying when you’re not in a crisis situation, because it will take a little trial and error to discover the fastest method. Which method performs best will depend on your system. The biggest factors are how many disk drives, network cards, and CPUs you have, and how fast they are relative to each other. It’s a good idea to monitor vmstat -n 5 to see whether the disk or the CPU is the speed bottleneck.

If you have idle CPUs, you can probably speed up the process by running several copy operations in parallel. Conversely, if the CPU is the bottleneck and you have lots of disk and network capacity, omit the compression. As with dumping and restoring, it’s often a good idea to do these operations in parallel for speed. Again, monitor your servers’ performance to see if you have unused capacity. Trying to over-parallelize may just slow things down.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

Many database products can cache query execution plans, so the server can skip the SQL parsing and optimization stages for repeated queries. MySQL can do this in some circumstances, but it also has a different type of cache (known as the query cache) that stores complete result sets for SELECT statements.

The MySQL query cache holds the exact bits that a completed query returned to the client. When a query cache hit occurs, the server can simply return the stored results immediately, skipping the parsing, optimization, and execution steps.
The query cache keeps track of which tables a query uses, and if any of those tables changes, it invalidates the cache entry. This coarse invalidation policy may seem inefficient—because the changes made to the tables might not affect the results stored in the cache—but it’s a simple approach with low overhead, which is important on a busy system.

The query cache is designed to be completely transparent to the application. The application does not need to know whether MySQL returned data from the cache or actually executed the query. The result should be the same either way. In other words, the query cache doesn’t change semantics; the server appears to behave the same way with it enabled or disabled.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

MySQL is not necessarily the solution for every need. It’s often much better to do some work completely outside MySQL, even if MySQL can theoretically do what you want.

One of the most obvious examples is storing data in a traditional filesystem instead of in tables. Image files are the classic case: you can put them into a BLOB column, but this is rarely a good idea. The usual practice is to store images or other large binary files on the filesystem and store the filenames inside MySQL; the application can then retrieve the files from outside of MySQL. In a web application, you accomplish this by putting the filename in the element’s src attribute.

Full-text searching is something else that’s best handled outside of MySQL—MySQL doesn’t perform these searches as well as Lucene or Sphinx. The NDB API can also be useful for certain tasks. For instance, although MySQL’s NDB Cluster storage engine isn’t (yet) well suited for storing all of a highperformance web application’s data, it’s possible to use the NDB API directly for storing web site session data or user registration information. You can learn more about the NDB API at http://dev.mysql.com/doc/ndbapi/en/index.html. There’s also an NDB module for Apache, mod_ndb, which you can download at http://code.google.com/p/mod-ndb/.

Finally, for some operations—such as graph relationships and tree traversals—a relational database just isn’t the right paradigm. MySQL isn’t good for distributed data processing, because it lacks parallel query execution capabilities. You’ll probably want to use other tools for this purpose (possibly in combination with MySQL).

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »