MySQL Table and Schema Size

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

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

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

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

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

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

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

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>