The F - Technology

Deleting MySQL Database log files

2010-05-04T04:05:00.001-07:00

We have a master database and its slave database running on different nodes. The master DB is subjected to HUGE no. of inserts/updates because of which the log files written by it grew by enormous proportions (log files at one time occupied more than 80% of the disk space).

Log files are important because using them we can track the changes made to the DB and in case of any DB crash, we can rebuild it again from the scratch. But in our case, we didn't require the log files and had the luxury to get rid of them instead of finding a reliable storage to keep them safe.

We decided to go ahead with deleting the log files. We automated this process by setting a flag in configuration file so that MySQL automatically deleted the log files which are older than 90 days.

This can be done as follows:

1) Stop the Master MySQL server.

2) Add the follwing parameter to the my.cnf file (here the figure on the right hand side is the no. of days after which the log file must be cleared)

expire_logs_days = 90

3) Start the server.

Simple !!!

Incase, you just need to delete the log files without automating the process then it can be done using the following command (use desired date value):

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

Since the replication node uses the log files, we need to make sure we only delete files that were created before the logfile that the slave node is currently reading. We can get this data by using the following command on the slave database:

show slave status;

This command gives the details about the slave. The value of the column 'Master_Log_file' is the name of the log file currently being read by the slave. Once we have the name of this file, we can get its creation date. Lets assume the creation date was '2010-05-03 14:56:23'. All log files created before this time can be deleted with the following command

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

References:
MySQL v5 documentation for purging log files

Move MySQL database location

2009-07-02T00:18:00.000-07:00

MySQL DB might using some location as data repository where hard-disk space is limited. This will warrant moving the data from that location to some other location which can provide more room to accomodate the increasing data. Following steps can move this data to the new location:

du -h : will show the memory parition of the current folder

--------------------

-->> Set the password for mysqlmanager (required if password is not set already).
mysqlmanager --passwd >> /etc/mysqlmanager.passwd

--> log into mysql manager
mysql -uroot -psh*pp@r --socket=/tmp/mysqlmanager.sock

-->> kill all instances of mysql
ps afx | grep mysql //get process ids
kill -9 4465 4473 4474 // kill those processes

-->> create the directory where you want to transfer the files
mkdir /mnt/mysql/marketingportal

-->> Give mysql the ownership of the directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

--> Open the folder where the current data is
cd mysql-marketingportal/

--> Copy this data to the new folder
cp -R * /mnt/mysql/marketingportal/

-->> Give mysql the ownership of the new directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

-->> Change the name of the old directory (optional - this is done to make sure that it isn't beign used any longer)
mv mysql-marketingportal/ mysql-marketingportal-old/

-->> edit the my.cnf file
change the value of datadir

-->> Start the mysqlmanager
nohup mysqlmanager --user=mysql > mysqlmanager.out &

High Performance Database Tips

2009-02-22T09:32:00.000-08:00

Some Database mantras discovered on the basis of practical experience.

Use the smallest datatype possible. eg. where a integer can take care of all possible states, don't use bigint. Every bit of extra space alloted will make the DB to grow in size without any actual utility, and there by taking toll on the performance
Choose what to index and how much to index. Indexing is creating a meta data of data. If data is being fetched on the basis of a particular attribute, make that attribute an index. It would will definitely boost-up the reads and updates. Also, keep in mind that indexing increases the insert time and these meta-data information do occupy disk space. Partial Indexing is a good way to create indexes not the on the complete field, but the deciding part of it. eg. Index can be created for the first few character's URLs if data to be fetched is on the basis of host name.
Choose storage engine wisely. eg. If the DB is not relational, go for MyISAM. Read the pros-cons of each engine before choosing one.
Controlled Data Redundancy. Higher the normalisation of a DB, lesser is the redundancy. But, one think to keep in mind is that retrieving data from such a DB might require joining of tables, which is a very expensive operation. If the size of DB is huge or if the data to be fetched is distributed across a significant no. of tables, then performance will take the hit. One has to make a educated choice between space and performance. My experience tells me that a bit of redudancy isn't that bad !
Fetch only relevant data. If you require some fields of a table(s), retrieve only those fields. Don't do a 'select *'.
Better programming logic: When programitically accessing DB use reuse connections. eg. If on the same DB schema, we need to fire 2 quries to retrive logical/related set of data, try using the same connection. Creating connection is costly. Also, Make sure to close all connections when use is over.
Don't run all servers on the same node. If application receives a huge no. of hits, and also tremendous DB operations are taking place in the DB (like huge no. of inserts/read/writes), its better to host the application on a node diffrerent from the DB. There is only so much a single node can handle.

Write SQL query output on file

2009-01-22T22:25:00.001-08:00

Query into outfile 'filename';

Query - query to be fired
filename - name of the file on which the data is to be written. Full path can also be given. Make sure that the folder is already in existence.

eg. select * from HOTEL_DETAILS into outfile 'C:/datafolder/sqldata.txt';

Further Reading:
http://gilfster.blogspot.com/2005/11/writing-sql-results-to-file.html

MySQL Table and Schema Size

2008-12-03T22:18:00.000-08:00

I found these 2 commands for finding the size of a table or whole schema on MySQL mailing list (unable to recollect the URL)

Query To Find the size of a schema

#Query to find the size of a database schema (replace YOUR_SCHEMA_NAME with appropriate name)

#Run this query without selecting any schema

SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, 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 = "YOUR_SCHEMA_NAME" GROUP BY s.schema_name ORDER BY pct_used DESC\G;

-------------------------------------------------------

Query To Find the size of a table

# Query to find size of a particular table (replace YOUR_TABLE_NAME with appropriate name)

# Run this after choosing the schema

show table status like 'YOUR_TABLE_NAME'

Get log files from S3 that were generated within a given timeframe

2008-09-18T21:54:00.000-07:00

Amazon S3 log files are named as:

TargetPrefix-YYYY-MM-DD-HH-MM-SS-UniqueNumber

TargetPrefix is what we chose
YYYY-MM-DD-HH-MM-SS is the timestamp
UniqueNumber is something we should not care about

S3 returns a list of Objects (files) in S3 bucket if give give them the desired prefix.

Now, to get a list of log files that were created within a specific timeframe, we can use the following Algo:

beginTime : YYYY-MM-DD-10:25:15
endTime 13:00:00

1) get all files with prefix YYYY-MM-DD-10-25-16 to YYYY-MM-DD-10:25:59
2) get all files with prefix YYYY-MM-DD-10-26 to YYYY-MM-DD-10:59
3) get all files with prefix YYYY-MM-DD-12
3) get all files with prefix YYYY-MM-DD-13

One can further optimise the algorith to get files on 'day of the month' basis. But, that would not be a good idea because the list of files received from S# will be very long.

Following is a Java method for the implementation of the above logic


/**
 * Retuns a List of targetprefix. List is from (beginTime + 1 second) to the
 * hour value of endtime. Minute and second value of endTime is ignored.
 * 
 * @param dbLogTime
 * @param beginTime
 * @return
 */
public List getPrefixList(Timestamp beginTime, Timestamp endTime, String targetPrefix) {
 List prefixList = new ArrayList();

 SimpleDateFormat sdfhr = new SimpleDateFormat("yyyy-MM-dd-HH");
 SimpleDateFormat sdfmin = new SimpleDateFormat("yyyy-MM-dd-HH-mm");
 SimpleDateFormat sdfsec = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");

 Calendar beginCal = Calendar.getInstance();
 beginCal.setTimeInMillis(beginTime.getTime());
 beginCal.set(Calendar.MILLISECOND, 0);

 Calendar endCal = Calendar.getInstance();
 endCal.setTimeInMillis(endTime.getTime());
 endCal.set(Calendar.MINUTE, 0);
 endCal.set(Calendar.SECOND, 0);
 endCal.set(Calendar.MILLISECOND, 0);

 if (endCal.compareTo(beginCal) < 0) {
  logger.error("Error: Current Time cannot be less than the time in LAST_PROCESS_TIME. currentCal: "
    + sdfsec.format(endCal.getTime()) + ". lastProcessTime: " + sdfsec.format(beginCal.getTime()));
  return prefixList;
 }

 beginCal.add(Calendar.SECOND, 1);
 while (!(beginCal.get(Calendar.SECOND) == 0)) {
  prefixList.add(targetPrefix + "-" + sdfsec.format(beginCal.getTime()));
  beginCal.add(Calendar.SECOND, 1);
 }

 if (endCal.compareTo(beginCal) > 0) {
  while (!(beginCal.get(Calendar.MINUTE) == 0)) {
   prefixList.add(targetPrefix + "-" + sdfmin.format(beginCal.getTime()));
   beginCal.add(Calendar.MINUTE, 1);
  }
 }

 while (endCal.compareTo(beginCal) >= 0) {
  prefixList.add(targetPrefix + "-" + sdfhr.format(beginCal.getTime()));
  beginCal.add(Calendar.HOUR_OF_DAY, 1);
 }

 return prefixList;
}

Ignore log4j logger statements for specific classes

2008-09-11T02:38:00.000-07:00

We use certain 3rd party libraries and while development, we might want to log statements that has been generated from our application and not the third party library.

To set the log level of log requests from such libraries, put the following snippet in log4j.xml file.


<logger name="org.thirdpartlib">
    <!-- Print only messages of  level warn or above in the package <span style="font-style: italic;">thirdpartlib</span>-->
      <level value="WARN"/>
</logger>

Replace org.thirdpartylib with appropriate value. That should be do the magic.

PS: org.thirdpartlib.* won't work.

MySQL database dump

2008-08-31T22:38:00.000-07:00

How to take MySQL database dump:

mysqldump -hhostname-uusername -ppassword schemaname tablename (contents will be printed on the console)

mysqldump -hhostname -uusername -ppassword schemaname tablename> filename (contents will be written on file)

hostname: name of the host. If the db is on your local system, then this might not be needed

schemaname: name of the schema which you want to back up

tablename: name of the table which you want to back up

filename: name of the file which will hold the dump. You can specify the location of the file too. Eg. C:\sql\testsqldump.sql. If 'sql' directory doesn't exist and error will be generated.

eg. mysqldump -uroot -padmin userinfo_schema USER_TABLE > D:\sql\userinfo.sql

If you don't provide the tablename, then the dump of whole schema will be taken.

To take dump of all the schema
mysqldump -uroot -padmin --all-databases > filename

To populate the MySQL database:

mysql -uusername -ppassword schemaname < filename
filename: name of the file that contains the mysqldump

eg. mysql -uroot -padmin userinfo_schema < D:\sql\userinfo.sql

TaF Public release

2008-07-31T22:57:00.000-07:00

We are now releasing Tell-A-Friend widget for public use.

One of the features that makes this widget stand apart from rest of the widgets is that, if you wish to change the design of the buttons (be it the color, size or appearance), you don't have to embed a new or modified code in your webpage/blog. This helps save a lot of copy-paste exercise, which is kinda a welcome feature for lazy bums like me. All one has to do it to go to the accounts page, choose the kinda button they like, save the changes and then its all Eureka. All the changes are instantly reflected in the blogsite/webpage where TaF is embedded. Now, isn't this something cool ??

Apart from wiring a few connectors in the TaF widget, I worked on the analytics collection module. This was a pretty good learning exercise. I got to know more about the Apache log4j utility. These Apache guys are real bonds. They write some pretty good stuff.

For analytics collections, I wrote a custom JDBC appender to log all data. This has many advantages.

1) It consumes only a limited database connections.
2) Requests are served on first-come-first-serve basis. So, everything is sequential in nature in accordance to time. (log4j uses a queue)

But then, there are possible pitfalls too !!
Imagine that there are many requests, this will increase the queue size, thus increasing the response time. As of now, we have tested our infrastructure for traffics much more than we are expecting. If anyhow, response time does gets a beating, switching to a distributed system would be a possible solution which I suppose would require a bit of tweaking.

May be I will soon write another post on how to write custom JDBC appender and on analytics collection framework of works.

Tell-a-Friend

2008-05-30T07:16:00.000-07:00

Tell-a-Friend or TaF (as we developers call it) is a new product from Pramati Technology, Hyderabad

Its something to aid 'spread the word around the world'. Just click on it and instantly  announce things to your near & dear ones. So, you can announce your blog posts, articles, products on sale etc. through IM, Email, Blogs and Social Networks(like Facebook and Twitter) through a single integrated interface. So, its kinda like a single window to  multiple channels to make a million announcements. So, it will bring more clicks to your weblog / website and help market your articles/products.

When Sunny showed me the TaF button embedded in Nina's blog (ya...thats our test blog), I could see foretell that this product could actually become a rage. Intel Mashmaker, CatchFlix and BigScreen are already using it/used it. But, now any one  can integrate TaF in their website/blog for the following 2 divine reasons:
1) The free version is absolutely free :P (and one day beer shall also be free)
2) All it takes is embedding a code snippet in the page where the TaF icon is to be displayed.

Hope I see the TaF icons weblogs alongside dig.com, stumble upon etc. icons. After all, it always feels nice to see people use and talk about something that has your code :P.

TaF extensively employs JAVA, JAVASCRIPT, DOJO and AJAX.  The widget opens up in an iframe and seemlessly gives a web2.0 feel to the page. We are working to further boost its performance by using CSS sprites, decreasing the number of http request/response and fine-tuning the backend. A rework on its look-and-feel is also being undertaken. So, with time, its gonna become all the more difficult to ignore it and impossible to resist not having it on your page :)  

JSON and HashMap

2008-01-21T09:53:00.000-08:00

DWR is used for easy 'AJAX for JAVA' and is employed to call Java methods from Javascript (version 2.0 + supports reverse-AJAX too).

Working on the ShopprStream(earlier known as WEB122) has helped me get acquainted with many facets of web-development.

Now I have a Java method that returns a generic HashMap object ;
For this example lets consider that the key is of type String
And the elements are instances of the class CustomClass with name (of type String) as instance variable

The DWR call will return a JSON object. This is how we can access the data


for (var key in hashMap)
{
     //if not using Firebug, comment out console.log statements
                        
     //'key' is the key in the HashMap
     console.log("key : "+ key);
          
    //this will give the no. of elements stored corresponding to the 'key'
    console.log("hashMap[key].length: "+ hashMap[key].length);
                 
    //Access individual elements corresponding to the 'key'
    for(var i=0; i<hashMap[key].length; i++)
    {
    console.log("hashMap[key][i].name: " + hashMap[key][i].name);
    }
}

If the HashMap contains custom objects, make sure it has a corresponding converter entry in the dwr.xml file

for example, if my class is called CustomClass, then write as

<convert converter="bean" match="com.pramati.web122.imsupport.ContactDetail"/>

Here, abc.xyz is the package structure

If object returned is hashmap, then values can be accessed as:
hasMap[key]

If object returned is hashmap, then values can be accessed as:
hasMap[key][0], hasMap[key][1], hasMap[key][2] . . . . .

Helpful JavaScripts

2008-01-17T02:43:00.000-08:00

To bring focus to a text box / button or any other element

just add this javascript in the HTML code:


<script type="text/javascript">
   document.getElementById("elementId").focus();
</script>

where elementId the id of the element on which the focus is to be set

for example, the following code will bring focus to the textbox that has the id email

<input type="text" id="email" />
<script type="text/javascript">
   document.getElementById("email").focus();
</script>

Fake Mail

2007-12-05T21:11:00.000-08:00

One can send mails using JAVA Mail API. It is fairly simple to use this library to send mails.

First download the JAVA Mail API, and add mail.jar to your classpath
Then download, JAF API, and add activation.jar to your classpath

Following is the simple code:

Java Class: TestMail.java

import java.io.*;
import java.util.Properties;
import java.util.Date;
import javax.mail.*;
import javax.mail.internet.*;
import java.net.InetAddress;

class TestMail
{
public static void main(String args[])
{
 try
 {
  //from address
  String from = "fromaddress@somecompany.com";
 
  //receipents of the mail
  String to = "toaddress@someothercompany.com";
 
  /*
  *If there are multiple
  *recepients, then email ids
  *should be stored in a String
  *with a space in between them.
  *eg: String to = "id1@comp1.com id2@comp2.com id3@comp3.com"
  */
    
  //subject of the mail
  String subject = "Test Mail Subject";
 
  //e-mail content
  String text = "This is a test email";
 
  /*SMTP host
  *SMTP host should be correct
  *for this program to work.
  *Also, some SMTP servers
  *are configured to allow
  *messages to be sent from
  *one or a few doamins
  */
  String mailhost = "mail.somecompany.com";
 
  //mailer
  String mailer = "testmailer";
 
  Properties props = System.getProperties();
 
  props.put("mail.smtp.host", mailhost);
 
  Session session = Session.getInstance(props, null);
 
     //construct the message
  Message msg = new MimeMessage(session);
  msg.setFrom(new InternetAddress(from));
  msg.setRecipients(Message.RecipientType.TO,
   InternetAddress.parse(to, false));
  msg.setSubject(subject);
  msg.setText(text);
  msg.setHeader("X-Mailer", mailer);
     msg.setSentDate(new Date());

  //send the mail
  Transport.send(msg);    
 
  System.out.println("\nMail was sent successfully.");
 }
 catch(Exception e)
 {
  e.printStackTrace();
 }
}
}

WARNING:
When full headers are viewed by the recepeint for a mail sent by this program, it mite have an indication that there is something fishy about how the mail was sent !!

Happy Spamming !!!

JavaScript NULL

2007-12-04T03:53:00.000-08:00

For JavaScript:

If you want to test whether a String object is NULL , then the following may FAIL

if(someString == "")

if(someString == "null")

The CORRECT way to test would be:

if(someString == null)

---------------------------------------------------------
Resources:

http://lists.evolt.org/archive/Week-of-Mon-20050214/169524.html

Post HTML codes in blogs

2007-12-03T02:53:00.000-08:00

If you post articles with html tags on blogspot, it will most probably give an error message as:

ERROR
"Your HTML cannot be accepted: Tag is not allowed: "

The way to get around this is to convert all the mark-ups (eg: '<' , '>') into character-entities (eg: '<' for '<' ; '>' for '>')

One can use online applications that perform this conversion stuff:

http://www.stanford.edu/~bsuter/js/convert.html

If formatting has to be retained, then just enclose the code inside '<pre>' and '</pre>' tags.

Web Browser Compatibility !!!!

2007-12-03T01:29:00.000-08:00

Developing on a web application is not easy if you intend to have the product compatible with all the available browsers in the universe for some new browser or its clone comes to life everyday.

This article is about how can one know which browser is being used by the end-user and how one can customize the code in accordance the same, be it IE or Mozilla Firefox or Safari or blah blah blaaaaaaaaaaaah !!!

The end-user browser can be determined by using navigator.appname

The following code illustrates the same:


<html>
    <head>
        <script type="text/javascript">
            function navigatorName()
            {
                alert(navigator.appName);
            }
        </script>
    </head>

    <body onload="navigatorName()">
        <div id="browserContents">
        </div>
    </body>
</html>

When the page gets loaded, the onload feature will call the javascript function navigatorName() which will display the alert box with the name of the browser as the message.

Now, inside the function navigatorName, we can write the logic that would make our code browser specific


<html>
 <head>
  <script type="text/javascript">
   function navigatorName()
   {
    if(navigator.appName == "Netscape")
    {
     document.getElementById("browserContents").innerHTML = "Netscape";
    }
    else if(navigator.appName == "Microsoft Internet Explorer")
    {
     document.getElementById("browserContents").innerHTML = "MS IE";
    }
    else
    {
     document.getElementById("browserContents").innerHTML = "Some Browser";
    }
   }
   </script>
  </head>

 <body onload="navigatorName()">
  <div id="browserContents">
   loading . . .
  </div>
 </body>
</html>

Here, when the body part of the page has been loaded, the browser calls the function navigatorName(). This function checks the browser being used, and in accordance to that, replaces the contents of the div with id browserContents appropriate contents by making use of the function document.getElementById("browserContents").innerHTML.

Derby

2007-12-02T22:55:00.000-08:00

I had a project work in which I needed to port an existing web-application running on MySQL toDerby. The SQL and datatypes of both are fairly same with minor variations (like syntax for triggers), though Derby does NOT support a few features like 'limit'.

TIPS:

If you are using embedded derby, remember, only ONE connection can be made to the database at a time. If applications running on different JVM's try to access the same database resource at the same time, then except the first one, none will be able to get access. So, go for Network server, if you have any such requirements.

----------------------------------------------

Username & Password

While creating the derby database, if username and password were not specified, and the application you are using requires one, then use the following if it doesn't accept blank/null fields

username: app
password: app

----------------------------------------------

Derby Network Server (for Windows)

Goto
%DERBY_INSTALL%\frameworks\NetworkServer\bin
and type "startNetworkServer.bat"

This will start the server and by default it will listen on port 1527

example:

In my machine Derby is installed in
C:\Program Files\Apache Software Foundation

So, value of the DERBY_INSTALL variable is "C:\Program Files\Apache Software Foundation\db-derby-10.2.1.6-bin"

So, the command "cd %DERBY_INSTALL%\frameworks\NetworkServer\bin"
opens up the following:
C:\Program Files\Apache Software Foundation\db-derby-10.2.1.6-bin\frameworks\Net
workServer\bin>

Now entering "startNetworkServer.bat" starts the server on port 1527

Remember, your derby database files must be located in the
"C:\> cd %DERBY_INSTALL%\frameworks\NetworkServer\bin" directory,
if you are run the server following the aforesaid.

From the program through which you wish access the database, give the following path of the database as:

"jdbc:derby://localhost:1527/dbname"

where 'dbname' is the name of the database.

If the database is located in some other folder, say D:/dbfolder, then modify the above as follows:

"jdbc:derby://localhost:1527/D:/dbfolder/dbname"

----------------------------------------------

Resources:

Tutorial on Apache Website

Ramblings

2007-11-29T22:51:00.000-08:00

I will attempt to jot down here codes/techniques on topics which I found were complex, or for which any help online or otherwise were not available. But, the things presented in this blog are not the geeky types. They are just a collection of simple things to make programming and software development easier.

Writing good algorithms and neat code is a demanding art that has to be learnt and mastered . On the contrary, developing applications requires not much intelligence, but knowledge of the available libraries and the know-how to use them.

So, posting articles will help me remember things that I learnt or did or both, and will come in handy for I am blessed with amnesia.