MySQL Interview Questions -3

MySQL Interview Questions -3

Q.Explain some of the uses of MYSQL?

Mysql is widely used for web applications. This acts as a database component for many languages such as BAMP, WAMP, LAMP and MAMP. PHP and ruby added to popularity to the language. PHP and MYSQL are very much used for managing database and web applications.

Q.What happens when we don?t use Console option?

If console option is not present or used during the start of MYSQL then all the output will be written to the error log file. It is always better to use the console option because it reduces problem on the server. Error log file will be stored with an extension .err. Any text editor can read this file.

Q.How will retrieve nth level categories from one query in mysql ?

SELECT c1.CategoryID c1.Name c2.CategoryID c2.Name
FROM category AS c1 category AS c2
WHERE c1.CategoryID c2.ParentID
ORDER BY c1.CategoryID c2.CategoryID

Q.How to display nth highest record in a table for example?How to display 4th highest (salary) record from customer table?

Query: SELECT sal FROM `emp` order by sal desc limit (n-1),1If the question: "how to display 4th highest (salary) record from customer table."The query will SELECT sal FROM `emp` order by sal desc limit 3,1
There are two ways
- Using LIMIT clause
- Special SQL
Using LIMIT there are issues that if table has got same salary (field) value multiple times, but this will show you only one record, then you will not get to know that there are also others with same salary.
Another SQL Approach is -
-------------
select a.empid, a.salary from employees as a
where 2 =(select count(distinct b.salary) from employees as b
where b.salary >=a.salary)
---------------
this query will return you all the rows which comes under this criteria.

Q.Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

Q.State some security recommendations while using MYSQL?

Some of the security recommendations which should be followed while using MYSQL are as follows: -
1)Minimal privileges to users in the network.
2)Super and process privileges should be granted minimally.
3)File privileges should be granted minimally to administrators.
4)Validation of data and queries should be thoroughly checked.

Q.Explain about the rules which should be followed while assigning a username?

A username has a maximum length of 16 characters. Spaces should be avoided while creating username because they are case sensitive. Hostname will be the computer from which you are going to connect. The best way to specify a username is to connect through a local host.

Q.State some of the features of MYSQL?

Some of the features presented by MYSQL are
1) Multiple storage engines, Native storage engines, Partner developed storage engines, custom storage engines, community developed storage engines and commit grouping.
Some of these features present in MYSQL are not present in most popular RDBMS

Q.What are CSV tables?

Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.

Q.Explain about a security flaw which is present while creating a username?

Naming MYSQL databases has to be very careful because any database starting with the test name can be accessed by every one on the network. Make sure that you don't start the databases naming with test. It should be used only for experimental purposes only

Q.How To Drop an Existing Table in MySQL?

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+
1 row in set (0.40 sec)
mysql> DROP TABLE tipBackup;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tipBackup;
ERROR 1146 (42S02): Table 'ggl.tipbackup' doesn't exist
Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.

Q.How To Use Regular Expression in Pattern Match Conditions?

If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples:
SELECT 'GlobalGuideLine.com' REGEXP '.*ggl.*' FROM DUAL;
1
SELECT 'GlobalGuideLine.com' REGEXP '.*com$' FROM DUAL;
1
SELECT 'GlobalGuideLine.com' REGEXP '^F.*' FROM DUAL;
1

Q.How To Get a List of Indexes of an Existing Table?

If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:
mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...
| Non_unique | Key_name | Seq_in_index | Column_name |...
+------------+-------------+--------------+-------------+...
| 0 | PRIMARY | 1 | id |...
| 1 | tip_subject | 1 | subject |...
+------------+-------------+--------------+-------------+...
2 rows in set (0.03 sec)
It's interesting to see that there is a default index for the primary key column.

Q.How To Create a New Table in MySQL

If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":
mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
This scripts creates a testing table called "tip" with 4 columns in the current database.

Q.How To Use IN Conditions?

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the tutorial exercise below:
SELECT 3 IN (1,2,3,4,5) FROM DUAL;
1
SELECT 3 NOT IN (1,2,3,4,5) FROM DUAL;
0
SELECT 'Y' IN ('F','Y','I') FROM DUAL;
1

Q.How To Use LIKE Conditions?

A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:
'_' is used in the pattern to match any one character.
'%' is used in the pattern to match any zero or more characters.
ESCAPE clause is used to provide the escape character in the pattern.
The following tutorial exercise provides you some good pattern matching examples:
SELECT 'GlobalGuideLine.com' LIKE '%center%' FROM DUAL;
1
SELECT 'GlobalGuideLine.com' LIKE '%CENTER%' FROM DUAL;
1
-- Case insensitive by default
SELECT 'vyomtechnosoft.com' LIKE '%CENTER_com' FROM DUAL;
1

Q.How To Convert Dates to Character Strings?

You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%Y Year, numeric, four digits
%y Year, numeric (two digits)
The tutorial exercise below shows you some good examples:
SELECT DATE_FORMAT('1997-01-31', '%W, %M %e, %Y') FROM DUAL;
Friday, January 31, 1997
SELECT DATE_FORMAT('1997-01-31 09:26:50.000123',
'%W, %M %e, %Y, at %h:%i:%s %p') FROM DUAL;
Friday, January 31, 1997, at 09:26:50 AM |
SELECT DATE_FORMAT('1997-01-31 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
31-Jan-1997 09:26:50.000123

Q.What Is MyISAM?

MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.

Q.What Is "mysqlshow"?

"mysqlshow" is a command-line interface for end users to see information on tables and columns. Here are some sample commands supported by "mysqlshow":
"mysqlshow" - Shows all the databases.
"mysqlshow databaseName" - Shows all the tables in the specified database.
"mysqlshow databaseName tableName" - Shows all the columns in the specified table.
"mysqlshow --verbose" - Shows all the databases with extra information.
"mysqlshow --verbose my%" - Shows all the databases who's names match the pattern "my%" with extra information.
"mysqlshow --verbose mysql time%" - Shows all the tables who's names match the pattern "time%" in "mysql" database with extra information.

Q.How To Show Table Names with "mysqlshow"

If you want to show table names with "mysqlshow", you need to specify a database name. The followings tutorial exercise shows you how to get all table names that match a pattern:
If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:
>cd mysqlin
>mysqlshow --verbose mysql time%
Database: mysql Wildcard: time%
+---------------------------+----------+
| Tables | Columns |
+---------------------------+----------+
| time_zone | 2 |
| time_zone_leap_second | 2 |
| time_zone_name | 2 |
| time_zone_transition | 3 |
| time_zone_transition_type | 5 |
+---------------------------+----------+
5 rows in set.

Q.What Are the Differences between BINARY and VARBINARY?

Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:
BINARY stores values in fixed lengths. Values are padded with 0x00.
VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.

Q.What Are Numeric Data Types?

MySQL supports the following numeric data types:
BIT(n) - An integer with n bits.
BOOL same as BOOLEAN - Boolean values stored in 1 bit.
TINYINT - A small integer stored in 1 byte.
SMALLINT - A small integer stored in 2 bytes.
MEDIUMINT - A medium integer stored in 3 bytes.
INT same as INTEGER - An integer stored in 4 bytes.
BIGINT - An integer stored in 8 bytes.
FLOAT - A single-precision floating-point number stored in 4 bytes.
DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.
REAL - Same DOUBLE by default.
DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).
Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

Q.How do you start and stop MySQL on Windows?

net start MySQL, net stop MySQL

Q.Describe the use of PL/SQL tables

PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

Q.How to see the database architecture in MySQL??

mysql_stat();

Q.What are the advantages of mysql comparing with oracle?

MySql has many advantages in comparison to Oracle.
1 - MySql is Open source, which can be available any time
2 - MySql has no cost of development purpose.
3 - MySql has most of features , which oracle provides
4 - MySql day by day updating with new facilities.
5 - Good for small application.
6 - easy to learn and to become master.
7 - MySql has a good power these days.
even though MySql having so many advantages, Oracle is best database ever in Software development.

Q.Explain federated tables

Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

Q.Explain about HEAP table?

This type of table is stored in the memory. Speed of execution of this table is very commendable. There are associated disadvantages associated with this table the primary one being loss of stored memory which occurs when there is power failure and can cause the server to run out of memory. Columns with AUTO_INCREMENT, TEXT characteristics and BLOB are not supported.

Q.Explain about primary keys?

MYSQL allows only one primary key. This primary key can be used on multiple tables. There are many rules which should be followed such as it shouldn't be null and it can never change. Primary key assigned should be unique it cannot be matched with any other keys.
a primary should have unique values.
a primary does not contain null values.
a primary key has clustred index

Q.What are the limitations of mysql in Comparison of Oracle?

Every column in a database table is assigned a data type. The data type maybe native to the database management system or may be a custom data type. Each Oracle9i Database native data type belongs to one of six categories: character number date large object (LOB) raw and row identifier. Oracle date data types only store valid dates support automatic time zone conversion and store sub-second precision. MySQL categorizes data types into string numeric and date and time types. Unlike Oracle MySQL date data types can store invalid dates do not support time zones and the precision of time values is limited to one second

Q.What Is TIMESTAMP in MySQL?

A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:
The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.
The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.
If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.
You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".
The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column:
CREATE TABLE links (lastDate TIMESTAMP);
CREATE TABLE links (lastDate TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);

Q.What Are the "mysql" Command Line Arguments?

"mysql" supports only one optional command line argument, "database". But "mysql" allows the
operating system to redirect input and output streams at the command line level. Here are some good examples:
"mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.
"mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.
"mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the
specified file, and write output to the specified file.
Here is a tutorial exercise of how to use the command line argument to specify the database to use:
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.00 sec)
mysql> quit;
Bye

Q.How do you use Outer Join in MySQL

select sname,phone,feebal,feepaid from student s left outer join fees f
on s.sid=f.sid;
union
select sname,phone,feebal from student s right outer join fees f on s.si
d=f.sid;

Q.What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do

On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

Q.How do you configure mysql on linux

For the configuration of MySql these dependencies are required as:
mysql-5.0
mysql-server-5.0
mysqlclient9-5.0
mysql-devel-5.0
php-mysql-5.0
install by rpm -i vh mysql-server-5.0.rpm and others also same
If configured in RHEL the .rpm are needed.
if configured in debian the .deb are needed and also one can do configuration as
apt-get install mysql-server-5.0
apt-get install mysql-client-5.0
After installing
start the Mysql as
/etc/rc.d/init.d/mysqld start (RHEL)
/etc/init.d/mysql start (Debian)
The default configuration file is in /etc/my.cnf
Ans ALso reset or assign a password as
mysqladmin -u root password 'new-password
and do the rest.

Q.State some of the disadvantages present in MYSQL?

MYSQL standard has diverted on the standards of implementation of Null and default values. Arithmetic operations are susceptible to integer over flow. Mode of operation of SQL is set to unusual tolerant state which developers hate.

Q.how to add video or audio file database

using BLOB(Binary large Objects)

Q.What happens if a table has one column defined as TIMESTAMP?]

That field gets the current timestamp whenever the row gets altered.

Q.How To Create a Table Index in MySQL?

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the "CREATE INDEX" statement as shown in the following script:
mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE INDEX tip_subject ON tip(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

Q.What Is "mysqladmin" in MySQL?

"mysqladmin" is a command-line interface for administrators to perform server administration tasks. It support a number of commonly used commands like:
"mysqladmin shutdown" - Shuts down the server.
"mysqladmin ping" - Checks if the server is alive or not.
"mysqladmin status" - Displays several important server status values.
"mysqladmin version" - Displays version information of the server.
"mysqladmin create databaseName" - Creates a new database.
"mysqladmin drop databaseName" - Drops an existing database.

Q.How To Drop an Existing View in MySQL

If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:
mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM faqComment;
ERROR 1146 (42S02): Table 'ggl.faqcomment' doesn't exist

Q.How MySQL is different from SQL?

1.SQL is structural quary language but mysql is database package.
2.MySQL is an opensource database but SQL is an microsoft product.

Q.What Are the "mysql" Command Line Options?

"mysql" offers a big list of command line options. Here are some commonly used options:
"-?" - Displays a help message on how to use "mysql" and terminates the program.
"-u userName" - Specifies a user name for the server to authenticate when connecting to the server.
"-p[password]" - Specifies a password for the server to authenticate when connecting to the server.
"-h hostName" - Specifies a host name where the MySQL server is running. The default host name "localhost".
"-P portNumber" - Specifies a port number where the MySQL server is listening for connections. The default port number is 3306.
"-e command" - Executes the specified command and terminates the program.
"-t" - Specifies that the query output is displayed in text table format. This is the default display format for interactive mode.
"-H" - Specifies that the query output is displayed in HTML table format.
"-X" - Specifies that the query output is displayed in XML element format.
Here is a tutorial exercise of how to use the "-?" option with "mysql":
>cd mysqlin
>mysql -?
mysql Ver 14.12 Distrib 5.0.24, for Win32 (ia32)
Copyright (C) 2002 MySQL AB
...
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't
need to use 'rehash' to get table and field
completion, but startup and reconnecting may
take a longer time. Disable with
--disable-auto-rehash.
-A, --no-auto-rehash
...

Q.What Are the Non-Standard SQL Commands Supported by "mysql"?

There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands:
"SHOW infoName" - Shows basic information of based on the specified information name.
"SHOW infoName" - Shows basic information of based on the specified information name.
"SET ..." - Sets new values to server or connection session variables.
"GRANT ..." - Grants access privileges to users.
"REVOKE ..." - Revokes access privileges from users.
"CHECK TABLE tableName" - Checks the specified table for errors.
"ANALYZE TABLE tableName" - Analyzes the specified table.
"REPAIR TABLE tableName" - Repairs the specified table.
"BACKUP TABLE tableName" - Backs up the specified table.
"RESTORE TABLE tableName" - Restores the specified table.
"USE databaseName" - Uses the specified database as the current database.
"HELP topicName" - Returns help information on the specified topic.
Here is a tutorial exercise of how to use SHOW, USE and ANALYZE commands in "mysql":
>cd mysqlin
>mysql -u root
mysql> USE test;
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| articles |
| links |
+----------------+
2 rows in set (0.00 sec)
mysql> ANALYZE TABLE links;
+----------+-------+--------+---------------------------+
|Table |Op |Msg_type|Msg_text |
+----------+-------+--------+---------------------------+
|test.links|analyze|status |Table is already up to date|
+----------+-------+--------+---------------------------+
1 row in set (0.14 sec)

Q.How To Escape Special Characters in SQL statements?

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:
The escape character () needs to be escaped as ().
The single quote (') needs to be escaped as (') or ('') in single-quote quoted strings.
The double quote (") needs to be escaped as (") or ("") in double-quote quoted strings.
The wild card character for a single character (_) needs to be escaped as (_).
The wild card character for multiple characters (%) needs to be escaped as (%).
The tab character needs to be escaped as ( ).
The new line character needs to be escaped as ( ).
The carriage return character needs to be escaped as ( ).
Here are some examples of how to include special characters:
SELECT 'It''s Sunday!' FROM DUAL;
It's Sunday!
SELECT 'Allo, C'est moi.' FROM DUAL;
Allo, C'est moi.
SELECT 'Mon Tue Wed Thu Fri' FROM DUAL;
Mon Tue Wed Thu Fri

Q.What should I do to prepare my client code to use performance-enhancing replication?

A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with the replicated setup should be very smooth and easy - just change the implementation of your database access to read from some slave or the master, and to awlays write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions:
safe_writer_connect()
safe_reader_connect()
safe_reader_query()
safe_writer_query()
safe_ means that the function will take care of handling all the error conditions.
You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it will pay off in the long run. All applications that follow the above pattern will be able to take advantage of one-master/many slaves solution. The code will be a lot easier to maintain, and adding troubleshooting options will be trivial. You will just need to modify one or two functions, for example, to log how long each query took, or which query, among your many thousands, gave you an error. If you have written a lot of code already, you may want to automate the conversion task by using Monty's replace utility, which comes with the standard distribution of MySQL, or just write your own Perl script. Hopefully, your code follows some recognizable pattern. If not, then you are probably better off re-writing it anyway, or at least going through and manually beating it into a pattern.
Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Q.How can I use replication to improve performance of my system?

You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write-for-all-tables to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed.

Q.When and how much can MySQL replication improve the performance of my system?

MySQL replication is most beneficial for a system with frequent reads and not so frequent writes. In theory, by using a one master/many slaves setup you can scale by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.
In order to determine how many slaves you can get before the added benefits begin to level out, and how much you can improve performance of your site, you need to know your query patterns, and empirically (by benchmarking) determine the relationship between the throughput on reads (reads per second, or max_reads) and on writes max_writes) on a typical master and a typical slave. The example below will show you a rather simplified calculation of what you can get with replication for our imagined system.
Let's say our system load consists of 10% writes and 90% reads, and we have determined that max_reads = 1200 - 2 * max_writes, or in other words, our system can do 1200 reads per second with no writes, our average write is twice as slow as average read, and the relationship is linear. Let us suppose that our master and slave are of the same capacity, and we have N slaves and 1 master. Then we have for each server (master or slave):
reads = 1200 - 2 * writes (from bencmarks)
reads = 9* writes / (N + 1) (reads split, but writes go to all servers)
9*writes/(N+1) + 2 * writes = 1200
writes = 1200/(2 + 9/(N+1)
So if N = 0, which means we have no replication, our system can handle 1200/11, about 109 writes per second (which means we will have 9 times as many reads due to the nature of our application).
If N = 1, we can get up to 184 writes per second.
If N = 8, we get up to 400.
If N = 17, 480 writes.
Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already.
Note that our computations assumed infinite network bandwidth, and neglected several other factors that could turn out to be signficant on your system. In many cases, you may not be able to make a computation similar to the one above that will accurately predict what will happen on your system if you add N replication slaves. However, answering the following questions should help you decided whether and how much, if at all, the replication will improve the performance of your system:

Q.How MySQL Uses Memory ?

The list below indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the server variable relevant to the memory use is given:
The key buffer (variable key_buffer_size) is shared by all threads; Other buffers used by the server are allocated as needed.
Each connection uses some thread-specific space: A stack (default 64K, variable thread_stack), a connection buffer (variable net_buffer_length), and a result buffer (variable net_buffer_length). The connection buffer and result buffer are dynamically enlarged up to max_allowed_packet when needed. When a query is running, a copy of the current query string is also allocated.
All threads share the same base memory.
Only the compressed ISAM / MyISAM tables are memory mapped. This is because the 32-bit memory space of 4GB is not large enough for most big tables. When systems with a 64-bit address space become more common we may add general support for memory mapping.
Each request doing a sequential scan over a table allocates a read buffer (variable record_buffer).
All joins are done in one pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a big record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk. One problem in MySQL versions before Version 3.23.2 is that if a HEAP table exceeds the size of tmp_table_size, you get the error The table tbl_name is full. In newer versions this is handled by automatically changing the in-memory (HEAP) table to a disk-based (MyISAM) table as necessary. To work around this problem, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option SQL_BIG_TABLES in the client program.
In MySQL Version 3.20, the maximum size of the temporary table was record_buffer*16, so if you are using this version, you have to increase the value of record_buffer. You can also start mysqld with the --big-tables option to always store temporary tables on disk. However, this will affect the speed of many complicated queries.
Most requests doing a sort allocates a sort buffer and 0-2 temporary files depending on the result set size.
Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc() and free()).
Each index file is opened once and the data file is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where n is the maximum row length, not counting BLOB columns). A BLOB uses 5 to 8 bytes plus the length of the BLOB data. The ISAM/MyISAM table handlers will use one extra row buffer for internal usage.
For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.
Table handlers for all in-use tables are saved in a cache and managed as a FIFO. Normally the cache has 64 entries. If a table has been used by two running threads at the same time, the cache contains two entries for the table.
A mysqladmin flush-tables command closes all tables that are not in use and marks all in-use tables to be closed when the currently executing thread finishes. This will effectively free most in-use memory. ps and other system status programs may report that mysqld uses a lot of memory. This may be caused by thread-stacks on different memory addresses. For example, the Solaris version of ps counts the unused memory between stacks as used memory. You can verify this by checking available swap with swap -s. We have tested mysqld with commercial memory-leakage detectors, so there should be no memory leaks.

Q.How MySQL Locks Tables ?

You can find a discussion about different locking methods in the appendix.
All locking in MySQL is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.
The locking method MySQL uses for WRITE locks works as follows:
If there are no locks on the table, put a write lock on it. Otherwise, put the lock request in the write lock queue. The locking method MySQL uses for READ locks works as follows:
If there are no write locks on the table, put a read lock on it. Otherwise, put the lock request in the read lock queue. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.
This means that if you have many updates on a table, SELECT statements will wait until there are no more updates.
To work around this for the case where you want to do many INSERT and SELECT operations on a table, you can insert rows in a temporary table and update the real table with the records from the temporary table once in a while.
This can be done with the following code:
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
You can use the LOW_PRIORITY options with INSERT if you want to prioritize retrieval in some specific cases.
You could also change the locking code in `mysys/thr_lock.c' to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.

0 comments:

Post a Comment