Wednesday, October 21, 2009

Filled your hard disk with Mysql binary logs

When I'm running a program, I saw an error message "java.io.IOException: No space left on device". I wonder how I don't have enough hard disk space, because at the same day I cleaned 2GB. Then I just think about what are the things I have done during this period to kill my hard disk. I only ran sample SQL scripts to check the MySQL Horizontal partitioning feature as mentioned in my previous post. It inserts 8,000,000 X 2 records to database.

Once I checked the size of the MySQL directory I figure out it has 6.9GB and mysql/data directory occupies 6.5GB out of that. There are lots of "mysql-bin.*" files available in the data directory. Ohhh I have enabled the binary logs. So every update your doing, it will be logged in the binary log. This will help you to recover in case of a crash. Do I need to recover my TEST databases used in the projects? Since this is a development environment, then the answer is "NO". This is how you disable it and make your hard dick clean with out the unwanted binary logs.

1. Stop the mysql server : /etc/init.d/mysql stop
2. Comment out "#log-bin=mysql-bin" in my.cnf
If you can't find out where is your my.cnf is then use the following command to find out the file.
find / -type f -name "my.cnf" -print
3. Then delete your "mysql-bin" from data directory
Note: It would be safe if you can keep last 10 bin logs files and delete the rest.
4. Start the mysql server: /etc/init.d/mysql start

Even though the disabling of binary log is straight forward for most of the developers, most of them do not disable in there MySQL at development environment. Unintentionally our hard disk will fill with out notice. That's why I thought of highlighted this.

I got 5GB cleared from my hard disk and SQL queries get faster... Now I can store 7 movies downloaded from Torrent :))

MySQL Partitioning

One of the recent project I were assigned, I had to handle lots of event records and subscriber details(which is in Telco environment). So we looked at the database partitioning for subscriber profile by the number ranges and prepaid/post-paid way. For the event tables, it would be a weekly basis tables. If we create the tables structures according to the above logic and going to handle at the coding level, then the SQL queries get very complex. Then I found the a very effective way of handling the database partitioning at the database layer itself without bothering about what kind of tables which we are having at the coding level.

MySQL 5.1 provides a very stable feature called partitioning. It provides both Horizontal and Vertical partitioning.

Check this article: Improving Database Performance with Partitioning
This specified the performance improvement and how easy for a developer to work with the higher data load.

For Oracle DB partitioning: Oracle Partitioning Oracle9i Enterprise Edition

Even though the partitioning is a very rich feature, there are few limitations as well. If you are using with innodb please check the limitations before you use it, most importantly it does not support foreign keys.
Check this: Restrictions and Limitations on Partitioning

Sunday, October 18, 2009

Successful Architect....

Two groups Software architects Technical Architect and Marketing Architects
1. A Technical Architect is a technical person who will be look on technical path and do the technical architecture of a solution.
2. A
Marketing Architect is a business architect who do the business

I
nside every successful architect there should posses equally balanced Technical Architect and Marketing Architect skills.



Architect should....
1. Talk to the user(end users actually uses the system) knows what the user really wants
2. Talk to marketing and business development guys so they know business needs and marketing needs.
3. Talk to Technical/Engineering teams and identify the technical side of the products, codes.
4. Build the awareness of the code by working with the relevant teams.
5. Fill the gap between the technology and business.
6. Build usable software and adapt the frequent changes of business/ market needs.

Check this for more info: Beyond Software Architecture: Creating and Sustaining Winning Solutions

Sunday, February 15, 2009

MySQL start while booting the machine

su -

cd /usr/local/mysql/support-files/

cp mysql.server /etc/init.d/

cd /etc/init.d/

chmod +x mysql.server

mv mysql.server mysql

service mysql start

chkconfig --add mysql

Wednesday, January 28, 2009

Validating XML against Schema

Use following command to do it

xmllint --noout --schema schema.xsd file.xml

eg: Shell> xmllint --noout --schema ../xsd/CdrFile.xsd test.xml

Output>>>
test.xml validates

Thursday, January 8, 2009

Installing MySQL from tar.gz Packages on Unix-Like Systems

The basic commands that you must execute to install and use a MySQL binary distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &


Loging to MySQL server

shell> bin/mysql

Set permission

mysql>GRANT ALL ON *.* TO user@localhost IDENTIFIED BY "password";
GRANT ALL ON *.* TO user@"%" IDENTIFIED BY "password";


Then login again as

shell> bin/mysql -uuser -ppassword

Add a
add a symbolic link from mysql_home/bin/mysql to /usr/bin to access mysql from anyware from your command line

shell>
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

refere for more info : http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html