| MYSQL -- CPU Max Out; Very Poor Response [message #1332] |
Mon, 28 May 2007 22:57  |
 sgpal@hotmail.com
Messages: 1
Registered: May 2007 |
Junior Member |
|
|
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]
|
|
|
| Re: MYSQL -- CPU Max Out; Very Poor Response [message #1339 is a reply to message #1332 ] |
Tue, 29 May 2007 11:03  |
tanj
Messages: 16
Registered: March 2007
Location: France |
Junior Member |
|
|
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 (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) 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.
|
|
|
Members
Search
Help
Register
Login
Home
