Sunday, September 30, 2012

Speedup mysql inserts

By default, when connected to MySQL server, every command is executed as a distinct transaction. As a result, after each INSERT SQL statement MYSQL saves data to the database and commits transaction. When your application inserts hundreds and thousands of records, your database is busy in saving new records all the time and you are loosing in speed.

The solution for this problem is very easy. You need to prevent MySQL from committing transaction after every INSERT and commit transaction for example every 50 records.

It can be easily done. You just need to issue "START TRANSACTION;" SQL statement, insert your records, and when the counter reaches 50 new records, do commit "COMMIT';

You can easily do it in a web based scripts and in stand alone applications.

For example:

START TRANSACTION;
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
-- more inserts

COMMIT;


After the COMMIT query all new records are flushed and written to disk.

This method gives in some cases up to 10x performance boost.

It can be applied to other types of databases too.


Thursday, September 27, 2012

Micro optimization or why code runs slower on 64 bit

One of my friends works for the company doing video processing. He told me that the company code runs slowly on 64 bit processor than on 32. It can be number of reasons. I would like to talk about the most important one.

The main problem is the size of the integer.

When your code runs on 32 bit processor you were using int for all integer numbers. When you compile the same code on 64 bit processor, the integer stays 32 bit.

  int i;

NOTE: The size of int variable in C++ stays 32 bit when code is compiled for 64bit processor or for 32 bit.

So, when you have the following code:

  unsigned char * data = new char[n];
  int i = 10;
  data[i] = 'a';

When the code is compiled and executed on 64 bit processor, the memory addressing is 64 bit too. As a result when doing simple and quick operation like data[i] which equals to offset_to_data+I is converted to offset_to_data + convert_to_64(i). The int i variable needs to be converted to 64 bit.

As a result, when working with memory all the int variables are converted to 64 bit. And eventually additional code is executed each time you access memory !


Solution.

The solution is very simple. You need to convert your code to use ssize_t  type variable instead of integers and use size_t type variable instead of unsigned int.

When the code is compiled on 32 bit processor the ssize_t type variable has the size of 32 bit.

When code is compiled on 64 bit processor, the ssize_t type  variable has the size of 64 bit.


Any problems?

One issue discovered when working with code that must compile on windows and linux. The problem is with printing these variables. Somehow Linux and Windows compilers use different standards when printing these numbers.

Here is solution:

#ifdef WIN32
#define SIZE_T_FORMAT "%Iu"
#define SSIZE_T_FORMAT "%Id"
#else
#define SIZE_T_FORMAT "%zd"
#define SSIZE_T_FORMAT "%zu"
#endif
 

ssize_t num = 1234;
printf("ssize_t number: " SIZE_T_FORMAT "\n", num);


Feel free to leave a comments/questions here.

Tuesday, September 25, 2012

Hello World Post

Hello !

This is my first post at this blog.

Enjoy your stay !