Hi All,

Here is a simple tip to improve the query performance in MYSQL DB Server.

In common, MYSQL server recives a request it will parse it and retrives data from database and send it back to the client.

In the situation we need to get the result of the similar query several times but differs in the condition which is dynamic then the server will just sent the result from CACHE.

Enable caching in MySQL

Setting the Query Cache Size

mysql> SET GLOBAL query_cache_size = 16777216;

Setting up quey caching in config file

mysql> vi /etc/my.cnf

Quey CACHE System Variables

• query_cache_size
Size of the cache query in Bytes.
• query_cache_type
a. 0 -> Don’t cache results in or retrieve results from the query cache.
b. 1 -> Cache all query results except for those that begin with SELECT S_NO_CACHE.
c. 2 -> Cache results only for queries that begin with SELECT SQL_CACHE
• query_cache_limit
The Limit of the Cached query in Bytes.

Try this to Enable the Query CACHE,

__________________
-Murali..

Hi,

On a table with 1,00,000 records, Normally we doing this kind of process
In MySQL advanced to fatch the data, this format is more than 3 time faster than doing.

MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS().

GROUP BY .. WITH ROLLUP

Use ROLLUP with GROUP by functions in order to calculate aggregate value of the result set returned by GROUP BY
function. This will reduce extra work, writting an another query for calculating sum of salary, we can directly send
the entire result set to the client. It might reduce the additional calculation process done at client side.

__________________
Keep smiling...

Tips about Speed of INSERT Statements
Inserting a row is determined by the following factors

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

bulk_insert_buffer_size variable is used to stored the insert statement buffer. Default 8MB.

• Bulk Insert statement is compare to single insert statement, Bulk insert is considerably faster (many times faster) .
• If multiple clients are inserting a lot of rows, you can get higher speed by using the INSERT DELAYED statement
• To loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.
• Before inserting the data, to OFF the all index, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes.

  • Optionally create the table with CREATE TABLE.
  • Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.
  • Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This removes all use of indexes for the table.
  • Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.
  • Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.
  • Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

To speed up INSERT operations

Bulk insert is increase performance in MyISAM tables, for both LOAD DATA INFILE and INSERT, to enlarge the key cache to increasing the key_buffer_size system variable.

GROUP BY OPTIMIZATION
======================

While using the GROUP BY in the select statement,
output rows are sorted according to the GROUP BY columns.

See the following example.

This is the full records of the table.

mysql> select * from sample;
+----+---------+-------+--------+
| id | name | dept | salary |
+----+---------+-------+--------+
| 1 | Ashok | CSE | 20000 |
| 2 | Kumar | PROD | 12000 |
| 3 | Sathish | IT | 20000 |
| 4 | Bala | EEE | 15000 |
| 5 | Suresh | ECE | 15000 |
| 6 | Karan | CSE | 20000 |
| 7 | Varun | EEE | 12000 |
| 8 | Vasu | MECH | 5000 |
| 9 | Logesh | CIVIL | 13000 |
| 10 | Visu | AUTO | 20000 |
+----+---------+-------+--------+
10 rows in set (0.00 sec)


Record set with GROUP BY....

mysql> select id,dept,sum(salary) from sample group by dept;
+----+-------+-------------+
| id | dept | sum(salary) |
+----+-------+-------------+
| 10 | AUTO | 20000 |
| 9 | CIVIL | 13000 |
| 1 | CSE | 40000 |
| 5 | ECE | 15000 |
| 4 | EEE | 27000 |
| 3 | IT | 20000 |
| 8 | MECH | 5000 |
| 2 | PROD | 12000 |
+----+-------+-------------+
8 rows in set (0.00 sec)


If the record set will increase in the table,
sorting process will take the long time....
so, to avoid the overhead of sorting..
we can add "ORDER BY NULL" with the GROUP BY.

mysql> select id,dept,sum(salary) from sample group by dept order by null;
+----+-------+-------------+
| id | dept | sum(salary) |
+----+-------+-------------+
| 1 | CSE | 40000 |
| 2 | PROD | 12000 |
| 3 | IT | 20000 |
| 4 | EEE | 27000 |
| 5 | ECE | 15000 |
| 8 | MECH | 5000 |
| 9 | CIVIL | 13000 |
| 10 | AUTO | 20000 |
+----+-------+-------------+
8 rows in set (0.00 sec)


I hope, this will help you all...


Regards,
S.Ashokkumar

Speed of UPDATE Statements

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed is depends up on the amount of data to updated and the number of indexes that are updated.

Indexes that are not changed do not get updated.

Another way to update fast updates
To delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.

For a MyISAM tables are uses dynamic row format, updating a row to a longer total length may split the row.

OPTIMIZE TABLE
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ..

OPTIMIZE TABLE should be used if deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

For MyISAM tables, OPTIMIZE TABLE works as follows:
1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

Speed of DELETE Statements

The time take to delete records is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache, "key_buffer_size" in system variable. Default key_buffer_size is 1 MB.

If you have delete all rows in a table, using TRUNCATE TABLE tbl_name is faster than DELETE FROM Truncate operations are not transaction-safe, if an any error occurs when attempting, to activate the table and then perform it, finally release the lock.

Hi all,

MYSQL has several keywords that can be placed in the SQL statement to give the database server an explicit optimization.

SQL_CACHE

When query_cache_type Variable is set to 2 then u can instruct the MYSQL to use CACHE in the SQL Query.

Modify the query_cache_type by

SQL_NO_CACHE

When u need to disable the MYSQL inbuilt Query CACHING mechanism for making your query more efficient when the SQL Query built dynamic based on various conditions.

Modify the query_cache_type by
HIGH_PRIORITY

The HIGH_PRIORITY keyword can be used on INSERT statements to let MYSQL know that this is a high priority query. It allows the specified Query to do the function before the other process which is in queue.
LOW_PRIORITY

The LOW_PRIORITY keyword can be used on INSERT statements. When u feel the statement that can be executed slowly after other High Priority statement then need to specify the Keyword LOW_PRIORITY.

__________________
-Murali..

Another tips :

SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result
set has many rows. In this case, MySQL directly uses disk-based temporary tables if needed,
and prefers sorting to using a temporary table with a key on the GROUP BY elements.
1 row in set (0.00 sec)

Here, SQL_BIG_RESULT uses advanced filesorting algorithm hence the query performance will be high.


SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result
set is small. In this case, MySQL uses fast temporary tables to store the resulting table
instead of using sorting. This should not normally be needed.


Refer the highlighted part to know the difference between these two.

__________________
Keep smiling...

Speed up index scans - MySQL InnoDB Defragmentation

If there are random insertions into or deletions from the indexes of a table, the indexes may become
fragmented.Fragmentation means that the physical ordering of the index pages on the disk is not close
to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

ALTER TABLE tbl_name Engine=InnoDB

That causes MySQL to rebuild the table, so that it can speed up index scans. Another way to perform a defragmentation operation is to use
mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

__________________
Keep smiling...

Optimize MySQL database server response time

High loaded web sites can get the slow response time. Because of lot different users visit the web sites. Visitor’s site queries to MySQL database server, the server response time is slow.

MySQL database response time is very important for web site or an application to run efficiently. They have many ways to improve the MySQL server performance, like cache size, Stop DNS resolution etc.

1. Disable the DNS hostname

Disable the DNS hostname in MySQL database server. So only connect the database server in local area network because of security purpose and fast accessing.

In Linux distribution MySQL configuration files are location in this path /etc/mysql/my.cnf.

In order to apply the --skip name –resolve switch in when starts MySQL, it’s a simple to add

Code:

Note: If need to activated, can you provide the option to one IP address in the MySQL grant all access tables.


2. MySQL database performance tuning

MySQL provide the configuration file to change the all memory usage value using SET. MySQL have to provide the default value for all buffer size by use mysqld command.

shell> mysqld --help
This command produces a list of all mysqld options and configurable variables. The output includes the default values and like this.

Possible variables for option --set-variable (-O) are:
back_log current value: 5
bdb_cache_size current value: 1048540
binlog_cache_size current value: 32768
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_binlog_cache_size current value: 4294967295
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
myisam_sort_buffer_size current value: 8388608
net_buffer_length current value: 16384
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
read_buffer_size current value: 131072
record_rnd_buffer_size current value: 131072
slow_launch_time current value: 2
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800

If have limited memory size in MySQL server.

shell> mysqladmin variables

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance.

The two most important variables are key buffer size and cache tables.
If have more than 256 MB of memory size
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O read_buffer_size=1M &

And need to set the Max connection max_connection = 500. This is because of most the connection establish a few sec.

3. Optimize the MySQL database response time

The most important to getting the system fast for the basic design of server.

Disk seeks. It an take a time to find the data in disk At a time more than 100 seeks to search the data. So need to spread the data in more than one disk, so easily server performance is increase to fetching the data fast.

Disk reading/writing: Write the data in parallel disk, so easily to search fast and fetch the data in multiple disks in parallel quickly.

Memory bandwidth: When the CPU needs more data than can fit in the CPU cache the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one should be aware of it.


4. Activate Query Cache

We’ve to lighten a bit the mysql database server's load.
By checking the Query cache memory:

mysql> SHOW STATUS LIKE 'Qcache%';

we could see that no query cache memory was left. It was neccessary to increase the query cache size.

Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:
Code:

-R.Gopi