Announcement

Announcement Module
Collapse
No announcement yet.

MYSQL -- CPU Max Out; Very Poor Response

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL -- CPU Max Out; Very Poor Response

    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]

  • #2
    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/...xt-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.

    Comment

    Working...
    X