04 Jan

life at rest – my first poem

Life At Rest

You’d never forget the those beautiful moments when you feel loved and feel blessed with the presence of the one of your life.

Life At Rest

Looking at above capture of my dear ones, I jotted down a few lines and that’s my first attempt.

I call it (my) Life At Rest.

insomniac for your sleep,

you’re awesome when its deep,

pale skin, nighty hair,

mighty life and you’re so near…

warm breath, and your charm,

makes me wait, till you wake,

while you sleep, lemme gaze,

beauty like you and I awaits….

sun won’t rise, birdz won’t sing,

oh dear, u oughtta rest,

my life, I’m with u, won’t leave,

I know you will rise and I won’t laze..

its your time, waiting next door,

I have the keyz,letz go n open,

my life.. I am waiting..

life you’re resting, waiting..

I see u radiance, felicitous

you glow , weaknesses fading,

feeling u, i was waiting for u..

arise, arise and krex is with you… forever.

01 Jan

Choosing between MyISAM and INNODB – MySQL Storage Engines

After reading at a lot of places for the the single repeatative question, “What engine shall I choose – MyISAM or Innodb?”, this is what I’ve got.

Following are points of consideration for MyISAM – MySQL storage engine:

  • Tables are really fast for select-heavy loads
  • Table level locks limit their scalability for write intensive multi-user environments.
  • Smallest disk space consumption
  • Fulltext index
  • Merged and compressed tables.

Following are points of consideration for InnoDB – MySQL storage engine:

  • ACID transactions
  • Row level locking
  • Consistent reads – allows you to reach excellent read write concurrency.
  • Primary key clustering – gives excellent performance in some cases.
  • Foreign key support.
  • Both index and data pages can be cached.
  • Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state- No check / repair like MyISAM may require.
  • All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines.

Also consider:

  • Choose MyISAM for large constant tables or logging tables, relatively infrequent updates or Fast selects – these will not lock the table for the long time and thus it will not reduce performance.
  • Choose MyISAM if you really don’t need InnoDB.
  • Choose InnoDB storage engine when following is required:
  • Intensively updated tables – which can have many long selects running at the same time.
  • Multi-statement transactions Advanced isolation levels and row-level locking Foreign key constraints. Well we can say for regular usage: MyISAM is for speed and InnoDB for data integrity.
01 Jan

Perl Script for Analyze – Optimize – Repair Mysql Databases

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize and Repair you may ofcourse refer dev.mysql.com.

OPTIMIZE TABLE should be used if you have 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.

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk –recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables.

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk –analyze.

The perl script is mainly created to avoid manual Mysql Server Maintenance. The script uses Perl module DBI. You need to provide access credentials and database name(optional). Regarding Analyse, Optimize and Repair you may ofcourse refer dev.mysql.com.
OPTIMIZE TABLE should be used if you have 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.
REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk –recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables.
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk –analyze.

use DBI;
my $username;
my $password;
my $hostname;
my $dbName;
my $databasez;
my $withRepair = 0; #1=enabled ##Also checks and repairs the table if required.
$username='USERNAME';
$password='PASSWORD';
$hostname='HOSTNAME';
$dbName=''; #(Optional) considers all if left blank
$databasez='';
$dbName ='';
my $port = 3307;
my $dbh;


$dbh = DBI->connect("dbi:mysql:database=$dbName;host=$hostname;$port", trim($username), trim($password)) or die "$DBI::errstr";

if(length(trim($databasez)) == 0)
{
my $db_SQL="SHOW DATABASES;";
$sth=$dbh->prepare($db_SQL);
$sth->execute();

while($databasez = $sth->fetchrow_array())
{
if ((trim($databasez) eq "information_schema") or (trim($databasez) eq "mysql")) {
next;
}
tablesCount($databasez);
}
}
else {
tablesCount($databasez);
}

sub trim($)
{
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}

sub execute($)
{
my $sth_analyze_table = $dbh->prepare(shift);
$sth_analyze_table->execute() or $dbh->errstr;
my $status2 = $sth_analyze_table->fetchrow_array();
printf "%35s %10s %10s\n",$tablez,$status,$status2;
}

sub tablesCount($)
{
my $Line = "-----------------";
printf "\n%20s\n",$Line;
printf "\nDatabase:%35s\n",$databasez;
printf "\n%35s %10s %10s\n","Table Name" ,"Status", "Repair Status";
printf "\n%20s\n",$Line;
my $tables_SQL = "SHOW TABLES FROM $databasez;";
my $sth_table=$dbh->prepare($tables_SQL);
$sth_table->execute();
while($tablez = $sth_table->fetchrow_array())
{
my $analyze_tables_SQL = "ANALYZE TABLE $databasez.$tablez;";
my $optimize_table_SQL = "OPTIMIZE TABLE $databasez.$tablez;";
my $check_tables_SQL = "repair table $databasez.$tablez;";

execute($analyze_tables_SQL);
execute($optimize_table_SQL);

if ($withRepair == 1)
{
my $check_tables_SQL = "check table $databasez.$tablez;";
my $sth_check_table=$dbh->prepare($check_tables_SQL);
$sth_check_table->execute() or $dbh->errstr;
my $status = $sth_check_table->fetchrow_array();

if(trim($status) ne 'OK')
{
print "Attempting Repair: $databasez.$tablez \n";
my $check_tables_SQL = "repair table $databasez.$tablez;";
execute($check_tables_SQL);
my $status2 = $sth_check_table->fetchrow_array();
printf "%35s %10s %10s\n",$tablez,$status,$status2;
}
else
{
printf "%35s %10s %10s\n",$tablez,$status,"NA";
}
}
}
}

Download mysql-maintenance.pl

01 Jan

Using MySQLTuner – MySQL Optimization Tool

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.

Downloading MySQLTuner:

wget http://mysqltuner.com/mysqltuner.pl

chmod +x mysqltuner.pl

Using MySQLTuner Script for Lampp systems:

Just replace mysql & mysqladmin commands with respect lampp commands

vi mysqltuner.pl

Press Keys:- <ESC> and <:>

Enter:- 1,$s/\`mysql/\`\/opt\/lampp\/bin\/mysql/g

Running mysqltuner and obtaining the performance analysis:

Shell> ./mysqltuner.pl

Following is the Sample Output from mysqltuner script::

>>  MySQLTuner 1.0.0 – Major Hayden

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with ‘–help’ for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

——– General Statistics ——————————–

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.27-log

[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM

——– Storage Engine Statistics ————————-

[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 33G (Tables: 1013)

[–] Data in ARCHIVE tables: 22B (Tables: 1)

[!!] Total fragmented tables: 71

——– Performance Metrics ——————————-

[–] Up for: 22d 7h 48m 16s (2B q [1K qps], 557K conn, TX: 1B, RX: 3B)

[–] Reads / Writes: 15% / 85%

[–] Total buffers: 1.2G global + 12.3M per thread (100 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 2.4G (59% of installed RAM)

[OK] Slow queries: 0% (7K/2B)

[OK] Highest usage of available connections: 25% (25/100)

[OK] Key buffer size / total MyISAM indexes: 1.0G/4.6G

[OK] Key buffer hit rate: 99.7% (8B cached / 30M reads)

[OK] Query cache efficiency: 49.2% (1M cached / 2M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (207 temp sorts / 49M sorts)

[OK] Temporary tables created on disk: 9% (46K on disk / 511K total)

[OK] Thread cache hit rate: 99% (269 created / 557K connections)

[!!] Table cache hit rate: 0% (64 open / 1M opened)

[OK] Open file limit used: 10% (112/1K)

[OK] Table locks acquired immediately: 99% (224M immediate / 224M locks)

——– Recommendations ———————————–

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

table_cache (> 64)

Using MySQLTuner – MySQL Optimization Tool
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.
Downloading MySQLTuner:
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
Using MySQLTuner Script for Lampp systems:
Just replace mysql & mysqladmin commands with respect lampp commands
vi mysqltuner.pl
Press Keys:- <ESC> and <:>
Enter:- 1,$s/\`mysql/\`\/opt\/lampp\/bin\/mysql/g
Running mysqltuner and obtaining the performance analysis:
Shell> ./mysqltuner.pl
Following is the Sample Output from mysqltuner script::
>>  MySQLTuner 1.0.0 – Major Hayden
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
——– General Statistics ——————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.27-log
[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM
——– Storage Engine Statistics ————————-
[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 33G (Tables: 1013)
[–] Data in ARCHIVE tables: 22B (Tables: 1)
[!!] Total fragmented tables: 71
——– Performance Metrics ——————————-
[–] Up for: 22d 7h 48m 16s (2B q [1K qps], 557K conn, TX: 1B, RX: 3B)
[–] Reads / Writes: 15% / 85%
[–] Total buffers: 1.2G global + 12.3M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.4G (59% of installed RAM)
[OK] Slow queries: 0% (7K/2B)
[OK] Highest usage of available connections: 25% (25/100)
[OK] Key buffer size / total MyISAM indexes: 1.0G/4.6G
[OK] Key buffer hit rate: 99.7% (8B cached / 30M reads)
[OK] Query cache efficiency: 49.2% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (207 temp sorts / 49M sorts)
[OK] Temporary tables created on disk: 9% (46K on disk / 511K total)
[OK] Thread cache hit rate: 99% (269 created / 557K connections)
[!!] Table cache hit rate: 0% (64 open / 1M opened)
[OK] Open file limit used: 10% (112/1K)
[OK] Table locks acquired immediately: 99% (224M immediate / 224M locks)
——– Recommendations ———————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 64)


-- Kedar Vaijanapurkar --