If MySQL can’t do what you need, one possibility is to extend its capabilities. If you’re interested in exploring any of these avenues further, there are good resources online, and there are books available on many of the topics. When we say “MySQL can’t do what you need,” we mean two things: MySQL can’t do it at all, or MySQL can do it, but in a slow or awkward way that’s not good enough. Either is a reason to look at extending MySQL. The good news is that MySQL is becoming more and more modular and general-purpose. For example, MySQL 5.1 has a lot of useful plug-in functionality; it even allows storage engines to be plug-ins, so you don’t need to compile them into the server.

Storage engines are a great way to extend MySQL for a special purpose. Brian Aker has written a skeleton storage engine and a series of articles and presentations about how to get started writing your own storage engine. This has formed the basis for several of the major third-party storage engines. Many companies are writing their own internal storage engines now, as you’ll see if you follow the MySQL internals mailing list. For example, Friendster uses a special storage engine for social graph operations, and we know of another company that built a custom engine for fuzzy searches. A simple custom storage engine isn’t very hard to write. You can also use a storage engine as an interface to another piece of software. A good example of this is the Sphinx storage engine, which interfaces with the Sphinx fulltext search software.

MySQL 5.1 also allows full-text search parser plug-ins, and you can write UDFs, which are great for CPU-intensive tasks that have to run in the server’s thread context and are too slow or clumsy in SQL. You can use them for administration, service integration, reading operating system information, calling web services, synchronizing data, and much more.

MySQL Proxy is another option that’s great if you want to add your own functionality to the MySQL protocol. And Paul McCullagh’s scalable blob-streaming infrastructure project (http://www.blobstreaming.org) opens up a range of new possibilities for storing large objects in MySQL.

Because MySQL is free, open source software, you can even hack the server itself if it doesn’t do what you need. We know of companies that have extended the server’s parser grammar, for example. Third parties have submitted many interesting MySQL extensions in the areas of performance profiling, scalability, and new features in recent years. The MySQL developers are very responsive and helpful when people want to extend MySQL. They’re available via the mailing list internals@lists.mysql.com (see http://lists.mysql.com to subscribe), MySQL forums, or the #mysql-dev IRC channel on freenode.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

Practice Makes Perfect
The designers of C# were able to draw upon the lessons learned from other OOPLs that preceded it. They borrowed the best features of C++, Java, Eiffel, and Smalltalk, and then added some capabilities and features not found in those languages. Conversely, the features that proved to be most troublesome in earlier languages were eliminated. As a result, C# is a powerful programming language that is also easy to learn. This is not to say that C# is a perfect language—no language is!—but simply that it has made some significant improvements over many of the languages that have preceded it.

C# Is Part of an Integrated Application Development Framework
The C# language is integrated into Microsoft’s .NET Framework—Microsoft’s powerful, comprehensive platform for developing applications and managing their runtime environment. The .NET Framework primarily supports the C#, C++, J#, and Visual Basic programming languages, but also provides a functionality called cross-language interoperability that allows objects created in different programming languages to work with each other. A core element of the .NET Framework is the common language runtime (CLR) that is responsible for the runtime management of any .NET Framework program. The CLR takes care of loading, running, and providing support services for the .NET Framework program.

The .NET Framework provides a high degree of interoperability between the languages it supports—C#, C++, Visual Basic, and JScript—through a Common Language Specification (CLS) that defines a common set of types and behaviors that every .NET language is guaranteed to recognize. The CLS allows developers to seamlessly integrate C# code with code written in any of the other .NET languages.

The .NET Framework also contains a vast collection of libraries called the .NET Framework Class Library (FCL) that provides almost all the common functionality needed to develop applications on the Windows platform. You’ll find that with the FCL a lot of programming work has already been done for you on topics ranging from file access to mathematical functions to database connectivity. The C# language and the .NET Framework provide one-stop shopping for all your programming needs. You can find out more about the .NET Framework here: http://msdn.microsoft.com/en-us/library/default.aspx.


C# Is Object-Oriented from the Ground Up
Before newer OOPLs such as C# and Java arrived on the scene, one of the most widely used OOPLs was C++, which is actually an object-oriented extension of the non-OOPL C. As such, C++ provides a lot of “back doors” that make it very easy to write decidedly “un-OO” code. In fact, many proficient C programmers transitioned to C++ as a better C without properly learning how to design an object-oriented application, and hence wound up using C++ for the most part as a procedural (non-OO) language. In contrast, C# was built from the ground up to be a purely OOPL.

• Primitive value types, such as int and double, inherit from the Object class.

• All the graphical user interface (GUI) building blocks—windows, buttons, text input fields, scroll bars, lists, menus, and so on—are objects.

• All functions are attached to objects and are known as methods—there can be no freefloating functions as there are in C/C++.

• Even the entry point for a C# program (now called the Main method) no longer stands alone, but is instead bundled within a class.

Because of this, C# lends itself particularly well to writing applications that uphold the object-oriented paradigm.


C# Is Free
One last valuable feature of C# that we’ll mention is that it’s free! You can download the C# compiler and all other libraries and utilities you’ll need from the Microsoft Developer Network (MSDN) web site at no cost.

Source of Information : Apress Beginning C Sharp 2008 Objects From Concept To Code

• 0 comments »

Let’s begin by considering how MyISAM performs I/O for its indexes. MyISAM normally flushes index changes to disk after every write. If you’re going to make many modifications to a table, however, it may be faster to batch these writes together.

One way to do this is with LOCK TABLES, which defers writes until you unlock the tables. This can be a valuable technique for improving performance, as it lets you control exactly which writes are deferred and when the writes are flushed to disk.

You can defer writes for precisely the statements you want. You can also defer index writes by using the delay_key_write variable. If you do this, modified key buffer blocks are not flushed until the table is closed.* The possible settings are as follows:

OFF
MyISAM flushes modified blocks in the key buffer (key cache) to disk after every write, unless the table is locked with LOCK TABLES.

ON
Delayed key writes are enabled, but only for tables created with the DELAY_KEY_
WRITE option.

ALL
All MyISAM tables use delayed key writes.

Delaying key writes can be helpful in some cases, but it doesn’t usually create a big performance boost. It’s most useful with smaller data sizes, when the key cache’s read hit ratio is good but the write hit ratio is bad. It also has quite a few drawbacks:

• If the server crashes and the blocks haven’t been flushed to disk, the index will be corrupt.

• If many writes are delayed, it’ll take longer for MySQL to close a table, because it will have to wait for the buffers to be flushed to disk. This can cause long table cache locks in MySQL 5.0.

• FLUSH TABLES can take a long time, for the reason just mentioned. This in turn can increase the time it takes to run FLUSH TABLES WITH READ LOCK for an LVM snapshot or other backup operation.

• Unflushed dirty blocks in the key buffer might not leave any room in the buffer for new blocks to be read from disk. Therefore, queries might stall while waiting for MyISAM to free up some space in the key buffer.

In addition to tuning MyISAM’s index I/O, you can configure how MyISAM tries to recover from corruption. The myisam_recover option controls how MyISAM looks for and repairs errors. You have to set this option in the configuration file or at the command line. You can view, but not change, the option’s value with this SQL statement (this is not a typo—the system variable has a different name from the corresponding command-line option):

mysql> SHOW VARIABLES LIKE 'myisam_recover_options';

Enabling this option instructs MySQL to check MyISAM tables for corruption when it opens them, and to repair them if problems are found. You can set the following values:

DEFAULT (or no setting)
MySQL will try to repair any table that is marked as having crashed or not marked as having been closed cleanly. The default setting performs no other actions upon recovery. In contrast to how most variables work, this DEFAULT value is not an instruction to reset the variable to its compiled-in value; it essentially means “no setting.”

BACKUP
Makes MySQL write a backup of the data file into a .BAK file, which you can examine afterward.

FORCE
Makes recovery continue even if more than one row will be lost from the .MYD file.

QUICK
Skips recovery unless there are delete blocks. These are blocks of deleted rows
are still occupying space and can be reused for future INSERT statements. This can be useful because MyISAM recovery can take a very long time on large tables.

You can use multiple settings, separated by commas. For example, BACKUP,FORCE will force recovery and create a backup.

We recommend that you enable this option, especially if you have just a few small
MyISAM tables. Running a server with corrupted MyISAM tables is dangerous, as they can sometimes cause more data corruption and even server crashes. However, if you have large tables, automatic recovery might be impractical: it causes the server to check and repair all MyISAM tables when they’re opened, which is inefficient. During this time, MySQL tends to block connections from performing any work. If you have a lot of MyISAM tables, it might be a good idea to use a less intrusive process that runs CHECK TABLES and REPAIR TABLES after startup. Either way, it is very important to check and repair the tables.

Enabling memory-mapped access to data files is another useful MyISAM tuning option. Memory mapping lets MyISAM access the .MYD files directly via the operating system’s page cache, avoiding costly system calls. In MySQL 5.1 and newer, you can enable memory mapping with the myisam_use_mmap option. Older versions of MySQL use memory mapping for compressed MyISAM tables only.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

Configuring MySQL to use memory correctly is vital to good performance. You’ll almost certainly need to customize MySQL’s memory usage for your needs. You can think of MySQL’s memory consumption as falling into two categories: the memory you can control, and the memory you can’t. You can’t control how much memory MySQL uses merely to run the server, parse queries, and manage its internals, but you have a lot of control over how much memory it uses for specific purposes. Making good use of the memory you can control is not hard, but it does require you to know what you’re configuring.

You can approach memory tuning in steps:
1. Determine the absolute upper limit of memory MySQL can possibly use.

2. Determine how much memory MySQL will use for per-connection needs, such as sort buffers and temporary tables.

3. Determine how much memory the operating system needs to run well. Include memory for other programs that run on the same machine, such as periodic jobs.

4. Assuming that it makes sense to do so, use the rest of the memory for MySQL’s caches, such as the InnoDB buffer pool.


How much memory can MySQL use?
There is a hard upper limit on the amount of memory that can possibly be available to MySQL on any given system. The starting point is the amount of physically installed memory. If your server doesn’t have it, MySQL can’t use it.

You also need to think about operating system or architecture limits, such as restrictions 32-bit operating systems place on how much memory a given process can address. Because MySQL runs in a single process with multiple threads, the amount of memory it can use overall may be severely limited by such restrictions—for example, 32-bit Linux kernels limit the amount of memory any one process can address to a value that is typically between 2.5 and 2.7 GB. Running out of address space is very dangerous and can cause MySQL to crash.

There are many other operating system-specific parameters and oddities that must be taken into account, including not just the per-process limits, but also stack sizes and other settings. The system’s glibc libraries can also impose limits per single allocation. For example, you might not be able to set innodb_buffer_pool larger than 2 GB if that’s all your glibc libraries support in a single allocation.

Even on 64-bit servers, some limitations still apply. For example, many of the buffers, such as the key buffer, are limited to 4 GB on a 64-bit server. Some of these restrictions are lifted in MySQL 5.1, and there will probably be more changes in the future because MySQL AB is actively working to make MySQL take advantage of more powerful hardware. The MySQL manual documents each variable’s maximum values.


Per-connection memory needs
MySQL needs a small amount of memory just to hold a connection (thread) open. It also requires a certain base amount of memory to execute any given query. You’ll need to set aside enough memory for MySQL to execute queries during peak load times. Otherwise, your queries will be starved for memory, and they will run poorly or fail.

It’s useful to know how much memory MySQL will consume during peak usage, but some usage patterns can unexpectedly consume a lot of memory, which makes this hard to predict. Prepared statements are one example, because you can have many of them open at once. Another example is the InnoDB table cache.

You don’t need to assume a worst-case scenario when trying to predict peak memory consumption. For example, if you configure MySQL to allow a maximum of 100 connections, it theoretically might be possible to simultaneously run large queries on all 100 connections, but in reality this probably won’t happen. For example, if you set myisam_sort_buffer_size to 256M, your worst-case usage is at least 25 GB, but this level of consumption is highly unlikely to actually occur.

Rather than calculating worst cases, a better approach is to watch your server under a real workload and see how much memory it uses, which you can see watching the process’s virtual memory size. In many Unix-like systems, this is reported in the VIRT column in top, or VSZ in ps. The next chapter has more information on how to monitor memory usage.


Reserving memory for the operating system
Just as with queries, you need to reserve enough memory for the operating system to do its work. The best indication that the operating system has enough memory is that it’s not actively swapping (paging) virtual memory to disk.

You should not need to reserve more than a gigabyte or two for the operating system, even for machines with a lot of memory. Add in some extra for safety, and add in some more if you’ll be running periodic memory-intensive jobs on the machine (such as backups). Don’t add any memory for the operating system’s caches, because they can be very large. The operating system will generally use any leftover memory for these caches, and we consider them separately from the operating system’s own needs in the following sections.


Allocating memory for caches
If the server is dedicated to MySQL, any memory you don’t reserve for the operating system or for query processing is available for caches.

MySQL needs more memory for caches than anything else. It uses caches to avoid disk access, which is orders of magnitude slower than accessing data in memory. The operating system may cache some data on MySQL’s behalf (especially for MyISAM), but MySQL needs lots of memory for itself too.

The following are the most important caches to consider for the majority of installations:
• The operating system caches for MyISAM data
• MyISAM key caches
• The InnoDB buffer pool
• The query cache

There are other caches, but they generally don’t use much memory. It is much easier to tune a server if you’re using only one storage engine. If you’re using only MyISAM tables, you can disable InnoDB completely, and if you’re using only InnoDB, you need to allocate only minimal resources for MyISAM (MySQL uses MyISAM tables internally for some operations). But if you’re using a mixture of storage engines, it can be very hard to figure out the right balance between them. The best approach we’ve found is to make an educated guess and then benchmark.

Source of Information : OReIlly High Performance MySQL Second Edition

• 0 comments »

The MySQL database system consists of several components.You should be familiar with what these components are and the purpose of each, so that you understand both the nature of the system you’re administering and the tools available to help you do your job. If you take the time to understand what you’re overseeing, your work will be much easier.
To that end, you should acquaint yourself with the following aspects of MySQL.

The MySQL server. The server, mysqld, is the hub of a MySQL installation; it performs all manipulation of databases and tables. On Unix, several related scripts are available to assist in server startup. mysqld_safe is a related program used to start the server, monitor it, and restart it in case it goes down.The mysql.server script is useful on versions of Unix that use run-level directories for starting system services. If you run multiple servers on a single host, mysqld_multi can help you manage them more easily.On Windows, you have the choice of running the server from the command line or as a Windows service.

The MySQL clients and utilities. Several MySQL programs are available to help you communicate with the server. For administrative tasks, some of the most important ones are listed here:

• mysql—An interactive program that enables you to send SQL statements to the server and to view the results.You can also use mysql to execute batch scripts (text files containing SQL statements).

• mysqladmin—An administrative program for performing tasks such as shutting down the server, checking its configuration, or monitoring its status if it appears not to be functioning properly.

• mysqldump and mysqlhotcopy—Tools for backing up your databases or copying databases to another server.

• mysqlcheck and myisamchk—Programs that help you perform table checking, analysis, and optimization, as well as repairs if tables become damaged. Mysqlcheck works with MyISAM tables and to some extent with tables for other storage engines. myisamchk is for use only with MyISAM tables.

The server’s language, SQL.You should be able to talk to the server in its own language. As a simple example, you might need to find out why a user’s privileges aren’t working the way you expect them to work. There is no substitute for being able to go in and communicate with the server directly, which you can do by using the mysql client program to issue SQL statements that let you examine the grant tables.

If you don’t know any SQL, be sure to acquire at least a basic understanding of it.A lack of SQL fluency will only hinder you in your administrative tasks, whereas the time you take to learn will be repaid many times over. A real mastery of SQL takes some time, but the basic skills can be attained quickly. For instruction in SQL and the use of the mysql command-line client.

The MySQL data directory. The data directory is where the server stores its databases and status files. It’s important to understand the structure and contents of the data directory so that you know how the server uses the filesystem to represent databases and tables, as well as where the server logs are located and what they contain.You should also know your options for managing allocation of disk space across filesystems should you find that the filesystem on which the data directory is located is becoming too full.

Source of Information : MySQL (4th Edition)

• 0 comments »

Symmetric keys are at the bottom of the SQL Server encryption key hierarchy. A symmetric key is used to encrypt other symmetric keys or data. Because symmetric key encryption is so much faster than asymmetric encryption and does not suffer the same data-length limitations as SQL Server’s asymmetric encryption implementations, Microsoft recommends encrypting your data exclusively with symmetric keys.

While asymmetric encryption requires two keys (a public key/private key pair), symmetric encryption requires only a single key to both encrypt and decrypt your data. Symmetric encryption is performed using block cipher algorithms, which encrypt your data in blocks of a constant size, and stream cipher algorithms, which encrypt your data in a continuous stream. Block cipher algorithms have a set encryption key size and encryption block size.


You can calculate the size of the cipher text based on the length of the plain text using one of the following formulas:

• For 8-byte block ciphers like the Data Encryption Standard (DES) family, use length of ciphertext = 8 * ( ( length of plaintext + 8 ) / 8 ) + 36.

• For 16-byte block ciphers like the Advanced Encryption Standard (AES), use length of ciphertext = 16 * ( ( length of plaintext + 16 ) / 16 ) + 44.

For either formula, add 20 bytes to the total length if you use an authenticator.


SQL Server provides the following statements to manage symmetric keys:

• CREATE SYMMETRIC KEY: Creates a symmetric key to be used for encryption. Symmetric keys can be encrypted by certificates, asymmetric keys, passwords, or even other symmetric keys.

• ALTER SYMMETRIC KEY: Allows you to change the method of securing your symmetric keys.

• DROP SYMMETRIC KEY: Drops a symmetric key from the database. Symmetric keys cannot be dropped while they are open.

• OPEN SYMMETRIC KEY: Opens and decrypts a symmetric key for use.

• CLOSE SYMMETRIC KEY: Closes a symmetric key that was previously opened.

• CLOSE ALL SYMMETRIC KEYS: Closes all symmetric keys currently open in the current session.


SQL Server does not provide backup or restore statements for symmetric keys. Because symmetric keys are stored in the current database, they are backed up during the normal database backup process. You can also re-create a symmetric key from scratch with the CREATE SYMMETRIC KEY statement. In order to re-create a symmetric key from scratch, you must supply a KEY_SOURCE and IDENTITY_VALUE. The KEY_SOURCE is a value SQL Server hashes and performs bitwise manipulations on to generate a symmetric encryption key. If not specified, SQL Server randomly generates a KEY_SOURCE. The IDENTITY_VALUE is a value SQL Server uses to generate a key GUID. Copies of the key GUID are stored with the data the key is used to encrypt. In order to re-create a symmetric key, you must supply the same KEY_SOURCE and IDENTITY_VALUE originally used to create the key. SQL Server guarantees that supplying duplicate IDENTITY_VALUE and KEY_SOURCE values will generate an identical key.

The following example creates a symmetric key and then drops it:

CREATE SYMMETRIC KEY SymTest
WITH ALGORITHM = Triple_DES
ENCRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';

DROP SYMMETRIC KEY SymTest;


Of course, creating a symmetric key is not very useful if you can’t use it to encrypt things. And as we mentioned, symmetric keys in SQL Server can be used to protect other symmetric keys or data. To protect a symmetric key with another symmetric key, use the ENCRYPTION BY SYMMETRIC KEY clause of the CREATE SYMMETRIC KEY statement. To encrypt and decrypt data, use the EncryptByKey and DecryptByKey functions. The following example creates a symmetric key, which is used to encrypt another symmetric key, which is used in turn by EncryptByKey and DecryptByKey to encrypt and decrypt some data.

USE AdventureWorks;
GO

-- Create a symmetric key to encrypt a symmetric key
CREATE SYMMETRIC KEY SymKey
WITH ALGORITHM = Triple_DES
ENCRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';

-- Open the key-encrypting key
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';

-- Create a symmetric key to encrypt data
CREATE SYMMETRIC KEY SymData
WITH ALGORITHM = Triple_DES
ENCRYPTION BY SYMMETRIC KEY SymKey;

-- Open the data-encrypting key
OPEN SYMMETRIC KEY SymData
DECRYPTION BY SYMMETRIC KEY SymKey;

-- Initialize the plain text
DECLARE @plain_text NVARCHAR(512);
SET @plain_text = N'"Those who would give up Essential Liberty to purchase a ' +
N'little Temporary Safety, deserve neither Liberty nor Safety." - Ben Franklin'
PRINT @plain_text;

-- Encrypt the data
DECLARE @cipher_text VARBINARY(1024);
SET @cipher_text = EncryptByKey(Key_GUID(N'SymData'), @plain_text);
PRINT @cipher_text;

-- Decrypt the data
SET @plain_text = CAST(DecryptByKey(@cipher_text) AS NVARCHAR(512));
PRINT @plain_text;

-- Close the data-encrypting key
CLOSE SYMMETRIC KEY SymData;

-- Close the key-encrypting key
CLOSE SYMMETRIC KEY SymKey;

-- Drop the symmetric keys
DROP SYMMETRIC KEY SymData;
DROP SYMMETRIC KEY SymKey;

The EncryptByKey function requires the key GUID of the symmetric key to encrypt your data. The symmetric key GUID can be retrieved by passing the name of the key to the Key_GUID function. The plain_text passed into the function is char, varchar, nchar, nvarchar, binary, or varbinary data. The return value of EncryptByKey is varbinary(8000). Block mode ciphers on SQL Server, like Triple DES and AES, automatically use an encryption mode known as Cipher Block Chaining (CBC) mode and random initialization vectors (IVs) to further obfuscate your encrypted data. In addition, the EncryptByKey function also accepts an optional authenticator value to help defeat whole value substitutions of your data. The authenticator value passed in is a sysname, which is synonymous with nvarchar(128). When an authenticator value is provided, it is encrypted together with the plain text to even further obfuscate your data. The authenticator value can be used to “tie” your encrypted data to a specific row. If you do use an authenticator, the add_authenticator parameter to EncryptByKey must be set to 1.

The DecryptByKey function accepts your encrypted data as a varbinary(8000), and returns the decrypted plain text as a varbinary(8000). If your original data was varchar or nvarchar, then you will need to CAST or CONVERT the result back to its original datatype. If you used an authenticator value when you encrypted the plain text, you must supply the same authenticator value to decrypt your cipher text. Note that you don’t need to supply the Key_GUID when you call DecryptByKey. This is because SQL Server stores the key GUID with the encrypted data during the encryption process.

When you use the EncryptByKey and DecryptByKey functions, and the symmetric key you are using to encrypt or decrypt data is protected by another key, you must explicitly open the symmetric key with the OPEN SYMMETRIC KEY statement. SQL Server provides the following additional functions that automatically open and decrypt your symmetric key before decrypting your data:

• DecryptByKeyAutoAsymKey: Decrypts your data with a symmetric key that is protected by an asymmetric key. This function automatically opens and decrypts your symmetric key with its associated asymmetric key.

• DecryptByKeyAutoCert: Decrypts your data using a symmetric key that is protected by a certificate. This function automatically opens and decrypts your symmetric key with its associated certificate.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »