Fanatical Solutions

 

1. The client sends the SQL statement to the server.

• The protocol is halfduplex, which means that at any given time the MySQL server can be either sending or receiving messages, but not both. It also means there is no way to cut a message short.
• The client sends a query to the server as a single packet of data. This is why the max_packet_size configuration variable is important if you have large queries. Once the client sends the query, it doesn’t have the ball anymore; it can only wait for results. The response from the server usually consists of many packets of data.When the server responds, the client has to receive the entire result set. It cannot simply fetch a few rows and then ask the server not to bother sending the rest. If the client needs only the first few rows that are returned, it either has to wait for all of the server’s packets to arrive and then discard the ones it doesn’t need, or disconnect ungracefully. Neither is a good idea, which is why appropriate LIMIT clauses are so important.Here’s another way to think about this: when a client fetches rows from the server, it thinks it’s pulling them. But the truth is, the MySQL server is pushing the rows as it generates them. The client is only receiving the pushed rows; there is no way for it to tell the server to stop sending rows. The client is “drinking from the fire hose,” so to speak.

 

2. The server checks the query cache. If there’s a hit, it returns the stored result from the cache; otherwise, it passes the SQL statement to the next step.

• Before even parsing a query, MySQL checks for it in the query cache, if the cache is enabled. This operation is a case sensitive hash lookup. If the query differs from a similar query in the cache by even a single byte, it won’t match, and the query processing will go to the next stage.
• If MySQL does find a match in the query cache, it must check privileges before returning the cached query. This is possible without parsing the query, because MySQL stores table information with the cached query. If the privileges are OK,
• MySQL retrieves the stored result from the query cache and sends it to the client, bypassing every other stage in query execution. The query is never parsed, optimized, or executed.

3. The server parses, preprocesses, and optimizes the SQL into a query execution plan.

• MySQL’s parser breaks the query into tokens and builds a “parse tree”from them. The parser uses MySQL’s SQL grammar to interpret and validate the query. For instance, it ensures that the tokens in the query are valid and in the proper order, and it checks for mistakes such as quoted strings that aren’t terminated. The preprocessor then checks the resulting parse tree for additional semantics that the parser can’t resolve. For example, it checks that tables and columns exist, and it resolves names and aliases to ensure that column references aren’t ambiguous.Next, the preprocessor checks privileges. This is normally very fast unless your server has large numbers of privileges.
• The parse tree is now valid and ready for the optimizer to turn it into a query execution plan. A query can often be executed many different ways and produce the same result. The optimizer’s job is to find the best option. MySQL uses a  ost-based optimizer, which means it tries to predict the cost of various execution plans and choose the least expensive. The unit of cost is a single random four-kilobyte data page read.

4. The query execution engine executes the plan by making calls to the storage engine API.

• he parsing and optimizing stage outputs a query execution plan, which MySQL’s query execution engine uses to process the query. The plan is a data structure; it is not executable byte-code, which is how many other databases execute queries. In contrast to the optimization stage, the execution stage is usually not all that complex: MySQL simply follows the instructions given in the query execution plan. Many of the operations in the plan invoke methods implemented by the storage engine interface, also known as the handler API. Each table in the query is represented by an instance of a handler. If a table appears three times in the query, for example, the server creates three handler instances. Though we glossed over this before, MySQL actually creates the handler instances early in the optimization stage. The optimizer uses them to get information about the tables, such as their column names and index statistics.

5. The server sends the result to the client.

• The final step in executing a query is to reply to the client. Even queries that don’t return a result set still reply to the client connection with information about the query, such as how many rows it affected.

Reference: http://adminlinux.blogspot.com/2009/06/mysql-query-execution-basics.html

First query cache is new and added in MySQL v4.x.x version only so if you are using old version of MySQL server it will not work.

When MySQL server recives a request it will parse it and retrives data from database/table and sent back to client browser. If same query request (in case of dynamic content) comes repeatedly and server will just sent them result from cache (thus saving disk I/O and other associated cost with each query).
Please note that when data stored in table is modified, any related cached entries in the query cache are flushed.

How to check the global variable from mysql prompt

mysql> show variables like 'query_cache_size';

mysql> show variables like 'query%';

How do I find out my MySQL query cache is working or not…Very simple, MySQL provides the stats of same just type following command at mysql> prompt:

mysql> show status like 'Qcache%';

mysql> show status like 'qc%';

How to clear the query cache

mysql> reset query cache;
 

Mtop is a little perl utility that shows in real time statistics of your MySQL server. It allows you to quickly identify problems related to mysql poor performance, using an interface similar to the normal ‘top’ command.

To install mtop on RHEL We  needed to first satisfy a dependency (perl-Curses) that is not available in the regular RHEL packages. The quickest way to install perl-Curses is to use a build rpm.

Depending from your RHEL version download the respective package, perl-Curses-1.13-1.1.el3.rf.i386.rpm for RHEL3 i386, or perl-Curses-1.13-1.2.el4.rf.x86_64.rpm for RHEL4 on x86_64.

Optional in case you don’t have already perl-DBI and perl-DBD, then install them using

yum install perl-DBD-MySQL perl-DBI


The other requirements (Getopt::Long, Net::Domain) are normally part of the base install and are already available

 To install on your Red Hat 5 i386 servers I did the following:

wget http://apt.sw.be/redhat/el4/en/i386/rpmforge/RPMS/mtop-0.6.6-1.2.el4.rf.noarch.rpm
wget http://apt.sw.be/redhat/el4/en/i386/rpmforge/RPMS/perl-Curses-1.27-1.el4.rf.i386.rpm
rpm -Uvh mtop-0.6.6-1.2.el4.rf.noarch.rpm perl-Curses-1.27-1.el4.rf.i386.rpm
 

 The RPMs installed the binary file in the /usr/bin directory. To use the program as the MySQL root user you can type on the command line:

mtop --dbuser=root --password=<MySQL root password>

For more information on using mtop you can type ‘man mtop’ on the command line.
 

 

Substitute either word1 or word2 with a new word using regular expression

In the following example, it will translate any occurrences of either good or nice will be replaced with awesome.You can also do substitution by specifying regular expression.

Original Text: Linux is good. Life is nice.

:%s/\(good\|nice\)/awesome/g

Translated Text: Linux is awesome. Life is awesome.

Following example does the substitution of hey or hi to hai. Please note that this does not do any substitution for the words ‘they’, ‘this’.

:%s/\<\(hey\|hi\)\>/hai/g

• \< – word boundary.
• \| – “logical or” (in this case hey or hi)

Interactive Find and Replace in Vim Editor

You can perform interactive find and replace using the ‘c’ flag in the substitute, which will ask for confirmation to do substitution or to skip it as explained below. In this example, Vim editor will do a global find the word ‘awesome’ and replace it with ‘wonderful’. But it will do the replacement only based on your input as explained below.

:%s/awesome/wonderful/gc
replace with wonderful (y/n/a/q/l/^E/^Y)?

• y – Will replace the current highlighted word. After replacing it will automatically highlight the next word that matched the search pattern
• n – Will not replace the current highlighted word. But it will automatically highlight the next word that matched the search pattern
• a – Will substitute all the highlighted words that matched the search criteria automatically.
• l – This will replace only the current highlighted word and terminate the find and replace effort.

Substituting all lines with its line number.

When the string starts with ‘\=’, it should be evaluated as an expression. Using the ‘line’ function we can get the current line number. By combining both the functionality the substitution does the line numbering of all lines.

:%s/^/\=line(".") . ". "/g

Note: This is different from the “:set number” where it will not write the line numbers into the file. But when you use this substitution you are making these line number available inside the file permanently.

Substituting special character with its equivalent value.

Substituting the ~ with $HOME variable value.

Original Text: Current file path is ~/test/

:%s!\~!\= expand($HOME)!g

Translated Text: Current file path is /home/ramesh/test/

You can use expand function to use all available predefined and user defined variables.

Syntax of the text substitution inside vim editor:

:[range]s[ubstitute]/{pattern}/{string}/[flags] [count]

Following are three possible flags.

1. [c] Confirm each substitution.
2. [g] Replace all occurrences in the line.
3. [i] Ignore case for the pattern.

Substitute all occurrences of a text with another text in the whole file

This is the basic fundamental usage of the text substitution inside Vi editor. When you want a specific text to be replaced with another text in the entire file then you can use the following sequence.

:%s/old-text/new-text/g

%s – specifies all lines. Specifying the range as ‘%’ means do substitution in the entire file.

g – specifies all occurrences in the line. With the ‘g’ flag , you can make the whole line to be substituted. If this ‘g’ flag is not used then only first occurrence in the line only will be substituted.

Substitution of a text with another text within a single line

When you want a specific text to be replaced with another text within a single line in a case insensitive manner. Specifying no range means, do substitution in the current line only. With the ‘i’ flag, you can make the substitute search text to be case insensitive.

:s/I/We/gi

Substitution of a text with another text within a range of lines

With the range, you can make only a range of line to be affected in the substitution. Specifying 1, 10 as range means, do substitution only in the lines 1 – 10.

:1,10s/helo/hello/g

Substitution of a text with another text by visual selection of lines

You can also select a specific lines by visually selecting those lines. Press CTRL + V in command mode, use navigation keys to select the part of the file you want to be substituted. Press ‘:’ which will automatically formed as :’<,’> Then you can use the normal substitute as

:'<,'>s/helo/hello/g

Substitution of a text with another text only the 1st X number of lines

Using count in substitution, If you specify the count N in the substitution then it means do substitution in N lines from the current position of the cursor. do substitution in 4 lines from the current line.

:s/helo/hello/g 4

Substitute only the whole word and not partial match

Let us assume that you want to change only the whole word ‘his’ to ‘her’ in the original text mentioned below. If you do the standard substitution, apart from changing his to her, it will also change This to Ther as shown below.

Standard Subsitution

Original Text: This is his idea

:s/his/her/g

Translated Text: Ther is her idea

Whole Word Subsitution

Original Text: This is his idea

:s/\<his\>/her/

Translated Text: This is her idea

Note: You should enclose the word with < and > , which will force the substitution to search only for the full word and not any partial match

find .  -type f -daystart -mtime -10 -exec ls -l {} \; | cpio -pdmv /output/data

find /var/web/public_html  -type f -daystart -mtime -10 | cpio -pdmv /output/data

I have given an explanation of each flag used in the above command as well.

• find , linux command
• . & /home/web1/public_html , specifies the directory to search, in this case the website document root
• -type f , will only search for files and not directories
• -daystart , will tell find to start with today date
• -mtime, tells find to include every thing that was modified on and before a twenty fours hour period
• | , will redirect the output to another binary, in this instance cpio
• cpio, Creates and un-creates archived cpio files. And also is capable of copying files to things other than a hard disk.
• -pdmv,
  1. -p pass trough (copy in and out)
  2. -d make dirs
  3. -m preserve modification time
  4. -v verbose
• /root/test, is the output directory

For moving the files we can use the below command

find . -name '*.bak' -exec mv '{}' backup_dir/ \;

This would move every file found into backup_dir/.