Posts Tagged ‘DB’

Thinkphp memory leak? ThinkPHP内存泄露?

April 8th, 2013

Recently I encountered a problem with writing scripts with ThinkPHP framework. My scripts usually take long time to finish and have lots of communication with Mysql. However I find with ThinkPHP it’s very easy to come across with problem of out of memory, even if I’ve set the memory limit for each script to 256MB, which is quiet high!

Today I finally decided to carefully check this problem. The first thing came into my mind was that this must have something to do with db operations since I’m have heavy mysql operations. So I write a small piece of code to test whether I was right. Here is the code:

public function test() {
$mdl = M('t3');
$i = 0;
while(++$i < 300) {
$mdl->where('id=1')->select();
if($i % 50 == 0) {
echo number_format(memory_get_usage()) . "
\n";
}
}
}

I run this code and found this memory usage kept increasing which indicates there really exists a memory leak. Here is a sample output:

4,125,264 
4,146,688 
4,169,136 
4,189,536 
4,211,984

And then I replaced select() with buildSql() which only builds the sql instead of querying the db, I found that the memory stopped increasing. So the problem locates in the db operations!

After diving a bit in the source code, I found that the cause is the function Db::debug() which utilizes function trace(). Here is the Db::debug() definition:

    protected function debug() {
        $this->modelSql[$this->model]   =  $this->queryStr;
        $this->model  =   '_think_';
        if (C('DB_SQL_LOG')) {
            G('queryEndTime');
            trace($this->queryStr.' [ RunTime:'.G('queryStartTime','queryEndTime',6).'s ]','','SQL');
        }
    }

I realized that at the moment my application is still in the debug mode, which by default set the DB_SQL_LOG true. So now the solution is obvious: simply switch off the sql log, either by defining it in the config file such as Conf/debug.php or set it dynamically like this C(‘DB_SQL_LOG’, false). Because my website is still under development thus I need it on. So I choose the latter to set it dynamically in my script file.

But how the memory leak happens still remains uncovered. I’ll check it later.

How to keep the order of SELECT WHERE IN() in mysql

April 21st, 2012

The answer is to use ORDER BY FIND_IN_SET(col_name, order_values).

Here is an example. A table `students` has 2 columns: `id`, `name`.
If you want to find the items with the IDs of 4,5,2,1, and keep them in the order of 4,5,2,1 as well.

Here is the SQL:

SELECT id, name FROM students
WHERE id IN (4,5,2,1)
ORDER BY FIND_IN_SET(id, '4,5,2,1');

allow mysql to get outer connections

July 26th, 2011

If you want your mysql for outer connections, do not forget to set :

bind-address            = 0.0.0.0

in my.cnf.

Otherwise, you will get the error message like this: ”the target machine actively refused it”.

notes for migrating large data in mysql (innodb)

March 13th, 2011

Here migrating means exporting & importing. The tips below may help you when you need to migrate a large amount of data. I’ll also list the resource consume of my exporting & importing of 2 big tables:

name definition rows
users CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` char(30) CHARACTER SET utf8 NOT NULL,
`city` char(50) CHARACTER SET utf8 DEFAULT NULL,
`insertTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`assignedTo` smallint(5) unsigned NOT NULL DEFAULT ’0′,
`iconUrl` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `assignedTo` (`assignedTo`),
KEY `insertTime` (`insertTime`),
KEY `users_crawlTime` (`crawlTime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
21,000,000
relations CREATE TABLE `friendships` (
`id` int(11) NOT NULL,
`friendId` int(11) NOT NULL,
PRIMARY KEY (`id`,`friendId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
131,000,000

There are 2 ways to exprot & import data:

  • Using  SELECT … INTO OUTFILE … to export, and LOAD DATA INFILE … INTO TABLE … to import.
  • Using mysqldump … to export, and source … to import.

1. SELECT … INTO OUTFILE … & LOAD DATA INFILE … INTO TABLE …

The basic usage of SELECT INTO is:

SELECT … FROM tb_name INTO OUTFILE ‘/home/me/datadump/xxxx.txt’;

The basic usage of LOAD DATA is:

LOAD DATA INFILE ‘/home/me/datadump/xxxx.txt’ INTO TABLE tb_name [FIELDS TERMINATED BY 't' LINES TERMINATED BY 'n'];

1.1 Tips for SELECT INTO

  • use ORDER BY primary_key.
  • increase sort_buffer.

1.2 Tips for LOAD DATA

  • turn off foreign key checking, log bin & unique checking.
    set foreign_key_checks=0;
    set sql_log_bin=0;
    set unique_checks=0;
    load data ...
  • turn on delay_key_write and increase bulk_insert_buffer_size by editing [mysql] section in my.cnf. Here is a example:
    delay_key_write=ALL 
    bulk_insert_buffer_size=2048M

2. mysqldump … & source …

The basic grammar for mysqldump  is:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

And the source is easy to use:

mysql: source ‘/home/me/datadump/xxxx.sql’;

2.1 Tips for mysqldump

  • use switch –opt. This option is short for “–add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset”. The –opt option is enabled by default. You can use –skip-opt to disable it.
  • use switch –order-by-primary. Dump a table sorted by its primary key, or by its first unique index, if such an index exists. This is useful when dumping a MyISAM table to be loaded inInnoDB.

 

3. other tips:

  • use a single commit.
  • split a big file into several small files, such as 1M records in a file.

move Mysql’s datadir on ubuntu

March 8th, 2011

Mysql’s datadir (on ubuntu) by default is /var/lib/mysql. Sometimes, if your DB grows bigger and bigger, and finally exceeds the limit of /var partition, you may think of moving it to some other place. Here are the steps.

  1. stop the server
    sudo service mysql stop

    or

    sudo /etc/init.d/mysql stop
  2. copy data to the new folder
    For example, you want to set the new folder in /home2/: 

    sudo cp /var/lib/mysql /home2/

    Now /home2/mysql is the new datadir. Of course, you can change it to whatever name you want.

    Here I deleted the log file ib_logfile* by:

    sudo rm /home2/mysql/ib_logfile0

    Maybe you also want to change the original datadir to some other name to make sure it won’t be in effect any more.

    sudo mv /var/lib/mysql /var/lib/mysql.bak
  3. change the permission of the new directory
    Make sure the owner of /home2/mysql and all its subdirectories is mysql:mysql. When necessary, use chown to change the permission:sudo chown -R mysql:mysql /home2/mysql 

    Also make sure the permissions of the new datadir and its subdiretories are the same as those under /var/lib/mysql. In my experiment, the permission of table files in each database was changed to 400 when copying, so I had to use chmod to correct them:

    sudo chown -R me:me /home2/mysql;
    sudo chmod 660 /home2/mysql/*/*;
    sudo chown -R mysql:mysql /home2/mysql

    The reason why I changed the owner to me (my username on my computer) at the beginning and change it back to mysql at last is so that I can use the wildcast * to change all files’ permission at once.

  4. modify my.cnf and point datadir to the new directory
  5. sudo vi /etc/mysql/my.cnf

    Find datadir and change it to:

    datadir=/home2/mysql

    It seems in Mysql 4 you also need to change the basedir and socket accordingly, but I’m not sure.

  6. add the new diretory into the AppArmor configure file
  7. This part is special on some Linux distributions like Ubuntu >= 7.04. AppArmor is a Linux Security Module implementation of name-based access controls. It confines individual programs to a set of listed files and posix 1003.1e draft capabilities.

    You can use

    sudo aa-status

    to check whether mysql is confined by AppArmor. If so, use

    sudo vi /etc/apparmor.d/usr.sbin.mysqld

    to add the new datadir at the end of the file.

  8. restart apparmor service
    sudo /etc/init.d/apparmor reload
  9. start mysql server
    sudo service mysql start

    or

    sudo /etc/init.d/mysql start

    Refer to /var/log/mysql/error.log to see if there are any errors.

  10. do some tests.
    List all the database, select some records from a table, create a new table and see whether it’s in the new datadir, insert some records, delete them, and whatever.
    WARNING: you may need to run Mysql with the new datadir for a few days before deleting the old datadir.

Use Lucene to index a database

April 11th, 2010

According to Luncene FAQ:

How can I use Lucene to index a database?

Connect to the database using JDBC and use an SQL “SELECT” statement to query the database. Then create one Lucene Document object per row and add it to the index. You will probably want to store the ID column so you can later access the matching items. For other (text) columns it might make more sense to only index (not store) them, as the original data is still available in your database.

For a more high level approach you might want to have a look at LuSql (a specialized tool for moving data from JDBC-accessible databases into Lucene), Hibernate Search, Compass, DBSight, or Solr’s Data Import Handler which all use Lucene internally.

An example: Apache Lucene – Indexing a Database and Searching the Content