Unnecessary charset conversion causes performance issue?
Posted by: Joe Cai
Date: February 27, 2006 09:24AM

Hi,there,

I came into this problem when trying to upgrade mysql from 4.1.x to 5.0.x.
The cpu usage went up to 99.x% when I was importing large sql files into mysql.
Thus the importing process was very very slow. It looks like that mysql spent a
lot of cpu time doing charset conversion.

I have no idea if it's due to wrong configurations or mysql bug.

My database uses gbk and here's a typical table in it:

CREATE TABLE `products` (
`product_id` int(11) NOT NULL auto_increment,
`name` varchar(60) default NULL,
`price` float default NULL,
`time` int(11) default NULL,
`overview` text,
`detail` text,
PRIMARY KEY (`product_id`),
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

And You can see the character set variables below.

mysql> show variables like '%character%';

+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | gbk |
| character_set_results | gbk |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+

And here's my configuration in my.cnf that is related to character sets.

-- snip begin --
[client]
default-character-set = gbk

[mysqld]
character_set_server = gbk
collation_server = gbk_chinese_ci
skip-character-set-client-handshake

[mysql]
no-auto-rehash
default-character-set = gbk

-- snip end --

Could someone give me some idea of what's happening or how to solve this performance
problem? Thanks a lot.

Options: ReplyQuote


Subject
Views
Written By
Posted
Unnecessary charset conversion causes performance issue?
3430
February 27, 2006 09:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.