Thursday, December 27, 2012

How to estimate SQL query timing?

MySQL has a profiling tool. When you open a MySQL session, you could set the variable "profiling" to 1 or ON.
 
mysql> SET profiling = 1;

So, all the statements sent to the server will be profiled and stored in a historical and shown later with the command:
 
mysql> SHOW PROFILES;
See, from MySQL manual:
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+

Thursday, December 20, 2012

Unix Command Summary

Command   Description
 ac
ac prints  out  a  report  of connect time, in hours, based on the logins/logouts in the current wtmp file.  A total is also printed out.
 alias
alias with no arguments or with the -p option prints the list of aliases in the form alias name=value on standard output.  When arguments are supplied, an alias is defined for each name whose value is given.  A trailing space in value causes the next word to be checked for alias substitution when the alias is expanded.  For each name in the argument list for which no value is supplied, the name and value of the alias is printed.   alias returns true unless a name is given for which no alias has been defined.
 at
at and batch read commands from standard  input  or  a  specified  file which are to be executed at a later time. at  executes commands at a specified time.  atq lists  the  user’s  pending  jobs, unless the user is the superuser; in that case, everybody’s jobs are listed.   The format of the output lines, one for each job,is: Job number; date;hour; and job class.  atrm deletes jobs identified by their job number.  batch executes commands when system  load  levels  permit;  in  other words,  when  the  load  average  drops below 0.8, or the value specified in the invocation of atrun.
 awk
Gawk is the GNU Project’s implementation of the AWK programming language. Gawk - pattern scanning and processing language
 basename
Print name with any leading directory components removed.  If specified, also remove a trailing suffix.
 bc
bc is a calculator that supports arbitrary precision numbers with interactive execution of statements.
 bg
Resume the suspended job jobspec in the background, as if it had been started  with  &.
 break
Exit from within a for, while, until, or select loop regardless of the loop condition.
 cal
cal displays a simple calendar.  If arguments are not specified, the current month is displayed.
 cancel
Cancel jobs
 cat
Concatenate files and print on the standard output
 chdir
chdir changes the current directory to that specified in path.
 chfn
chfn is used to change finger information.   This information is stored in the /etc/passwd file, and is displayed by the finger program.
 chgrp
Change the group membership files.
 chmod
Change file access permissions
 chown
Change file owner and group
 chsh
Change the user’s login shell
 cksum
Checksum and count the bytes in a file
 clear
Clears the screen, if possible.
 cmp
The cmp utility compares two files of any type and writes the results to the standard output.
 col
Filter reverse line feeds from input
 comm
Compare two sorted files line by line
 compress
compress, uncompress, zcat - compress and expand data
 continue
Resume the next iteration of the enclosing for, while, until, or select loop.
 cp
Copy files and directories
 cpio
Copy files to and from archives
 crontab
Maintain crontab files for individual users
 csplit
Split a file into sections determined by context lines
 ctags
Generate tag files for source code
 cut
Remove sections from each line of files
 date
Print or set the system date and time
 df
Report filesystem disk space usage
 diff
Find differences between two files
 du
Estimate file space usage
 echo
Display a line of text
 ed
Ed is a line-oriented text editor.  It is used to create, display, modify and manipulate text files.
 egrep
Grep, egrep, fgrep - print lines matching a pattern
 emacs
Text editor
 env
Run a program in a modified environment
 exit
Cause  the  shell  to exit
 expand
Convert tabs in each file to spaces, writing to standard output.
 expr
Evaluate and print  the  value of expression to standard output
 false
Exit with a status code indicating failure.
 fg
Resume job in the foreground, and make it the  current  job
 fgrep
Grep, egrep, fgrep - print lines matching a pattern
 file
Determine file type
 find
Search for files in a directory hierarchy
 finger
User information lookup program
 fmt
Simple optimal text formatter
 fold
Wrap each input line to fit in specified width
 ftp
Basic file transfer program
 getfacl
Get file access control lists
 grep
Grep, egrep, fgrep - print lines matching a pattern
 groupadd
Create a new group
 groupdel
Delete a group
 groupmod
Modify a group
 gunzip
Expand compressed files
 gzip
Compress files
 halt
halt, reboot, poweroff - stop the system
 hash
For each name, the full file name of the command  is  determined by searching the directories in $PATH and remembered
 head
Output the first part of files
 help
Display  helpful information about builtin commands
 history
Display recently executed commands
 hostname
Show or set the system’s host name
 id
Print information for USERNAME, or the current user
 ifconfig
Display or configure the kernel-resident network interfaces
 jobs
Lists the active jobs
 join
Join lines of two files on a common field.  For  each  pair of input lines with identical join fields, write a line to standard output
 kill
Terminate a process
 ld
Linker.  ld  combines a number of object and archive files, relocates their data and ties up symbol references
 ldd
Print shared library dependencies
 less
less is a program similar to more , but it allows backward  movement in the file as well as forward movement
 lex
Used for pattern matching on text
 ln
Make links between files
 locate
Security Enhanced version of the GNU locate.  It provides a secure way to index and quickly search  for files on the system
 login
login is used  when  signing  onto  a system.  It can also be used to switch from one user to another at any time
 logname
Print the name of the current user
 logout
Exit a login shell
 lp
Submits files for printing or alters a pending job
 lpadmin
Configures printer and class queues provided by CUPS. It can also be used to set the server default printer or class.
 lpc
Provides limited control over printer and class queues provided by CUPS. It can also be used to query the state of queues.
 lpq
Shows  the  current print queue status on the named printer
 lpr
Submits files for printing
 lprm
Cancels  print  jobs  that  have been queued for printing
 lpstat
Displays status information about the current classes, jobs, and printers
 ls
List directory contents
 mail
Send and receive mail
 make
The purpose of the make utility is  to  determine  automatically  which pieces of a large program need to be recompiled and issue the commands to recompile them
 man
Formats and displays the on-line manual pages
 mesg
Controls  the write  access to the user’s terminal by others
 mkdir
Make directories
 more
more is a filter for paging through text one screenful at a time
 mount
Mount a file system
 mt
Control magnetic tape drive operation
 mv
Move or rename files
 neqn
Format equations for ASCII output
 netstat
Print network connections, routing tables, interface statistics, masquerade connections, and multicast memberships
 newgrp
Log in to a new group
 nice
Run a program with modified scheduling priority
 nohup
Run a command immune to hangups, with output to a non-tty
 nslookup
Query Internet name servers interactively
 passwd
Update a user’s authentication tokens(s)
 paste
Merge lines of files.  It write  lines  consisting  of  the sequentially corresponding lines from each file, separated by tabs, to standard output
 perl
Practical Extraction and Report Language
pgrep Looks through the currently running processes and lists the  process  ids which match the selection criteria to stdout
 ping
Uses the ICMP protocol’s mandatory ECHO_REQUEST datagram to elicit an  ICMP  ECHO_RESPONSE from a host or gateway
 pkill
Will send the specified signal, by default SIGTERM, to each process instead of listing them on stdout
 poweroff
Halt, reboot, poweroff - stop the system
 pr
Paginate or columnate file(s) for printing
 printf
Format and print data to the standard output
 ps
Report process status
 pwd
Print name of current/working directory
 rcp
Remote file copy … copies files between machines
 reboot
Halt, reboot, poweroff - stop the system
 red
ed is a line-oriented text editor.  It is used to create, display, modify and otherwise manipulate text files.  red is a  restricted ed:  it can  only  edit files in the current directory and cannot execute shell commands.
 rlogin
Remote login
 rm
Remove files or directories
 rmail
Handle remote mail received via uucp
 rmdir
Remove empty directories
 rpcinfo
Report remote procedure call (RPC) information.  It makes a RPC call to a RPC server and reports what it finds.
 rsh
Remote shell
 s2p
Psed - A stream editor that reads the input stream consisting of the specified files (or standard input, if none are given), processes it line by line by applying a script consisting of edit commands, and writes resulting lines to standard output.
 sar
Collect, report, or save system activity information
 script
Makes a typescript of everything printed on a terminal
 sdiff
Find differences between two files and merge interactively
 sed
A stream editor.  A stream editor is used to perform basic text transformations on an input stream
 sendmail
An electronic mail transport agent that sends a message to one or more recipients, routing the message over whatever networks are necessary
 set
Display, set or unset shell attributes.
 setenv
Change or add an environment variable
 setfacl
Set file access control lists
 sh
The bourne shell.  bash is an bourne-compatible command language interpreter that executes commands read from the standard input or from a file.
 shutdown
Brings the system down in a secure way.  All logged-in users are notified that the system is going down, and login is blocked.
 sleep
Delay for a specified amount of time or pause for number of seconds
 sort
Sort lines of text files
 split
Split a file into pieces
 stty
Change and print terminal line settings
 su
Change the effective user id and group id to that of another user
 sudo
Execute a command as another user.  It allows a permitted user to execute a command as the superuser or another user, as specified in the sudoers file
 sysinfo
Returns information on overall system statistics
 tail
Output the last part of files
 tar
The tar archiving utility
 tee
Read from standard input and write to standard output and files
 telnet
The  telnet  command is used to communicate with another host using the TELNET protocol
 time
Time a simple command or give resource usage
 touch
Update  the  access  and modification times of each file to the current time
 tput
Initialize a terminal or query terminfo database
 tr
Translate, squeeze, and/or delete characters from standard input, writing to standard output
 traceroute
Print the route packets take to a network host
 troff
The troff processor of the groff text formatting system
 ul
Do underlining
 umask
Set the user file-creation mask
 unalias
Remove  a  named alias  from  the list of defined aliases
 uname
Print certain system information
 uncompress
Expand compressed data
 uniq
Remove duplicate lines from a sorted file
 useradd
Create a new user or update default new user information
 userdel
Delete a user account and related files
 usermod
Modify a user account
 vi
A powerful text editor.
 view
Start the vi editor in read-only mode
 wait
Wait for the specified process and return its termination status.
 wc
Print byte, word, and newline counts for each file, and a total line if more than one file is specified.
 whereis
Locate the binary, source, and manual page files for a command
 which
Shows the full path of shell commands

 
 who
Show who is logged on
 whois
whois searches Whois servers for the named object.
 X
The X Window System is a network transparent window system which runs on a wide range of computing and graphics machines
 xfd
Display all the characters in an X font
 xlsfonts
Server font list displayer for X
 xrdb
X server resource database utility
 xset
User preference utility for X
 xterm
The xterm program is a terminal emulator for the X Window System
 zcat
Compress or expand files