6

I have a Web server running on RHEL that is running Apache and MySQL. It has a Quad core 3.2Ghz Xeon CPU and 8 Gigs of RAM Most of the time, we don't have any issues at all.

Our web application is very database intensive. When our usage gets pretty heavy MySQL will peg out at using 24.9% of the cpu. Most of the time, it hangs around below 5%. I have speculated that it is only using one core of the CPU and it is pegging out that core but TOP shows me in the cpu column that mysqld changes cores even while the usage stays at 24.9%. When it does this MySQL gets painfully slow as it is queuing up queries Is there some magic configuration that will tell mysql to use more cpu when it needs to? Also, any other advice on my configuration would be helpful.

We run two applications on this server. One that runs Innodb but doesn't get much usage (it has been replaced by the other app), and one that runs MyIsam and gets lots of use. Overall, our whole mysql data directory is something like 13Gigs if that matters at all.

Here is my config:

[root@ProductionLinux root]# cat /etc/my.cnf
[mysqld]
server-id       = 71
log-bin         = /var/log/mysql/mysql-bin.log
binlog-do-db    = oldapplication
binlog-do-db    = newapplication
binlog-do-db    = support
thread_cache_size = 30
key_buffer_size = 256M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M
skip-name-resolve

innodb_data_home_dir            = /usr/local/mysql/data/
innodb_data_file_path           = InnoDB:100M:autoextend
set-variable                    = innodb_buffer_pool_size=70M
set-variable                    = innodb_additional_mem_pool_size=10M
set-variable                    = max_connections=500
innodb_log_group_home_dir       = /usr/local/mysql/data
innodb_log_arch_dir             = /usr/local/mysql/data
set-variable                    = innodb_log_file_size=20M
set-variable                    = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit  = 1
log-queries-not-using-indexes
log-error = /var/log/mysql/mysql-error.log
mysql> show variables;
+---------------------------------+-----------------------------------------------------------------------------+
| Variable_name                   | Value                                                                       |
+---------------------------------+-----------------------------------------------------------------------------+
| auto_increment_increment        | 1                                                                           |
| auto_increment_offset           | 1                                                                           |
| automatic_sp_privileges         | ON                                                                          |
| back_log                        | 50                                                                          |
| basedir                         | /usr/local/mysql-standard-5.0.18-linux-x86_64-glibc23/                      |
| binlog_cache_size               | 32768                                                                       |
| bulk_insert_buffer_size         | 8388608                                                                     |
| character_set_client            | latin1                                                                      |
| character_set_connection        | latin1                                                                      |
| character_set_database          | latin1                                                                      |
| character_set_results           | latin1                                                                      |
| character_set_server            | latin1                                                                      |
| character_set_system            | utf8                                                                        |
| character_sets_dir              | /usr/local/mysql-standard-5.0.18-linux-x86_64-glibc23/share/mysql/charsets/ |
| collation_connection            | latin1_swedish_ci                                                           |
| collation_database              | latin1_swedish_ci                                                           |
| collation_server                | latin1_swedish_ci                                                           |
| completion_type                 | 0                                                                           |
| concurrent_insert               | 1                                                                           |
| connect_timeout                 | 5                                                                           |
| datadir                         | /usr/local/mysql/data/                                                      |
| date_format                     | %Y-%m-%d                                                                    |
| datetime_format                 | %Y-%m-%d %H:%i:%s                                                           |
| default_week_format             | 0                                                                           |
| delay_key_write                 | ON                                                                          |
| delayed_insert_limit            | 100                                                                         |
| delayed_insert_timeout          | 300                                                                         |
| delayed_queue_size              | 1000                                                                        |
| div_precision_increment         | 4                                                                           |
| engine_condition_pushdown       | OFF                                                                         |
| expire_logs_days                | 0                                                                           |
| flush                           | OFF                                                                         |
| flush_time                      | 0                                                                           |                                                             |
| ft_max_word_len                 | 84                                                                          |
| ft_min_word_len                 | 4                                                                           |
| ft_query_expansion_limit        | 20                                                                          |
| ft_stopword_file                | (built-in)                                                                  |
| group_concat_max_len            | 1024                                                                        |
| have_archive                    | YES                                                                         |
| have_bdb                        | NO                                                                          |
| have_blackhole_engine           | NO                                                                          |
| have_compress                   | YES                                                                         |
| have_crypt                      | YES                                                                         |
| have_csv                        | NO                                                                          |
| have_example_engine             | NO                                                                          |
| have_federated_engine           | NO                                                                          |
| have_geometry                   | YES                                                                         |
| have_innodb                     | YES                                                                         |
| have_isam                       | NO                                                                          |
| have_ndbcluster                 | NO                                                                          |
| have_openssl                    | NO                                                                          |
| have_query_cache                | YES                                                                         |
| have_raid                       | NO                                                                          |
| have_rtree_keys                 | YES                                                                         |
| have_symlink                    | YES                                                                         |
| init_connect                    |                                                                             |
| init_file                       |                                                                             |
| init_slave                      |                                                                             |
| innodb_additional_mem_pool_size | 10485760                                                                    |
| innodb_autoextend_increment     | 8                                                                           |
| innodb_buffer_pool_awe_mem_mb   | 0                                                                           |
| innodb_buffer_pool_size         | 73400320                                                                    |
| innodb_checksums                | ON                                                                          |
| innodb_commit_concurrency       | 0                                                                           |
| innodb_concurrency_tickets      | 500                                                                         |
| innodb_data_file_path           | InnoDB:100M:autoextend                                                      |
| innodb_data_home_dir            | /usr/local/mysql/data/                                                      |
| innodb_doublewrite              | ON                                                                          |
| innodb_fast_shutdown            | 1                                                                           |
| innodb_file_io_threads          | 4                                                                           |
| innodb_file_per_table           | OFF                                                                         |
| innodb_flush_log_at_trx_commit  | 1                                                                           |
| innodb_flush_method             |                                                                             |
| innodb_force_recovery           | 0                                                                           |
| innodb_lock_wait_timeout        | 50                                                                          |
| innodb_locks_unsafe_for_binlog  | OFF                                                                         |
| innodb_log_arch_dir             | /usr/local/mysql/data                                                       |
| innodb_log_archive              | OFF                                                                         |
| innodb_log_buffer_size          | 8388608                                                                     |
| innodb_log_file_size            | 20971520                                                                    |
| innodb_log_files_in_group       | 2                                                                           |
| innodb_log_group_home_dir       | /usr/local/mysql/data                                                       |
| innodb_max_dirty_pages_pct      | 90                                                                          |
| innodb_max_purge_lag            | 0                                                                           |
| innodb_mirrored_log_groups      | 1                                                                           |
| innodb_open_files               | 300                                                                         |
| innodb_support_xa               | ON                                                                          |
| innodb_sync_spin_loops          | 20                                                                          |
| innodb_table_locks              | ON                                                                          |
| innodb_thread_concurrency       | 20                                                                          |
| innodb_thread_sleep_delay       | 10000                                                                       |
| interactive_timeout             | 28800                                                                       |
| join_buffer_size                | 131072                                                                      |
| key_buffer_size                 | 268435456                                                                   |
| key_cache_age_threshold         | 300                                                                         |
| key_cache_block_size            | 1024                                                                        |
| key_cache_division_limit        | 100                                                                         |
| language                        | /usr/local/mysql-standard-5.0.18-linux-x86_64-glibc23/share/mysql/english/  |
| large_files_support             | ON                                                                          |
| large_page_size                 | 0                                                                           |
| large_pages                     | OFF                                                                         |
| license                         | GPL                                                                         |
| local_infile                    | ON                                                                          |
| locked_in_memory                | OFF                                                                         |
| log                             | OFF                                                                         |
| log_bin                         | ON                                                                          |
| log_bin_trust_function_creators | OFF                                                                         |
| log_error                       | /var/log/mysql/mysql-error.log                                              |
| log_slave_updates               | OFF                                                                         |
| log_slow_queries                | OFF                                                                         |
| log_warnings                    | 1                                                                           |
| long_query_time                 | 10                                                                          |
| low_priority_updates            | OFF                                                                         |
| lower_case_file_system          | OFF                                                                         |
| lower_case_table_names          | 0                                                                           |
| max_allowed_packet              | 1048576                                                                     |
| max_binlog_cache_size           | 18446744073709551615                                                        |
| max_binlog_size                 | 1073741824                                                                  |
| max_connect_errors              | 10                                                                          |
| max_connections                 | 500                                                                         |
| max_delayed_threads             | 20                                                                          |
| max_error_count                 | 64                                                                          |
| max_heap_table_size             | 16777216                                                                    |
| max_insert_delayed_threads      | 20                                                                          |
| max_join_size                   | 18446744073709551615                                                        |
| max_length_for_sort_data        | 1024                                                                        |
| max_relay_log_size              | 0                                                                           |
| max_seeks_for_key               | 18446744073709551615                                                        |
| max_sort_length                 | 1024                                                                        |
| max_sp_recursion_depth          | 0                                                                           |
| max_tmp_tables                  | 32                                                                          |
| max_user_connections            | 0                                                                           |
| max_write_lock_count            | 18446744073709551615                                                        |
| multi_range_count               | 256                                                                         |
| myisam_data_pointer_size        | 6                                                                           |
| myisam_max_sort_file_size       | 9223372036854775807                                                         |
| myisam_recover_options          | OFF                                                                         |
| myisam_repair_threads           | 1                                                                           |
| myisam_sort_buffer_size         | 8388608                                                                     |
| myisam_stats_method             | nulls_unequal                                                               |
| net_buffer_length               | 16384                                                                       |
| net_read_timeout                | 30                                                                          |
| net_retry_count                 | 10                                                                          |
| net_write_timeout               | 60                                                                          |
| new                             | OFF                                                                         |
| old_passwords                   | OFF                                                                         |
| open_files_limit                | 2510                                                                        |
| optimizer_prune_level           | 1                                                                           |
| optimizer_search_depth          | 62                                                                          |
| pid_file                        | /usr/local/mysql/data/ProductionLinux.pid                                   |
| port                            | 3306                                                                        |
| preload_buffer_size             | 32768                                                                       |
| protocol_version                | 10                                                                          |
| query_alloc_block_size          | 8192                                                                        |
| query_cache_limit               | 1048576                                                                     |
| query_cache_min_res_unit        | 4096                                                                        |
| query_cache_size                | 0                                                                           |
| query_cache_type                | ON                                                                          |
| query_cache_wlock_invalidate    | OFF                                                                         |
| query_prealloc_size             | 8192                                                                        |
| range_alloc_block_size          | 2048                                                                        |
| read_buffer_size                | 1044480                                                                     |
| read_only                       | OFF                                                                         |
| read_rnd_buffer_size            | 262144                                                                      |
| relay_log_purge                 | ON                                                                          |
| relay_log_space_limit           | 0                                                                           |
| rpl_recovery_rank               | 0                                                                           |
| secure_auth                     | OFF                                                                         |
| server_id                       | 71                                                                          |
| skip_external_locking           | ON                                                                          |
| skip_networking                 | OFF                                                                         |
| skip_show_database              | OFF                                                                         |
| slave_compressed_protocol       | OFF                                                                         |
| slave_load_tmpdir               | /tmp/                                                                       |
| slave_net_timeout               | 3600                                                                        |
| slave_skip_errors               | OFF                                                                         |
| slave_transaction_retries       | 10                                                                          |
| slow_launch_time                | 2                                                                           |
| socket                          | /tmp/mysql.sock                                                             |
| sort_buffer_size                | 4194296                                                                     |
| sql_mode                        |                                                                             |
| sql_notes                       | ON                                                                          |
| sql_warnings                    | ON                                                                          |
| storage_engine                  | MyISAM                                                                      |
| sync_binlog                     | 0                                                                           |
| sync_frm                        | ON                                                                          |
| sync_replication                | 0                                                                           |
| sync_replication_slave_id       | 0                                                                           |
| sync_replication_timeout        | 10                                                                          |
| system_time_zone                | CST                                                                         |
| table_cache                     | 256                                                                         |
| table_lock_wait_timeout         | 50                                                                          |
| table_type                      | MyISAM                                                                      |
| thread_cache_size               | 30                                                                          |
| thread_stack                    | 262144                                                                      |
| time_format                     | %H:%i:%s                                                                    |
| time_zone                       | SYSTEM                                                                      |
| timed_mutexes                   | OFF                                                                         |
| tmp_table_size                  | 33554432                                                                    |
| tmpdir                          |                                                                             |
| transaction_alloc_block_size    | 8192                                                                        |
| transaction_prealloc_size       | 4096                                                                        |
| tx_isolation                    | REPEATABLE-READ                                                             |
| updatable_views_with_limit      | YES                                                                         |
| version                         | 5.0.18-standard-log                                                         |
| version_comment                 | MySQL Community Edition - Standard (GPL)                                    |
| version_compile_machine         | x86_64                                                                      |
| version_compile_os              | unknown-linux-gnu                                                           |
| wait_timeout                    | 28800                                                                       |
+---------------------------------+-----------------------------------------------------------------------------+
210 rows in set (0.00 sec)

2 Answers 2

1

Because you use a OLD version of MySQL that don't scale well with multiple cores.

Try to upgrade :)

10
  • 1
    5.0 definitely uses several threads. the problem here is table lockup
    – Javier
    Commented Jan 8, 2011 at 12:31
  • 1
    5.0 has lot of performances issues in comparison with the last versions, it's from Dec 2005...
    – Kedare
    Commented Jan 8, 2011 at 17:52
  • We are working on migrating our production machines to the cloud (similar to Amazon's cloud servers). The version of mysql that we have installed on the machine that we use there is 5.1.48-log. Do you think we will have similar issues once we complete our migration? Commented Jan 9, 2011 at 1:36
  • It will be better, but if you can, try to take a look at the 5.5, it will provide a huge performance boost
    – Kedare
    Commented Jan 9, 2011 at 12:16
  • 1
    we did migrate the server and it has been a null issue since then. It looks like the newer version of MySQL did solve this issue (and create more...) Commented Feb 4, 2011 at 20:43
6

This seems an obvious effect of updating a single MyISAM table. The whole table is locked during writes.

Either use more tables, or switch those with heavy writes to InnoDB. Since the table engine choice is per table, not per database, you can choose what is best for each one.

Remember, MyISAM is fast for mostly-read tables, InnoDB is best for almost everything else.

Also, if you have a single table with so many writes, consider changing your architecture. check queue managers (to send those updates to a non-interactive task), or append-only engines (fast for logs or archives, if that's what you're doing), or a different kind of server for tight jobs (like an in-memory DB if you're doing a lot of temporary processing before writing final results to more persistent storage)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .