Infobright/MySQL Statistics Utility (C++)

• Download cpp.zip - 103 KB

Introduction  

This C++ program connects to an Infobright database and display its statistics (size, compressed size, compression ratio, etc.) both of the databases and tables in a specific database. The code can be used for use for a MySQL database.  

NOTE: You may need to remove the compression part since MySQL do not have the compression rate for its databases.

The purpose of this program is to provide some simple code to users about how to work with C++, Infobright databases, and MySQL.  

Required software  

Running the executable

There are two types of files in the source code. The executable file is for Windows users; and the binary file is for Linux users.

For windows users, run the file in Cygwin with the command like this:

./databases_windows [OPTIONS]

For Linux users, run the program in terminal with the command like this:

./databases_linux [OPTIONS]

The options are virtually the same as the mysql command

The default options will be used for unspecified options.  

i.e. For Windows users:

        ./databases_windows -h 127.0.0.1

     For Linux users:
   
         ./databases_linux -h 127.0.0.1

NOTE: If you see "Permission denied" after you type the command, you might need this to give you permission for the program: 

chmod +x databases_windows      and/or       chmod +x databases_linux 

This is the statistics of databases:

This is the statistics of tables in a specific database:

Editing the source code 

If you would like to view/edit the source code, we have included a compiling script.

For Linux users, you will need g++, mysql-client, and the MySQL development libraries.

    Ubuntu:  sudo apt-get install g++ mysql-client libmysqlclient-dev

    CentOS:  sudo yum install gcc-c++ mysql-client mysql-devel

For Windows users, install the Cygwin  

    http://www.cygwin.com/install.html

Download the setup.exe, then follow the steps to install the gcc and other packages.

    A detailed installation procedure is shown here:
 
    http://www.mcclean-cooper.com/valentino/cygwin_install/

    NOTE: when selecting the packages, at least selecting these packages:
        (1) Admin
        (2) Database
        (3) Devel
        (4) Libs
        (5) System
        (6) Math
        (7) Shells

Analyzing the code

Now let's analyze some of the source code. First of all, the main function asks for the arguments and connects to the database you selected. If you insert "-?" or "--help" into command line, then the show_help_screen function will be called and display the command options and default settings on the screen.  

Before we display the statstics of databases and tables, we need to save the statistics information into our memory since it will be easier for us to get these values later. Personally, I prefer using linked lists as the data strcture. database_linked_list is the function to create such linked lists.

The structures of the linked lists are listed below: 

struct table_stat {
    string table_name;
    double table_size;
    double table_raw;
    double table_compression;
    table_stat * table_next;
};
 
struct data_stat {
    string data_name;
    double compressed_size;
    double raw_size;
    double compression;
    data_stat * next;
    table_stat * mytable;
}; 

Obviously, table_stat is for the table statistics and the data_stat is for the database statistics.  Each table_stat has a pointer points to its table_stat list, and another pointer points to the next data_stat. The last table_stat and data_stat points to NULL so as to inform that this is the last block of the list.

Here is a graph showing the linkage between these lists:

Moreover, total_raw, total_compressed and total_ratio are used as global variables in order to save the total raw size, total compressed size and total compression ratio of the whole database. 

Unfortunately, the statistics you get from the database are in the form of strings, which means the numbers are saved as characters instead of a paticular number. In this way, we need to an additional step, I called it string_to_double, which is just like its literal meaning, transfer the type from string to double.  

The code is listed below:  

double string_to_double (string num){
    double return_result = 0.0;
    //first we want to find the '.'
    int i = 0;
    int no_dot = 0;
    while (num[i] != '.'){
          if (num[i] == '\0'){
             no_dot = 1;
             break;
          }
          i++;
    } 
    int dot = i;
    i--;
    double temp = 1;
    for (int j = i; j>= 0; j--){
        return_result = return_result + (((double)num[j])-48)*temp;
        temp = temp * 10;
    }
    dot++;
    temp =10;
    if (no_dot == 0){
       while (num[dot] != '\0'){
             return_result = return_result + (((double)num[dot])-48)/temp;
             dot++;
             temp =temp * 10;
       }
    }
    return return_result;
}  

After creating the data structures, we finally need to display the statistics onto the screen by using show_table and show_data_stat these two functions. The final results are shown on the graph above.   

When 'exit' is typed in, we need to call mysql_free_result and mysql_close these these two functions to close the database. Moreover, since we have allocated some memory spaces for our linked lists, we also need to delete these memories so as to avoid memory leak. Thus delete_linked_list is called to do so. 

Useful Queries 

When getting data from MySQL, we need to use some queries to help us get specific information. Here are some queries I used in this project. 

This lists the size (in MB) of each individual database: 

SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 'Data Base Size in MB',TABLE_COMMENT  
FROM information_schema.TABLES 
WHERE ENGINE = 'BRIGHTHOUSE' 
GROUP BY table_schema;

show variables like 'version_comment';   

This shows the table statitcs of the database called test: 

USE test; 
SHOW TABLE STATUS WHERE ENGINE='BRIGHTHOUSE';

NOTE: The above query is for an Infobright database. If you would like to get statistics for an InnoDB or MyISAM database, remove "where Engine='BRIGHTHOUSE'" from the query. 

History

The most recent version contains both versions for Linux and Windows users. The specific using methods are shown above and in README.txt file.

Conclusion 

This code is meant to be used as a starting point for building a database statistics application. You are encouraged to modify the code to fulfill your specific needs.