Simple analysis and improvement of MySQL schema

Here is some basic analysis and advises on improvement of MySQL database performance. From time to time you should check you MySQL schema status, I use the following request for it:

SELECT                                                                                                                                                                                                                                                                         
    table_name AS table_name,                                                                                                                                                                                                                                                  
    engine,                                                                                                                                                                                                                                                                    
    ROUND(data_length/1024/1024,2) AS total_data_size_mb,                                                                                                                                                                                                                      
    ROUND(index_length/1024/1024,2) AS total_index_size_mb,                                                                                                                                                                                                                    
    ROUND((data_length+index_length)/1024/1024,2) AS total_size_mb,                                                                                                                                                                                                            
    ROUND(data_free/1024/1024,2) AS total_free_size_mb,                                                                                                                                                                                                                        
    ROUND(data_free/(data_length/100)) AS defragment_percent,                                                                                                                                                                                                                  
    table_rows                                                                                                                                                                                                                                                                 
FROM                                                                                                                                                                                                                                                                           
    information_schema.tables                                                                                                                                                                                                                                                  
WHERE                                                                                                                                                                                                                                                                          
    table_schema=DATABASE();

Field defragment_percent value should be 0, but it may be even higher than 100(e.g. you loaded 3MB of data to the table and then deleted 2MB, the table will still be 3MB with real data of 1MB). The most prone to fragmentation are VARCHAR fields so pay attention to the tables where you use VARCHAR a lot. To optimize tables you can use MySQL command:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

To optimize the whole schema you can use:

mysqlcheck -o <databasename>

Field total_index_size_mb should be less than total total_data_size_mb. If it is not so then review your indexes.

Also I'd recommend to use MySQLTuner. MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. To get it you can use:

wget http://mysqltuner.com/mysqltuner.pl

Then run it with

perl mysqltuner.pl

This tool will provide you with analysis of your MySQL server and some advises on how to improve its performance.

Read more...

Execute scripts in multiple databases

Have you ever needed to execute the same script on several databases? I have met such need and developed a simple algorithm for it. This algorithm I'd like to share.

At first I tried to develop it using only JDBC but unfortunately it has the really big limitation: it cannot execute at once several calls, which is pretty common for SQL scripts. So I decided to run scripts merely from a command line.

Here is the code:

package uay.loader;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileFilter;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.Logger;

public class BulkScriptLoader {

    private static final String   DB_PREFIX     = "ua";
    private static final String[] DB_SKIP = { "ua_1", "ua_2" };
    private static final String   FOLDER_NAME   = "scripts";
    private static final String   FILE_EXT      = ".sql";
    private static final String   URL           = HOST + ":" + PORT;
    private static final String   USER_NAME     = "xxx";
    private static final String   PASSWORD      = "xxx";

    private final static Logger   LOGGER        = Logger.getLogger(BulkScriptLoader.class);

    public static void main(String[] args) {
        Connection connection;
        try {
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        } catch (SQLException e1) {
            LOGGER.error(e1);
            throw new RuntimeException(e1);
        }

        List<String> dbNames = getDbNames(connection);
        File[] scriptFiles = getScriptFiles();
        for (File file : scriptFiles) {
            LOGGER.info("Work with " + file.getName());
            BufferedReader bf;
            StringBuilder statement = new StringBuilder();
            try {
                bf = new BufferedReader(new FileReader(file));
                String line = "";
                while ((line = bf.readLine()) != null) {
                    statement.append(line).append("\n");
                }
            } catch (IOException e) {
                LOGGER.error(e, e);
            }
            if (statement.toString().length() > 0) {
                execute(connection, dbNames, statement.toString(), URL, USER_NAME, PASSWORD);
            }
        }
    }

    public static File[] getScriptFiles() {
        File folder = new File(FOLDER_NAME);
        if (folder.exists() && folder.isDirectory()) {
            return folder.listFiles(new FileFilter() {
                @Override
                public boolean accept(File pathname) {
                    return pathname.getName().toLowerCase().endsWith(FILE_EXT.toLowerCase());
                }
            });
        }
        return null;
    }

    public static void execute(Connection connection, List<String> dbNames,
            String generalStatement, String url, String userName, String password) {
        for (String dbName : dbNames) {
            String cmdCommand = String.format("mysql -u %s -p%s -h %s -P %s -D %s",userName, password, 
              parseHost(url), parsePort(url), dbName);
            LOGGER.info(cmdCommand);
            try {
                Process process = Runtime.getRuntime().exec(cmdCommand);
                Writer w = new OutputStreamWriter(process.getOutputStream());
                // System.out.println(statement);
                w.write(generalStatement);
                w.flush();
                w.close();
            } catch (IOException e) {
                LOGGER.error(e, e);
            }
        }
    }

    public static String parseHost(String url) {
        return url.substring(0, (url.indexOf(":") > 0 ? url.indexOf(":"): url.length()))

    }

    public static String parsePort(String url) {
        Pattern p = Pattern.compile("(\\d\\d\\d\\d)");
        Matcher m = p.matcher(url);
        if (m.find()) {
            return m.group(0);
        }
        return "3306";
    }

    public static List<String> getDbNames(Connection connection) {
        List<String> dbNames = new ArrayList<String>();
        try {
            PreparedStatement preparedStatement = connection.
               prepareStatement("select schema_name from information_schema.schemata");
            if (preparedStatement.execute()) {
                ResultSet rs = preparedStatement.getResultSet();
                while (rs.next()) {
                    String dbName = rs.getString(1);
                    if (dbName.startsWith(DB_PREFIX) && !Arrays.asList(DB_SKIP).contains(dbName)) {
                        dbNames.add(dbName);
                    }
                }
            }
        } catch (SQLException e) {
            LOGGER.error(e, e);
        }
        return dbNames;
    }
}

As you can see it's really simple. All scripts that I'd like to execute I place in 'scripts' folder with '.sql' extension. I configure database connection, prefix of databases that I want to update and specify the list of databases that I want to skip with this update.

Read more...

Search and replace text in multiple files

It's very common task to find some text in some files and replace it. Here is my recipe to deal with it

In this example I will find all 'varchar' text in my '.sql' files and replace it with 'char'.
At first let's search for the files that contain the text:

grep -i 'varchar' *.sql

In the output you can see the list of files with corresponding match within it.
Now let's take this list of files and replace the searched text within with the new one:

grep -il 'varchar' *.txt | xargs sed -i 's/varchar/char/gi'

This was a case-insensitive case. But if you need to be case-sensitive then use:

grep -l 'varchar' *.txt | xargs sed -i 's/varchar/char/g'

Read more...

Exclude SVN directories from Eclipse search

If you perform search in Eclipse IDE and use SVN in your project then probably you have encountered the situation that the search tool looks also in .svn.

To fix this situation you can:

1. Open Project settings
2. Select Resoursec / Resource Filter
3. Click Add... button
4. Select exclude all, applies to folders, all children
5. Type .svn

And that is all you need. The next search will no include svn directories.

Read more...