About MySQL index max length

By WZH

To fast the search speed such as sort, index is very important tool must to use(INDEX, PRIMARY KEY and  UNIQUE). By default (5.6 version) the InnoDB support the 767 bytes length (<256 UTF8 chars) for a index. But if your text is a bit longer  and you need to index them, what you should do?

You have to understand that two file format concepts of Antelope(default) and Barracuda(start from version 5.6 mySQL). Refer to the MySQL manual :

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format-identifying.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format-downgrading.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-specification.html

  • Antelope is the original InnoDB file format, which previously did not have a name. It supports COMPACT and REDUNDANT row formats for InnoDB tables and is the default file format in MySQL 5.6 to ensure maximum compatibility with earlier MySQL versions that do not support the Barracuda file format.

So you see that new file format can support up to 3027 bytes for index, which will improve the search speed a lot for long text. Here is the steps to change to make the large index works:

You can change the config in the my.cnf (/etc/mysql/my.cnf)

innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_large_prefix=1

or you can dynamic change at the console:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;     (default is on already)
SET GLOBAL innodb_large_prefix=1
All of those settings are captured as a table is created. (You could change existing tables using ALTER.)

About the
ROW_FORMAT=DYNAMIC;      ( or COMPRESSED  to save space but cost CPU)
ROW_FORMAT=DYNAMIC can be append to the end of CREATE or used in the ALTER TABLE.  MySQL WorkBench also support you this at alter table at “options” tab.
ALTER TABLE t ROW_FORMAT=format_name;

Advertisements

How to make the Linux guest screen display properly in Hyper-V

By WZH

I have worked on the Linux guest on Hyper-V recently, to set a proper screen display is more troublesome than in VirtualBox. (Running Linux on Hyper-V is not a good idea as obviously running slowly, even MS support integration in most Linux kernel to run them. ).

Here is the notes for the CentOS and Ubuntu:

For CentOS and Red Hat

 

grubby --update-kernel=ALL --args="video=hyperv_fb:1024x768"
reboot

 

For Ubuntu

Install linux-image-extras (hyperv-drivers):

sudo apt-get install linux-image-extra-virtual

Open the Terminal and type:

sudo gedit /etc/default/grub

Find the line starting with GRUB_CMDLINE_LINUX_DEFAULT, and add

video=hyperv_fb:1920×1080

or your preferred resolution in between the quotes (The maximum possible resolution is 1920×1080) like this:

GRUB_CMDLINE_LINUX_DEFAULT="quiet splash video=hyperv_fb:1920x1080"

Save and Exit.

Run

sudo update-grub

Restart Hyper-V (restarting Ubuntu (Linux) might be enough.

 

How to consume json object from Spring MVC controller input directly

By WZH

This is small note about the json input directly to Spring MVC controller side, jackson will auto convert it to Java object you want:

  1. Make sure has this jar added

 <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.5.0</version>
  </dependency>

2.  Controller side

@RequestMapping(value = urlPatternController, method = RequestMethod.POST)
public @ResponseBody Person createPerson(@RequestBody Person jsonString) {
   Person person=personService.savedata(jsonString);
   return person;
}

@RequestBody – Covert Json object to java
@ResponseBody– convert Java object to json

3. JS client side :

 

$.ajax("<%=path%>/web/urlPatternController", {
                           type:'POST',
                           dataType: 'json',
                           data: person
                    })

 

Link can be refered

 

 

Improve the Mybatis performance

By WZH

Some tips on improve the Mybatis performance.

  1. Set fetchSize to a considerable amount.
  2. Only retrieve needed columns from DB and map them to the Java object.
  3.  Try to reduce the nested selects in one user interaction, try to use more nested joins  in the SQL level in one time DB call.
  4.  Use cache integration to improve re-read speed.  Such as Memcached or  Ehcache
  5. SQL  and DB self optimization, such as index for sort and order

 

 

How to run Memcached on Mybatis

By W.ZH

1. Install Memcache

To start, install memcached via apt-get. such as in the Ubuntu 12.04

sudo apt-get install memcached

It auto starts the memcached

ps -ax | grep memcac
21199 ?        Sl     0:00 /usr/bin/memcached -m 64 -p 11211 -u memcache -l 127.0.0.1

Refer to official Mybatis link:  http://mybatis.github.io/memcached-cache/
Add the jar to your maven

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-memcached</artifactId>
    <version>1.0.0</version>
  </dependency>
 and then add the memcached to your mapper if you want which MyBatis mapper to use it.
<mapper namespace="org.acme.FooMapper">
  <cache type="org.mybatis.caches.memcached.MemcachedCache" />
  ...
</mapper>
Create a memcached.properties file and put to your class path, eg resources folder.
# any string identifier
org.mybatis.caches.memcached.keyprefix=_mybatis_
# space separated list of ${host}:${port}
org.mybatis.caches.memcached.servers=127.0.0.1:11211
org.mybatis.caches.memcached.connectionfactory=net.spy.memcached.DefaultConnectionFactory
org.mybatis.caches.memcached.expiration = 600
org.mybatis.caches.memcached.asyncget = true
# the expiration time (in seconds)
org.mybatis.caches.memcached.timeout = 600
org.mybatis.caches.memcached.timeoutunit = java.util.concurrent.TimeUnit.SECONDS
# if true, objects will be GZIP compressed before putting them to Memcached
org.mybatis.caches.memcached.compression = false

In fact if you add multiple cache server at org.mybatis.caches.memcached.servers, it will has fail over ability among them. auto continue using live one if one die.

This intergration in fact based on the Spymemcached, is an asynchronous supported, single-threaded Memcached client. When you call any caching-related method on spymemcached’s MemcachedClient, it will be handled asynchronously. The client call method handles writing the details of the operation that should be performed into a queue and returning the control back to the client making the call. The actual interaction with the Memcached server, meanwhile, is handled by a separate thread that runs in the background.

My testing prove that it can improve the loading DB data at least 50% reading time if data has been in memcached. So this also proves that Mybatis self cache is not enough  big because it is not designed for cache only.