Some limitations of partitioning in MySQL 5.1 on one page. All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.These rules also apply to existing nonpartitioned tables that you wish to partition. Partitioning applies to all data and indexes of a table; … Continue Reading
Perl script for checking MySQL database disk space usage (in GB)
Perl script for checking MySQL database disk space usage (in GB) #!/usr/bin/perl # database size verification use DBI; use Getopt::Long; #Commandline arguments #my @cmdline = @ARGV; #my $counts =@cmdline; sub usage { print STDERR @_, “n” if @_; die <<EOF; Usage: [ options ] Options: -?, –help show this help -h, –host=… connect to host … Continue Reading
Clear command doesn't work for clear the screen in MySQL
I was very much confused when I have tried to clear the screen of MySQL and I didn’t got any result. root@nilnandan-Satellite-A200:~# mysql -uroot -p Enter password: Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement. mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | … Continue Reading
How FLUSH TABLES works in MySQL
“FLUSH TABLES” is generally used for closing all tables by force in MySQL. Its really interesting that how mysql do this. Most of time we are doing this because someone adds new tables outside of MySQL. (Like for MyISAM tables, we are copying .FRM, .MYD and .MYI files. We can do this when MySQL is … Continue Reading
How caches used in MySQL
Following caches are generally available in MySQL. Key Cache This is a shared cache for all B-tree index blocks which are in different MYI files. Internally, this cache is using hashing and reverse linked lists algorithm for quick caching for most recently used blocks and flushed the blocks which are not recently used or some … Continue Reading
How MySQL does sorting with Order By
There are two filesort algorithms in MySQL for sorting and retriving select queries results. 1. Original Filesort Algorithm : This method uses only the ORDER BY columns. (prior to MySQL 4.1) 2. Modified Filesort Alforithm : This method uses ORDER BY columns and the columns which are used in query. (MySQL 4.1 and newer version) … Continue Reading
Postgres Plus 8.4 vs. MySQL 5.5
Nice comparison by EnterpriseDB. http://get.enterprisedb.com/whitepapers/Postgres_Plus_8.4_vs_MySQL_5.5.pdf Below Topics are included in this comparison. General Comparison Commentary Core Database Features Database Capacities Transaction Programming and Control Query Capabilities Business Intelligence and Data Warehousing Network/Distributed Services Bulk Data Management/Protection Security Features DBA Tools Developer Functionality Scalability Solutions High Availability Solutions Here, Read more [...]
Why server id important in MySQL Replication
Set server id in my.cnf is one of the necessary steps to implement or start replication. The general rule is, server id must be unique across the environment where we are implementing MySQL replication. Generally while implementing multi-master/circular replication, we are setting option –log-slave-updates= 1. so when any statement of master will be executed on … Continue Reading