Prabhat Kumar « ross beazley

Author Archive

MySQL : Calculate Query Executed per second

Saturday, May 8th, 2010

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Original Post by Prabhat Kumar

Here is a way to calculate Query Executed per second :

time=10
begin=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'`
sleep $time
end=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'`
diff=`expr $end - $begin`
avg=`expr $diff / $time`
echo "$avg"

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

Problem : CTRL+S in Putty

Wednesday, April 7th, 2010

Original Post by Prabhat Kumar

Over the years, we all have habit of using CTRL+S every few minutes during working on a document, because we all had too much work lost from stupid errors; In the Windows world, CTRL+S is used as the Save you work.
But, this habit will be a problem on working in the Linux world.

By accident while inside a terminal window (in PUTTY) we press CTRL+S, this accidental keystroke meant we had reconnect to my Linux server, kill whatever program we were running, and then start it again.

But here is solution :

CTRL+S actually does XOFF, which means the terminal will accept key strokes but won’t show the output of anything. It will appear as if your terminal is dead when it’s really just waiting to be turned back on. The fix? Simply press CTRL+Q to turn flow-control on (XON). If you pressed a whole bunch of keys before pressing CTRL+Q, you’ll see the output from those keystrokes.

Prabhat Kumar original post

Posted in Open Source, linux trick | No Comments »

memory-usage-free

Monday, March 22nd, 2010

Original Post by Prabhat Kumar

There is simple command to check for the overall usage of memory including physical memory and swap memory.

What you is just type free and it will shows you the information of the memory usage. for example:

total used free shared buffers cached
Mem: 16442272 16355460 86812 0 129600 10120444
-/+ buffers/cache: 6105416 10336856
Swap: 10223608 263608 9960000

The value shows at the first line of total column, is the total physical memory I have in kilobytes. I have 16GB ram, the results (16442272kb) shown above is approximate in kb. So the system have already used almost all the physically memory, which just left about 84M as free memory.

Out of 15.6G from 5.8G that stored in the system, is actually used. And it still left 9.8G in cache. I have total 10G of swap , and it uses 9.4GM and remain 9.4G unused.

The memory usage seems high but still consider healthy. I have loaded a lots of applications and uses some minor disc space for swap, but everything still running smooth without any lags.

Free have few options, if you feel that memory result shows in kb is not detail enough, you can have more accurate measurement in bytes. You can passes -b for bytes, or less details -m for Megabytes, -g for gigabytes.

free -b

You can ask free to display results in every 5 seconds, in order to track the increases/decreases on memory usage.

free -s 5

Prabhat Kumar original post

Posted in Open Source | No Comments »

Kontrollbase-MySQL monitoring tool

Friday, February 19th, 2010

Original Post by Prabhat Kumar

Kontrollbase - MySQL monitoring tool.
Past few days I am looking for a centralized MySQL database monitoring tool, Which will give complete details of MySQL database.
I have looked various tools but, Kontrollbase is given me the complete solution.

Kontrollbase is a monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases.

Important links:
software-kontrollbase
screenshots
demo Username: demo , Password: password
note: do not use IE, use Firefox or other browser.
downloads
Documentation

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

System details of Windows

Monday, February 15th, 2010

Original Post by Prabhat Kumar

Various time we required system (on Windows 2000, XP, 2003/8, Vista and Windows 7) details like hardware (motherboard, CPU , RAM , etc), installed software operating system’s security hotfixes and lots and lots of more.

One such solution is LookInMyPC – a free program that automatically generates a detailed audit report of the hardware and software on your computer. It records essential information such as, operating system and processor details, the amount of RAM installed, drive specifications, and much, much, more.

sample report

Prabhat Kumar original post

Posted in Open Source, windows | No Comments »

MySQL Tips : Verify database Objects

Friday, January 1st, 2010

Original Post by Prabhat Kumar

Verify database Objects:

1. Verify table.
a. SHOW TABLE STATUS LIKE '%user%'\G
b. SHOW TABLE STATUS from forum LIKE '%user%' \G

2. It will show you all tables have MyISAM engine.
a. SELECT TABLE_SCHEMA, table_name, table_type, engine From information_schema.tables where engine='MyISAM';

3. To know rows per table, you can use a query like this:
a. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'forum';

4. Verify you procedure / function.
a. SHOW procedure/FUNCTION STATUS LIKE ´hello´G
b. show create procedure/function hello\G

5. Routines
a. select routine_name from information_schema.routines; // list of all the routines in the system
b. select routine_name, routine_schema, routine_type from information_schema.routines; // This lists all of the routines in the system. With additional Information like the database the routines belongs too and also distinct between the different routines.
c. select routine_name, routine_schema, routine_type from information_schema.routines where routine_schema = database() // This time we can see just the routines for the currently selected database.

6. Triggers
a. select TRIGGER_SCHEMA,TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;
b. select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = database();

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

MySQL Tips : Calculate database and table size

Tuesday, December 29th, 2009

Original Post by Prabhat Kumar

Few MySQL Tips:

a. calculate databases size :
-> It will show database size

SELECT s.schema_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in("mysql","information_schema","test") GROUP BY s.schema_name order by Data_size DESC;

-> It will show database size along with table size.

SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in ("mysql","information_schema") GROUP BY s.schema_name,t.table_name,TABLE_ROW_FORMAT,ENGINE ORDER BY TABLE_ROWS DESC,data_size DESC,index_size DESC;

-> It will show table size of engine 'MEMORY'.

SELECT concat(table_schema,'.',table_name) as Database_Tablename, table_rows as Rows, concat(round(data_length/(1024*1024),2),'M') DATA, concat(round(index_length/(1024*1024),2),'M') idx, concat(round((data_length+index_length)/(1024*1024),2),'M') total_size, round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where ENGINE='MEMORY';

-> It will show 5 top tables using space.

SELECT concat(table_schema,'.',table_name) as Database_Tablename,
table_rows as Rows, concat(round(data_length/(1024*1024),2),'M') DATA,
concat(round(index_length/(1024*1024),2),'M') idx, concat(round((data_length+index_length)/(1024*1024),2),'M') total_size,
round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where ORDER BY data_length+index_length DESC limit 5;

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

Temporarily stop/start a process in linux

Wednesday, December 23rd, 2009

Original Post by Prabhat Kumar

Some time we have requirement that, particular job should stop for certain period of time and start again.

Most of us familiar with KILL command, but here is another feature of KILL command , which saves your life :-

#kill -STOP 10067 (where 10067 is process id)

#kill -CONT 10067 (where 10067 is process id)

have a fun :)

Prabhat Kumar original post

Posted in Open Source, linux trick | No Comments »

Extract single db/table from dump file

Wednesday, November 4th, 2009

Original Post by Prabhat Kumar

using sed to extract single database from the dumpfile:
=========================================
sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile > dbname.sql 2>error

eg:
sed -n '/^-- Current Database: `blogs`/,/^-- Current Database: `/p' dump.sql > blogs.sql 2>error

we can also use "awk" for the same.

extract single table from the dumpfile:-

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

nohup : commands keep executing even you exit from a shell prompt

Tuesday, October 27th, 2009

Original Post by Prabhat Kumar

nohup command if added in front of any command will continue running the command or process even if you shut down your terminal or close your session to machine

nohup command-name &

Where,

* command-name : is name of shell script or command name. You can pass argument to command or a shell script.
* & : nohup does not automatically put the command it runs in the background; you must do that explicitly, by ending the command line with an & symbol.

examples:

# nohup mysql -q -uUSER1 -pPASS1 < dump.sql > dump.log 2> error.log &

source:
http://www.idevelopment.info/data/Unix/General_UNIX/GENERAL_RunningUNIXCommandsImmunetoHangups_nohup.shtml

http://www.cyberciti.biz/tips/nohup-execute-commands-after-you-exit-from-a-shell-prompt.html

Prabhat Kumar original post

Posted in Linux, Open Source, linux trick | No Comments »

ross beazley

Author Archive

MySQL : Calculate Query Executed per second

Problem : CTRL+S in Putty

memory-usage-free

Kontrollbase-MySQL monitoring tool

System details of Windows

MySQL Tips : Verify database Objects

MySQL Tips : Calculate database and table size

Temporarily stop/start a process in linux

Extract single db/table from dump file

nohup : commands keep executing even you exit from a shell prompt

recent posts

Archives

Author Archive

recent posts

Tag Cloud

Archives