Top 10 advances to availability since MySQL 5.5

#1 - InnoDB as default

When: MySQL 5.5
Why:

• Row locks instead of table-locks means that there is less contention and sudden stalls as applications become more loaded (i.e. performance degrades much better).
• InnoDB also features Multiversion concurrency control, which means that queries that read data do not have to set locks - further increasing concurrency and reducing potential stalls.
• If MySQL crashes, InnoDB is able to perform crash recovery very quickly and come back online (it just replays through its log file). For MyISAM crash recovery takes much longer, as the whole table must be examined.

#2 - Crash safe Replication

When: MySQL 5.6
Why:

• Replication slaves are crash safe. This means when a slave crashes you no longer need to re-image its data from the master or another slave. The slave can resume from where it was.
• This results is less time without HA, and reduced operations team overhead.

#3 - Semi-sync replication

When: MySQL 5.5 (will get even better performance in MySQL 5.7).
Why:

• Semi-sync is an option where you can make sure at least one slave has a copy of the data.
• This means less chance of lost transactions if a master fails.

#4 - Improved Group-commit

When: MySQL 5.6
Why:

• This means that transactions being committed near the same time in InnoDB will merge together and write to transaction logs as one operation.
• This is important because it makes making MySQL run in durable mode) not result in as big of a performance drop.
• With the addition of changes like this (and fast SSDs) I recommend durability to a lot of users.

#5 - Replication with GTIDs

When: MySQL 5.6
Why:

• Previously it was very difficult when you had a master fail with many slaves. You could not simply pick a new master from one of the slaves and reposition the other slaves under it.
• With GITDs failover and maintenance operations that change the topology are much easier.

#6 - Online DDL

When: MySQL 5.6
Why:

• A large number of DDL operations (such as adding indexes) now do not block other queries reading or writing to the table. The list of which are blocking writes (‘allows concurrent DML’) is also well documented in the manual.
• Syntax is also provided so that if the query can not run without locking, it will refuse to execute. For example: ALTER TABLE my_table ADD INDEX a (a), LOCK=NONE;.

#7 - Improved InnoDB crash recovery

When: MySQL 5.5
Why:

• It was discovered that InnoDB crash recovery time was taking a long time on new servers with large amounts of RAM.
• The performance was greatly improved with a new crash recovery algorithm in MySQL 5.1 (InnoDB plugin edition only) and MySQL 5.5 (by default).
• This is now a very serious issue if you are running an older version of MySQL on some of the hardware available today.

#8 - Improved adaptive flushing

When: MySQL 5.5 (improved in MySQL 5.6)
Why:

• For performance InnoDB delays writes by first writing to a transaction log in the foreground, and then writing to destined page locations in the background.
• In earlier versions of MySQL it is possible that the log files can ‘fill up’ and have no free space available for new modifications. This can result in sudden stalls as the server quickly frees up and makes log space available.
• In MySQL 5.5 an adaptive flushing algorithm is introduced (default: on) to flush pages more aggressively as free space is running low. This feature results in more consistent and stable response times for all queries.

#9 - LRU not victim to side workloads

When: Introduced MySQL 5.5, MySQL 5.6 (by default)
Why:

• When MySQL needs to perform sudden IO (such as in the case of a mysqldump) it is possible that the ‘good’ contents of the cache could be unloaded from memory.
• In MySQL 5.5, the cache algorithm was changed from a classic LRU to a young sublist (default: 63%) and old sublist (default: 37%) so that these expensive side-load queries could have a fixed resource limit applied to them.
• The configuration variable innodb_old_blocks_time was introduced to specify the minimum time in milliseconds that a page must be in the old sublist before it can be promoted to the young sublist. In 5.6 it defaults to 1000.

#10 - MySQL Utilities

When: Independent Release
Why:

• MySQL now releases an official set of MySQL Utilities that provide command-line utilities for maintaining and administering MySQL servers.
• Notably mysqlfailover provides a scripted way to failover when using Replication with GTIDs. MySQL Fabric (Not yet GA; Labs Release Only) provides sharding with High-Availability groups.

By Morgan Tocker
http://www.tocker.ca/2013/10/22/top-10-advances-to-mysql-availability-since-mysql-5.5.html



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

InnoDB Internal Memory Usage Indetail

InnoDB is one of the most important storage engines in MySQL. Due to its transactional capabilities, locking levels and foreign key support it has become one of the widely used storage engines for MySQL. However unlike MyISAM, InnoDB is fairly complex in its architecture. Let's review how the architecture looks like on disk and memory (RAM) subsystem.

The following components are the most important in InnoDB :

1) InnoDB buffer pool

2) Transaction log buffer

3) InnoDB IO threads

4) Transaction Log files

5) Table-space files

6) Datafiles

To demonstrate the use of all the components, let's take a simple batch of Insert statement into consideration. The statement would be "INSERT INTO employee.employees (empno, empname, sal, hiredate, dept) VALUES (1, 'Akshay', 'XXXXXX','01-12-2011', 'MySQL')".

So the above statement makes it through components at the server level like we referred earlier in our previous document. Likewise coming from Client, scanning the Query cache, parsing, pre-processing, optimizing and then finally to the storage engine, let's see what happens further at the storage engine level :

1) Once the statement enters the InnoDB kernel, innodb checks whether the requested data "page" exist in the "BUFFER POOL". The buffer pool contains all the data pages which needs to be changed (INSERT, UPDATE and DELETE) or read ( SELECT). It will contain both the Index as well as Data Pages. So the INSERT statement above will check if the page in which record with values "(1, 'Akshay', 'XXXXXX','01-12-2011', 'MySQL')" needs to be inserted already exist in the pool, if it finds the page it will make the changes to page or if it doesnt find the page it will read from the Disk (datafile) in to the memory (Buffer pool) and then change it. InnoDB Buffer pool is most important memory structure in InnoDB, and is set using "innodb_buffer_pool_size" variable. Usually this is set to around 50-80% of the total RAM.

2) Once the data and index pages are changed in the buffer pool the pages are marked "DIRTY" and the INSERT statement is logged in the transaction log buffer. The function of transaction log buffer is very trivial in an RDBMS. Later the contents of transaction log buffer are written to the transaction log files (specifically on COMMITs). Lets summarize the document later with note on understanding transaction logs.

3) InnoDB IO threads are internal to InnoDB kernel and not related to any connection threads or O.S. threads (InnoDB IO threads works at the storage engine layer, whereas Connection threads works at MySQL Server layer). These IO threads (mainly known as Innodb Read threads and Write threads) does the job of writing DIRTY pages to the disk files from buffer pool and log buffer and reading pages from the files. Hence the above INSERT's data will written to the disk by one of the IO threads.

4) Transaction log files contains the contents from transaction log buffer on a durable media (Hard disks). It's used for transaction recovery during Instance crash (We will visit Instance crash in next sessions) and for POINT IN TIME RECOVERY. These files can be found in MySQL datadir namely "ib_logfile0" and "ib_logfile1". The files are used in a circular fashion, like, initially innodb will start filling up "ib_logfile0" and then "ib_logfile1".

5) Table-space files usually named as "ibdata1" files are used for multiple purposes. It stores the actual table and index data (if "innodb_file_per_table" is disabled), data-dictionary (meta-data about Innodb tables) and the undo-logs (used for ROLLBACK). So the DIRTY pages from BUFFER POOL will be written to the table-space files by the IO threads.

6) Datafiles are the files created when "innodb_file_per_table" is set. These files have filenames like <table_name>.ibd. These files contains index as well as actual data of the tables. These files allow easy maintenance as compared single tablespace file due to its size considerations. Every table will have its own .ibd file created in its respective data directory.

InnoDB uses its log to reduce the cost of committing transactions. Instead of flushing the buffer pool to disk when each transaction commits, it logs the transactions. The changes transactions make to data and indexes often map to random locations in the tablespace, so flushing these changes to disk would require random I/O. InnoDB assumes it’s using conventional disks, where random I/O is much more expensive than sequential I/O because of the time it takes to seek to the correct location on disk and wait for the desired part of the disk to rotate under the head.

InnoDB uses its log to convert this random disk I/O into sequential I/O. Once the log is safely on disk, the transactions are permanent, even though the changes haven’t been written to the data files yet. If something bad happens (such as a power failure), InnoDB can replay the log and recover the committed transactions. Of course, InnoDB does ultimately have to write the changes to the data files, because the log has a fixed size. It writes to the log in a circular fashion: when it reaches the end of the log, it wraps around to the beginning. It can’t overwrite a log record if the changes contained there haven’t been applied to the data files, because this would erase the only permanent record of the committed transaction.

To understand this more deeply, please read about difference between Random I/O and Sequential I/O. There are some SQL statements which can force such an I/O, take it as a Homework to find such Statements.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

LINUX COMMANDS FOR DBA's

1) Less :
Helps in viewing the content of files page by page.
ex)
# less country12_100812.sql

2) More :
It is also equivalent to less. It shows the percent of the file viewed so far.
ex)
# more country12_100812.sql

3) cat :
It shows the contents of the file as a whole. But not page by page. Useful for small files.
ex)
# cat anaconda-ks.cfg

4) ls :
List the files present in the current directory or the path.
ex)
# ls
alldatabase_030812.sql alldb100812.sql alldb.sql Country090812.sql
country12_100812.sql lost+found mysql_030812 Prabhu.sql world.sql

5) pwd :
It shows the path of your working directory .
ex) # pwd
/home/karthik/Desktop

6) df
Displays the partitions present and the server . It also provide its disk usage and Size Used Avail Use% Mounted on
3.9G 2.0G 1.9G 52% /
495M 420K 494M 1% /dev/shm
194M 55M 130M 30% /boot
6.2G 1.5G 4.5G 25% /data
2.0G 202M 1.7G 11% /logs
485M 11M 449M 3% /tmp
3.9G 3.3G 411M 90% /usr
2.0G 976M 893M 53% /var

7) du
Retrieve the disk usage of a folder or a file.
ex)
# du -sh
963M

8) ifconfig :
Displays the IP address assigned to the server and also its other network requistics.
ex) # ifconfig
eth1
Link encap:Ethernet HWaddr 00:1D:7D:80:C1:37
inet addr:10.10.25.98 Bcast:10.10.25.255 Mask:255.255.255.0
inet6 addr: fe80::21d:7dff:fe80:c137/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:906 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:47394 (46.2 KiB)
Interrupt:21 Base address:0xa000

9) Traceroute & tracepath :
They help in finding the network delay in reaching a server from remote host.
ex)
#tracepath google.com
#tracepath 10.10.25.98

10) Ping :
Examines whether the server is reachable or not . Helps in trouble shooting the packet
losses.
ex) ping 127.0.0.1

11) netstat :
Displays the active internet connections of the servers and the port opened for the
process.

12) crontab :
Scheduler for scheduling the automated jobs like backup and other maintenance scripts.

13) Free :
Displays the total ram present in the system and their utility.
# free -m

14) Top :
It is similar to the Task manager in Windows . It displays all the process running
and their resource usage.

15) uptime :
Displays the total time after the server is booted, It also shows the load average.
ex)
# uptime
00:56:09 up 57 min, 2 users, load average: 0.00, 0.05, 0.03

16) ps :
It makes list of the current processes running on the system.

17) chmod :
Changes the permission of the files . Read , write and execute.

18) chown :
Changes the ownership of the file to some other user. We must have root permission to
change the ownership of files.

19) mkdir and rmdir:
Mkdir : Creates a directory (folder )
Rmdir : removes the directory (folder )

20) cd :
Change the active directory to required directory.

21) head :
Displays only the first 10 lines of the file as a default.

22) tail :
Displays the last 10 lines of the files as a default.

23) mv :
Move the contents from one directory to other. It is also helpful in renaming the files.

24) wc :
Displays the number of lines, word and character present in a file.

25)grep :
Search a word or a phrase in a file
ex)
#grep -i “mysql” grep_test.txt

26) find :
Search for a file with a pattern . It returns the file name.

27) locate:
Search for a file with a pattern. It returns file with a full path.

28) echo :
Prints the content given inside the quotes.
ex)
# echo "mysql is a database"
mysql is a database

29) gzip :
Compress the files with .gz as extension . Can be extracted using gunzip command.
Ex )
compress
# gzip alldb100812.sql
extract
# gunzip alldb100812.sql.gz

30 ) tar
Tape Archive file. It is a compression format used mostly in binary installation files.
It has extension of .tar

31) kill :
To terminate a running process by the pid (process id)
Ex) Kill 528412

32) who :
List the user logged into the server and their duration

33) whoami :
Displays the user name of current session.

34) sort :
Arranges the content of the files in the ascending or descending order.

35) Vi :
The editor command used to make changes in existing file . It is used widely in
scripting too as “Vim”

36) man :
Shows the manual page of a command . It gives the complete info about the
command.

37) useradd :
To add a linux user for any new process like mysql. Requires root access

38) groupadd:
To add a group in the linux. Requires root access.

39) passwd :
To change or assign password foe a user.

40 ) uname ;
It displays the system information like the OS ,kernel and processor.
# uname -a
Linux remotemysqldba.com 2.6.32-279.2.1.el6.x86_64 #1 SMP Fri Jul 20 01:55:29 UTC
2012 x86_64 x86_64 x86_64 GNU/Linux

41) scp :
Copy the files to a remote server.
ex)
scp mysql.sql root@10.101.150.21:

42) ssh:
It is to connect the remote shell .
ex)
ssh root@10.101.150.21

43 ) telnet :
It is also used to connect the remote host. But it used for checking port opening.
ex)
telnet 10.101.150.21 22


MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

Installing MySQL on windows environment.

MySQL Installaion on windows. 

This tutorial will show you how to install MySQL on Windows XP. So, go to the MySQL website and download the MySQL Community Server.

File to download: Windows (x86, 32-bit), MSI Installer Essentials - Recommended if your operating system is a windows XP 64bit version use the Windows (x86, 64-bit), MSI Installer

On the downloaded MySQL double click and this will start the installation of MySQL and follow the instructions.

Select "I accept terms in the licence agreement" and click next: -

Now you have to select installation type

Typical : it installs server as well as common client programs also.

Custom: you can choose the components to be install and the path where to install.

Complete: it installs server as well as all client programs.(Prefered if you are new to MySQL)

now we are going to see custom installaiton.

select the "custom" icon to change the path of the installation.

Now browse or navigate to the folder C:\server\mysql we want to install the files in this folder.

After the change press ok.

Press install.

It will list the configuration types

1.Detailed : While installing the mysql server, the server itself changes the configurations changes based on the available resources ( like memory,no of connections) based on the given inputs.

2.Standard : This will install the mysql server with default configuration options.

Note : standard is the best option for personal or development purpose, Detailed is prefarable only for the mysql servers.(what ever it may be the installation type, you can chage the configuration file later also).

Now we are going to install wth a standard option.

Select a standerd configuration press next.

To install the mysql service, press next.

We need a password for MySQL database please enter a password, please remember the login name in most cases is root and the password your choosing, in my case I choose myrootpassword.

Click on execute to install the server components.

Click on finish once everything is done.

Now MySql database is successfully installed and it is ready to use.






MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

Installing MySQL From A Tar File

Installing MySQL from a tarball

Installing MySQL from a tarball should be straightforward but there are several steps, and therefore several opportunities for things to get funky. Nonetheless, doing it this way you will learn more about several aspects of your system, and what's really going on with the installation, and you'll see all the work that you'd save by using rpm.
Installing things from tarballs is a common practice on UNIX/Linux systems. You'll be doing it later when you install DBI as well. By then you'll be a pro. It usually involves just a few simple steps. Tarballs usually come as compressed, tar'd files. tar stands for Tape Archive, and refers to a format for storing and archiving files and directory structures. When you unpack a tar ball it recreates the original directory structure and files.
The binary source tarball comes with everything you need to run MySQL, as well as all the client programs for interacting with the database, and the development libraries for writing or using your own programs with the database. To install the software you should be able to log into your system as root. By default, mysql would run as a root process, and some of the files would be owned by root. Because this is something of a security risk, many people choose to create a regular user for running the mysql deamon. This way there are less root owned files on your system, and fewer opportunities for exploiting a root owned process. Once you have the software, and created a user, you can install the database and start it up. The last steps are simply to set an administration password, and then modify your system so that it starts the mysql deamon everytime the system is started.

Installation Overview

Basically you'll be performing the following steps:

1. Get the tarball
2. decompress and unpack the tarball
3. create a symbolic link to the mysql directory
4. create a user whose ID is used for running the database
5. change ownership of the directory from root to user mysql
6. edit the init startup file
7. su to user mysql
8. initialize the database and create the tables with the included install script
9. start the database
10. change the root password
11. change back to user root
12. place startup file in proper startup location
    

Get the software and unpack it

The tarball is more formally called a binary source distribution. You can get the source distribution at the same place you get the rpm files: www.mysql.com. It will have some filename like: mysql-3.22.32-pc-linux-gnu-i686.tar.gz
A common place to install software on linux systems is in the directory:
/usr/local/
Once you have the software, log in as root and move the tarball to that directory. The filename of the software will have two extenions on the end: .tar.gz The gz indicates that the file is compressed. To uncompress the file issue the command:
gunzip filename.tar.gz
this will decompress the file and remove the .gz extension. Now you have a true tar file. Unpack it using the tar command with the appropriate arguments:
tar -xvf filename.tar
the x means extract, the v means verbose (show me everything that's happening), and the f indicates that the next argument is the filename to extract. You should see the names of files and directories wizzing by your screen. If you list the contents of the current directory you should see that a new directory containing all the mysql files has been created.
Now that you have the distribution unpacked, it's a good idea to create the user that will own the files and run the database. You can do this with the adduser command.
Since it has a long clunky name, you should create a symbolic link to it (the same thing as an alias or shortcut):

Create a user for the mysql program

From a security point of view you'd like to run the server as some user other than root. It's also a good idea to not have any more root-owned files lying around your system than are necessary. To accomplish this you can simply create a user and group for owning and running the server. I create a user called mysql, with no home directory. We do this with the adduser and groupadd commands.

/usr/sbin/groupadd mysql
#create the user mysql with no home directory and group mysql
/usr/sbin/adduser mysql -M -g mysql

Now su to user mysql and initialize your database tables. It's important to do this as the user mysql, because otherwise the tables and directories will be created as owned by root

[root@zenith mysql]#su mysql [mysql@zenith mysql]# [mysql@zenith mysql]# scripts/mysql_install_db Creating db table Creating host table Creating user table Creating func table Creating tables_priv table Creating columns_priv table To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' See the manual for more instructions. Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy. [mysql@zenith mysql-3.22.32-pc-linux-gnu-i686]#

Now before you can set the root password, you must start the mysqld daemon. This is done with a little script in the scripts directory called safe_mysqld.

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# bin/safe_mysqld &
[1] 1541

# Starting mysqld daemon with databases from /usr/local/mysql-3.22.32-pc-linux-gnu-i686/data

 [root@zenith mysql-3.22.32-pc-linux-gnu-i686]

You can see if the daemon is running by looking at all the system process:
ps aux | more
Now try setting the root password:

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# ./bin/mysqladmin -u root password 'friday'

You can also try connecting to the server and issuing a command:

[root@zenith mysql-3.22.32-pc-linux-gnu-i686]# bin/mysql -pfriday mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.00 sec) mysql>

Set the mysql daemon to start at system start up

Next we have to make it so that the mysql server will start up everytime the computer is started. This is accomplished by a script that holds information about how the server should be started. We need to edit that script and place it in the proper location for system start up. There's a place onthe system for start up files edit support-files/mysql.server and change user to mysql

[root@zenith support-files]# cp mysql.server mysql.server-dist
[root@zenith support-files]# emacs -nw mysql.server
[root@zenith support-files]# cp mysql.server /etc/rc.d/init.d/
[root@zenith support-files]# cd /etc/rc.d/init.d/
[root@zenith init.d]# chmod a+x mysql.server 
[root@zenith init.d]# ls -al mysql.server
-rwxrwxr-x   1 root     root         2815 Oct  9  2000 mysql.server

When your system starts up there is a series of things in rc.d directory that occur. To add mysql as one of the things that gets started use the chkconfig command. Type: man chkconfig for more information on this. Or continuing from above, issue the following comands:

[root@zenith init.d]# /sbin/chkconfig --add mysql.server
[root@zenith init.d]# /sbin/chkconfig --list mysql.server
mysql.server 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@zenith init.d]#

The first command "chkconfig --add" adds the proper links in the right places for your system to execute the mysql.server script upon system startup. The second command "chkconfig --list" reports back to you the run levels in which the script is executed.
It's also a good idea to add the path to the mysql bin directory to the default path of users.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

What is MySQL? Features and Limitations.

What is MySQL?

MySQL is a open source Relational Database Management System. MySQL is very fast reliable and flexible Database Management System. It provides a very high performance and it is multi threaded and multi user Relational Database management system.

MySQL is one of the most popular relational database Management System on the web. The MySQL Database has become the world's most popular open source Database, because it is free and available on almost all the platforms. The MySQL can run on Unix , window, and Mac OS.

MySQL is used for the internet applications as it provides good speed and is very secure. MySQL was developed to manage large volumes of data at very high speed to overcome the problems of existing solutions. MySQL can be used for verity of applications but it is mostly used for the web applications on the internet.

Pronounced "my ess cue el" (each letter separately) and not "my SEE kwill." MySQL is an open source RDBMS that relies on SQL for processing the data in the database. MySQL provides APIs for the languages C, C++, Eiffel, Java, Perl, PHP and Python. In addition, OLE DB and ODBC providers exist for MySQL data connection in the Microsoft environment. A MySQL .NET Native Provider is also available, which allows native MySQL to .NET access without the need for OLE DB.

MySQL is developed, supported and marketed by MySQL AB. The database is available for free under the terms of the GNU General Public License (GPL) or for a fee to those who do not wish to be bound by the terms of the GPL.

Features of MySQL
The following list shows the most important properties of MySQL. This section is directed to the reader who already has some knowledge of relational databases. We will use some terminology from the relational database world without defining our terms exactly. On the other hand, the explanations should make it possible for database novices to understand to some extent what we are talking about.

Relational Database System: Like almost all other database systems on the market, MySQL is a relational database system.

Client/Server Architecture: MySQL is a client/server system. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they query data, save changes, etc. The clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).

Almost all of the familiar large database systems (Oracle, Microsoft SQL Server, etc.) are client/server systems. These are in contrast to the file-server systems, which include Microsoft Access, dBase and FoxPro. The decisive drawback to file-server systems is that when run over a network, they become extremely inefficient as the number of users grows.

SQL compatibility: MySQL supports as its database language -- as its name suggests – SQL (Structured Query Language). SQL is a standardized language for querying and updating data and for the administration of a database. There are several SQL dialects (about as many as there are database systems). MySQL adheres to the current SQL standard (at the moment SQL:2003), although with significant restrictions and a large number of extensions.
Through the configuration setting sql-mode you can make the MySQL server behave for the most part compatibly with various database systems. Among these are IBM DB/2 and Oracle.

SubSELECTs: Since version 4.1, MySQL is capable of processing a query in the form SELECT * FROM table1 WHERE x IN (SELECT y FROM table2) (There are also numerous syntax variants for subSELECTs.)

Views: Put simply, views relate to an SQL query that is viewed as a distinct database object and makes possible a particular view of the database. MySQL has supported views since version 5.0.

Stored procedures: Here we are dealing with SQL code that is stored in the database system. Stored procedures (SPs for short) are generally used to simplify certain steps, such as inserting or deleting a data record. For client programmers this has the advantage that they do not have to process the tables directly, but can rely on SPs. Like views, SPs help in the administration of large database projects. SPs can also increase efficiency. MySQL has supported SPs since version 5.0.

Triggers: Triggers are SQL commands that are automatically executed by the server in certain database operations (INSERT, UPDATE, and DELETE). MySQL has supported triggers in a limited form from version 5.0, and additional functionality is promised for version 5.1.

Unicode: MySQL has supported all conceivable character sets since version 4.1, including Latin-1, Latin-2, and Unicode (either in the variant UTF8 or UCS2).

User interface: There are a number of convenient user interfaces for administering a MySQL server.

Full-text search: Full-text search simplifies and accelerates the search for words that are located within a text field. If you employ MySQL for storing text (such as in an Internet discussion group), you can use full-text search to implement simply an efficient search function.

Replication: Replication allows the contents of a database to be copied (replicated) onto a number of computers. In practice, this is done for two reasons: to increase protection against system failure (so that if one computer goes down, another can be put into service) and to improve the speed of database queries.

Transactions: In the context of a database system, a transaction means the execution of several database operations as a block. The database system ensures that either all of the operations are correctly executed or none of them. This holds even if in the middle of a transaction there is a power failure, the computer crashes, or some other disaster occurs. Thus, for example, it cannot occur that a sum of money is withdrawn from account A but fails to be deposited in account B due to some type of system error.

Transactions also give programmers the possibility of interrupting a series of already executed commands (a sort of revocation). In many situations this leads to a considerable simplification of the programming process. In spite of popular opinion, MySQL has supported transactions for a long time. One should note here that MySQL can store tables in a variety of formats. The default table format is called MyISAM, and this format does not support transactions. But there are a number of additional formats that do support transactions. The most popular of these is InnoDB, which will be described extensively in this book.

Foreign key constraints: These are rules that ensure that there are no cross references in linked tables that lead to nowhere. MySQL supports foreign key constraints for InnoDB tables.

GIS functions: Since version 4.1, MySQL has supported the storing and processing of two-dimensional geographical data. Thus MySQL is well suited for GIS (geographic information systems) applications.

Programming languages: There are quite a number of APIs (application programming interfaces) and libraries for the development of MySQL applications. For client programming you can use, among others, the languages C, C++, Java, Perl, PHP, Python, and Tcl.

ODBC: MySQL supports the ODBC interface Connector/ODBC. This allows MySQL to be addressed by all the usual programming languages that run under Microsoft Windows (Delphi, Visual Basic, etc.). The ODBC interface can also be implemented under Unix, though that is seldom necessary.
Windows programmers who have migrated to Microsoft's new .NET platform can, if they wish, use the ODBC provider or the .NET interface Connector/NET.

Platform independence: It is not only client applications that run under a variety of operating systems; MySQL itself (that is, the server) can be executed under a number of operating systems. The most important are Apple Macintosh OS X, Linux, Microsoft Windows, and the countless Unix variants, such as AIX, BSDI, FreeBSD, HP-UX, OpenBSD, Net BSD, SGI Iris, and Sun Solaris.

Speed: MySQL is considered a very fast database program. This speed has been backed up by a large number of benchmark tests (though such tests -- regardless of the source -- should be considered with a good dose of skepticism).

Limitations

• When MySQL is used with standard tables (table type MyISAM), then locking, that is, the temporary blocking of access to or alteration of database information, is in operation only for entire tables (table locking). You can circumvent the table-locking problem by implementing transaction-capable table formats, such as InnoDB, that support row locking.
• In using MyISAM tables, MySQL is not able to execute hot backups, which are backups during operation without blocking the tables with locks. Here again, the solution is InnoDB, though here the hot backup function is available only in the form of a commercial supplement.
  
• Many database systems offer the possibility of defining custom data types. MySQL does not support such functionality, nor is any currently planned.
  

• MySQL has up to now ignored the general XML trend. It is not clear when MySQL will support direct processing of XML data. Numerous commercial database systems offer considerably more functionality in this area, and even the SQL:2003 standard provides for a host of XML functions.

• MySQL is in fact a very fast database system, but it is very limited in its usability for real-time applications, and it offers no OLAP functions. OLAP stands for online analytical processing, and refers to special methods for the management and analysis of multidimensional data. OLAP-capable database systems are often called data warehouses.

• MySQL supports, since version 5.0, stored procedures and triggers, but these functions have not yet fully matured (this applies especially to triggers) and do not yet have the same stability and plenitude of functions offered by commercial database systems.

• Similar restrictions hold as well for the GIS functions introduced in version 4.1. Commercial database systems offer in some cases considerably greater functionality.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

What is a Database & Database system concepts.

What Is a Database?
Before we can answer the central question of this chapter, namely, What is MySQL? you and I must find a common language. Therefore, this section presents a rudimentary database glossary, without going into great detail. (If you have already had significant dealings with relational databases, you can skip the next couple of pages in good conscience.)

There is scarcely to be found a term that is less precise than database. A database can be a list of addresses residing in a spreadsheet program (such as Excel), or it can be the administration files of a telecommunications firm in which several million calls are registered daily, their charges accurately calculated, monthly bills computed, and warning letters sent to those who are in arrears.

A simple database can be a stand-alone operation (residing locally on a computer for a single user), while others may be used simultaneously by thousands of users, with the data parceled out among several computers and dozens of hard drives. The size of a database can range from a few kilobytes into the terabytes.

In ordinary usage, the word "database" is used to refer to the actual data, the resulting database files, the database system (such as MySQL or Oracle), or a database client (such as a PHP script or a program written in C++). Thus there arises a great potential for confusion as soon as two people begin to converse on the subject of databases.

Relations, Database Systems, Servers and Clients
A database is an ordered collection of data, which is normally stored in one or more associated files. The data are structured as tables, where cross references among tables are possible. The existence of such relations among the tables leads to the database being called a relational database.
Let us clarify matters with an example. A database might consist of a table with data on a firm's customers (name, address, etc.), a table with data on the products the firm offers, and finally, a table containing the firm's orders. Through the table of orders it is possible to access the data in the other two tables (for example, via customer and product numbers).
MySQL, Oracle, Microsoft SQL Server, and IBM DB2 are examples of relational database systems. Such a system includes the programs for managing relational databases. Among the tasks of a relational database system are not only the secure storage of data, but also such jobs as the processing of commands for querying, analyzing, and sorting existing data and for storing new data. All of this should be able to take place not only on a single computer, but over a network as well. Instead of a database system we shall often speak of a database server.
Where there are servers, there are clients. Every program that is connected to the database system is called a database client. Database clients have the job of simplifying the use of the database for the end user. No user of a database system in his or her right mind would wish to communicate directly with the database server. That is much too abstract and inconvenient. (Let programmers worry about such direct communication!) Instead, the user has a right to expect convenient tables, list boxes, and so on to enable the location of data or to input new data.
Database clients can assume a variety of forms, and indeed, they are often not recognized by the user as database programs at all. Some examples of this type of client are HTML pages for the display and input of messages in an on-line discussion group, a traditional program with several windows for managing addresses and appointments, and a Perl script for executing administrative tasks. There is thus wide scope for database programming.

Relational Versus Object-Oriented Database Systems
Relational databases have dominated the database world for decades, and they are particularly well suited for business data, which usually lend themselves to structuring in the form of tables. Except for the following two paragraphs, this entire book discusses only relational databases (though we shall not always stress this point).
Another kind of database is the object-oriented database. Such databases can store free-standing objects (without having to arrange them in tables). Although in recent years there has been a trend in the direction of object-oriented programming languages (such as Object-Store, O2, Caché), object-oriented databases have found only a small market niche.
Note that relational databases can be accessed by means of object-oriented programming languages. However, that does not turn a relational database into an object-oriented one. Object-oriented database systems enable direct access to objects defined in the programming language in question and the storage of such objects in the database without conversion (persistency). It is precisely this that is not possible with relational database systems, in which everything must be structured in tables.

Tables, Records, Fields, Queries, SQL, Index, Keys
We have already mentioned tables, which are the structures in which the actual data are located. Every line in such a table is called a data record, or simply record, where the structure of each record is determined by the definition of the table. For example, in a table of addresses every record might contain fields for family name, given name, street, and so on. For every field there are precise conditions on the type of information that can be stored (such as a number in a particular format, or a character string with a predetermined maximum number of characters).
The description of a database consisting of several tables with all of its fields, relations, and indexes (see below) is called a database model. This model defines the construction of the data structures and at the same time provides the format in which the actual data are to be stored.
Tables usually contain their data in no particular order (more precisely, the order is usually that in which the data have been entered or modified). However, for efficient use of the data it is necessary that from these unordered data a list can be created that is ordered according to one or more criteria. It is frequently useful for such a list to contain only a selection of the data in the table. For example, one could obtain a list of all of one's customers, ordered by ZIP code, who have ordered a rubber ducky within the past twelve months.
To create such a list, one formulates queries. The result of the query is again a table; however, it is one that exists in active memory (RAM) and not on the hard drive.
To formulate a query one uses SQL instructions, which are commands for selecting and extracting data. The abbreviation SQL stands for Structured Query Language, which has become a standard in the formulation of database queries. Needless to say, every producer of a database system offers certain extensions to this standard, which dilutes the goal of compatibility among various database systems.
When tables get large, the speed at which a query can be answered depends significantly on whether there is a suitable index giving the order of the data fields. An index is an auxiliary table that contains only information about the order of the records. An index is also called a key.
An index speeds up access to data, but it has disadvantages as well. First, every index increases the amount of storage on the hard drive necessary for the database file, and second, the index must be updated each time the data are altered, and this costs time. (Thus an index saves time in the reading of data, but it costs time in entering and altering data. It thus depends on the use to which the data are to be put whether an index is on the whole a net plus or minus in the quest for efficiency.)
A special case of an index is a primary index, or primary key, which is distinguished in that the primary index must ensure a unique reference to a record. Often, for this purpose one simply uses a running index number (ID number). Primary indexes play a significant role in relational databases, and they can speed up access to data considerably.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers