MYSQL -- CPU Max Out; Very Poor Response

[sgpal]

Hello,
I have been struggling with a performance issue on my MYSQL Server that results in a huge response degradation on my website. The CPU utilization goes to 150% and on this powerful box I get the query back in 12-20s.

Pasted in the body –
a) Table description
b) Query
c) my.ini
d) We have to use a lot of “LIKE” with the percent sign before it unfortunately to perform text searching.

Can anybody help? Have you experienced something like this before?
Thanks in advance,
GP
sgpal@hotmail.com

The details are as follows:

1. Version MySQL 5.0.27
2. Fedora Core 6 on Dell 2950 with Dual Core and 8 GB RAM
3. Table structure pasted below with 94 columns.
4. QUERY
   select archive_table.lastname, archive_table.column_id_373, archive_table.column_id_389, archive_table.column_id_381, archive_table.column_id_371, archive_table.column_id_435, archive_table.column_id_365 , archive_table.leadid as crmid from archive_table where archive_table.deleted=0 and archive_table.converted=0 and archive_table.column_id_381 like ‘%BLISS%’ ORDER BY archive_table.leadid DESC LIMIT 20 OFFSET 0

Table Structure

mysql> describe archive_table;
±------------±--------------±-----±----±--------------- -----±------+
| Field | Type | Null | Key | Default | Extra |
±------------±--------------±-----±----±--------------- -----±------+
| leadid | int(19) | NO | PRI | 0 | |
| lastname | varchar(80) | NO | | | |
| firstname | varchar(80) | NO | | | |
| website | varchar(200) | YES | | NULL | |
| company | varchar(100) | NO | | | |
| createdtime | datetime | NO | | 0000-00-00 00:00:00 | |
| converted | int(1) | NO | | 0 | |
| deleted | int(1) | NO | | 0 | |
| column_id_363 | varchar(255) | YES | | NULL | |
| column_id_365 | varchar(255) | YES | | NULL | |
| column_id_371 | longtext | YES | | NULL | |
| column_id_373 | varchar(255) | YES | | NULL | |
| column_id_375 | varchar(255) | YES | | NULL | |
| column_id_377 | varchar(255) | YES | | NULL | |
| column_id_379 | varchar(255) | YES | | NULL | |
| column_id_381 | longtext | YES | | NULL | |
| column_id_383 | longtext | YES | | NULL | |
| column_id_385 | longtext | YES | | NULL | |
| column_id_389 | varchar(255) | YES | | NULL | |
| column_id_391 | varchar(255) | YES | | NULL | |
| column_id_393 | longtext | YES | | NULL | |
| column_id_395 | longtext | YES | | NULL | |
| column_id_397 | varchar(255) | YES | | NULL | |
| column_id_399 | varchar(255) | YES | | NULL | |
| column_id_401 | varchar(255) | YES | | NULL | |
| column_id_403 | text | YES | | NULL | |
| column_id_405 | longtext | YES | | NULL | |
| column_id_407 | varchar(255) | YES | | NULL | |
| column_id_409 | varchar(255) | YES | | NULL | |
| column_id_411 | varchar(255) | YES | | NULL | |
| column_id_413 | varchar(255) | YES | | NULL | |
| column_id_415 | varchar(255) | YES | | NULL | |
| column_id_417 | varchar(255) | YES | | NULL | |
| column_id_419 | varchar(255) | YES | | NULL | |
| column_id_421 | varchar(255) | YES | | NULL | |
| column_id_423 | varchar(255) | YES | | NULL | |
| column_id_425 | varchar(255) | YES | | NULL | |
| column_id_427 | varchar(255) | YES | | NULL | |
| column_id_429 | varchar(255) | YES | | NULL | |
| column_id_431 | varchar(255) | YES | | NULL | |
| column_id_433 | longtext | YES | | NULL | |
| column_id_435 | varchar(255) | YES | | NULL | |
| column_id_437 | longtext | YES | | NULL | |
| column_id_439 | varchar(255) | YES | | NULL | |
| column_id_441 | longtext | YES | | NULL | |
| column_id_443 | varchar(255) | YES | | NULL | |
| column_id_445 | longtext | YES | | NULL | |
| column_id_447 | varchar(25) | YES | | NULL | |
| column_id_449 | varchar(100) | YES | | NULL | |
| column_id_451 | varchar(100) | YES | | NULL | |
| column_id_453 | varchar(100) | YES | | NULL | |
| column_id_455 | varchar(100) | YES | | NULL | |
| column_id_457 | varchar(100) | YES | | NULL | |
| column_id_459 | varchar(100) | YES | | NULL | |
| column_id_461 | varchar(100) | YES | | NULL | |
| column_id_463 | longtext | YES | | NULL | |
| column_id_465 | varchar(100) | YES | | NULL | |
| column_id_467 | varchar(100) | YES | | NULL | |
| column_id_469 | varchar(50) | YES | | NULL | |
| column_id_471 | longtext | YES | | NULL | |
| column_id_473 | varchar(50) | YES | | NULL | |
| column_id_475 | varchar(50) | YES | | NULL | |
| column_id_477 | varchar(50) | YES | | NULL | |
| column_id_479 | longtext | YES | | NULL | |
| column_id_481 | varchar(100) | YES | | NULL | |
| column_id_483 | varchar(50) | YES | | NULL | |
| column_id_485 | varchar(50) | YES | | NULL | |
| column_id_487 | longtext | YES | | NULL | |
| column_id_489 | varchar(100) | YES | | NULL | |
| column_id_491 | varchar(50) | YES | | NULL | |
| column_id_493 | varchar(50) | YES | | NULL | |
| column_id_495 | longtext | YES | | NULL | |
| column_id_497 | varchar(50) | YES | | NULL | |
| column_id_499 | varchar(50) | YES | | NULL | |
| column_id_505 | varchar(10) | YES | | NULL | |
| column_id_507 | varchar(10) | YES | | NULL | |
| column_id_509 | varchar(100) | YES | | NULL | |
| column_id_511 | varchar(100) | YES | | NULL | |
| column_id_515 | varchar(100) | YES | | NULL | |
| column_id_517 | varchar(50) | YES | | NULL | |
| column_id_549 | decimal(8,2) | YES | | NULL | |
| column_id_551 | decimal(8,2) | YES | | NULL | |
| column_id_553 | decimal(8,2) | YES | | NULL | |
| column_id_555 | decimal(8,2) | YES | | NULL | |
| column_id_557 | decimal(8,2) | YES | | NULL | |
| column_id_559 | decimal(8,2) | YES | | NULL | |
| column_id_561 | decimal(8,2) | YES | | NULL | |
| column_id_563 | decimal(8,2) | YES | | NULL | |
| column_id_565 | decimal(8,2) | YES | | NULL | |
| column_id_567 | date | YES | | NULL | |
| column_id_867 | date | YES | | NULL | |
| column_id_1265 | decimal(27,6) | YES | | NULL | |
| column_id_1267 | varchar(100) | YES | | NULL | |
±------------±--------------±-----±----±--------------- -----±------+

My.CNF
[mysqld]
default-storage-engine=INNODB
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Default to using old password format for compatibility with mysql 3.x

#skip-locking
query_cache_limit=36M
query_cache_size=512M
query_cache_type=1
max_connections=500
max_user_connections=300
#interactive_timeout=20
#wait_timeout=20
#connect_timeout=6
thread_cache_size=256
thread_stack=256K
key_buffer=32M
join_buffer=3M
max_allowed_packet=16M
table_cache=2048
record_buffer=2M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=9999999

Try number of CPU’s*2 for thread_concurrency

thread_concurrency=4
myisam_sort_buffer_size=64M

clients (those using the mysqlclient10 compatibility package).

old_passwords=1
long_query_time=10
log-slow-queries=/var/log/mysql/mysql-slow.log

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

[tanj]

Well, I’m not in a habit to use 94 columns table (do you have a real reason to do this? possible to split this logically in many tables? dfficult to say as you don’t post your column names)

But I have a few hints for optimisations (supposing that your table is MyISAM type).

1. query_cache_size=512M

this is way useless, restrict this to 64M

2. key_buffer_size=32M

Whoa! you’re only using 32M here. It means that your database must be doing a lot of disk accesses. How big is your table? I see that you have 8GB memory in your server, so you could easily set key_buffer_size=1024M.

3. LIKE queries
   These are very slow. Either use FTS ([URL]http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html[/URL]) or Sphinx (http://sphinxsearch.com). FTS is built into MySQL, you just have to create the fulltext indexes and rewrite your queries. Also if you can use Sphinx, it’s extremely fast and efficient.

4. Table structure
   Also you could revamp your table structure a little. Think logically and split data, join by id. I see that in your sample query you return only 8 fields so it could be a huge optimization.

Best Regards.