|
Home > Archive > MySQL ODBC Connector > February 2006 > mySQL 5 and CPu at 99.99%
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
mySQL 5 and CPu at 99.99%
|
|
|
| Greetings,
We are running a server and the CPU is at %99.99 at all times, after about
2-3 hours of processing queries just hang, sounds like our hardware is weak
but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.
I was hoping someone could look at our settings and would help us analyze
this issue:
Please advise.
Here are the stats:
Some version information:
mySQL version: 5.0.16-standard
Uname: Linux db.example.com
2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
GNU/Linux
RAM: 4GB
SWAP 1GB
HD: 2 SCSI 10k RPM on 2 separate
controllers.
Some information about the load:
Queries per second avg: 16.346 (about)
Our biggest table is 3.5 million records and we index 3 of the columns for
fulltext search
We do a lot of join queries on 2 tables.
Some mySQL variables:
[mysqld]
tmpdir=/db.example.com/tmp
query_cache_size=104
8576
query_cache_limit = 33554432
query_cache_size = 33554432
myisam_sort_buffer_s
ize = 33554432
sort_buffer_size = 33554432
max_connections=500
table_cache = 1000
max_tmp_tables = 256
Here is all of my mysql -e 'SHOW VARIABLES':
+---------------------------------+-----------------------------------------
-+
| Variable_name | Value
|
+---------------------------------+-----------------------------------------
-+
| auto_increment_incre
ment | 1
|
| auto_increment_offse
t | 1
|
| automatic_sp_privile
ges | ON
|
| back_log | 50
|
| basedir | /
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_s
ize | 8388608
|
| character_set_client
| latin1
|
| character_set_connec
tion | latin1
|
| character_set_databa
se | latin1
|
| character_set_result
s | latin1
|
| character_set_server
| latin1
|
| character_set_system
| utf8
|
| character_sets_dir | /usr/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 | /var/lib/mysql/
|
| 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_timeo
ut | 300
|
| delayed_queue_size | 1000
|
| div_precision_increm
ent | 4
|
| engine_condition_pus
hdown | OFF
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 2
|
| ft_query_expansion_l
imit | 20
|
| ft_stopword_file | (built-in)
|
| group_concat_max_len
| 1024
|
| have_archive | YES
|
| have_bdb | NO
|
| have_blackhole_engin
e | NO
|
| have_compress | YES
|
| have_crypt | YES
|
| have_csv | NO
|
| have_example_engine | NO
|
| have_federated_engin
e | 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_me
m_pool_size | 1048576
|
| innodb_autoextend_in
crement | 8
|
| innodb_buffer_pool_a
we_mem_mb | 0
|
| innodb_buffer_pool_s
ize | 8388608
|
| innodb_checksums | ON
|
| innodb_commit_concur
rency | 0
|
| innodb_concurrency_t
ickets | 500
|
| innodb_data_file_pat
h | ibdata1:10M:autoexte
nd
|
| innodb_data_home_dir
|
|
| innodb_doublewrite | ON
|
| innodb_fast_shutdown
| 1
|
| innodb_file_io_threa
ds | 4
|
| innodb_file_per_tabl
e | OFF
|
| innodb_flush_log_at_
trx_commit | 1
|
| innodb_flush_method |
|
| innodb_force_recover
y | 0
|
| innodb_lock_wait_tim
eout | 50
|
| innodb_locks_unsafe_
for_binlog | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_si
ze | 1048576
|
| innodb_log_file_size
| 5242880
|
| innodb_log_files_in_
group | 2
|
| innodb_log_group_hom
e_dir | ./
|
| innodb_max_dirty_pag
es_pct | 90
|
| innodb_max_purge_lag
| 0
|
| innodb_mirrored_log_
groups | 1
|
| innodb_open_files | 300
|
| innodb_support_xa | ON
|
| innodb_sync_spin_loo
ps | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concur
rency | 20
|
| innodb_thread_sleep_
delay | 10000
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388600
|
| key_cache_age_thresh
old | 300
|
| key_cache_block_size
| 1024
|
| key_cache_division_l
imit | 100
|
| language | /usr/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 | OFF
|
| log_bin_trust_functi
on_creators | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates
| OFF
|
| lower_case_file_syst
em | OFF
|
| lower_case_table_nam
es | 0
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_siz
e | 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_t
hreads | 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_tmp_tables | 256
|
| 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_optio
ns | OFF
|
| myisam_repair_thread
s | 1
|
| myisam_sort_buffer_s
ize | 33554432
|
| 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_leve
l | 1
|
| optimizer_search_dep
th | 62
|
| pid_file | /var/lib/mysql/db.example.com.pid |
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_si
ze | 8192
|
| query_cache_limit | 33554432
|
| query_cache_min_res_
unit | 4096
|
| query_cache_size | 33554432
|
| query_cache_type | ON
|
| query_cache_wlock_in
validate | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_si
ze | 2048
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size
| 262144
|
| relay_log_purge | ON
|
| relay_log_space_limi
t | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| server_id | 0
|
| skip_external_lockin
g | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_compressed_pro
tocol | OFF
|
| slave_load_tmpdir | /db.example.com/tmp/ |
| slave_net_timeout | 3600
|
| slave_skip_errors | OFF
|
| slave_transaction_re
tries | 10
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 33554424
|
| sql_mode |
|
| sql_notes | ON
|
| sql_warnings | ON
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| sync_replication | 0
|
| sync_replication_sla
ve_id | 0
|
| sync_replication_tim
eout | 10
|
| system_time_zone | CST
|
| table_cache | 1000
|
| table_lock_wait_time
out | 50
|
| table_type | MyISAM
|
| thread_cache_size | 0
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| tmp_table_size | 33554432
|
| tmpdir | /db.example.com/tmp |
| transaction_alloc_bl
ock_size | 8192
|
| transaction_prealloc
_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| updatable_views_with
_limit | YES
|
| version | 5.0.16-standard
|
| version_comment | MySQL Community Edition - Standard (GPL)
|
| version_compile_mach
ine | x86_64
|
| version_compile_os | unknown-linux-gnu
|
| wait_timeout | 28800
|
+---------------------------------+-----------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Dan Baughman 2006-02-28, 8:28 pm |
| ------=_Part_8972_5001745.1141091233449
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Are you doing a lot of insert statements?
I noticed:
ft_max_word_len: 84
ft_min_word_len : 2
And if you have sizable full text index insert statements, I could you see
having more than normal cpu usage.
On 2/27/06, Taiyo <taiyo@sortprice.com> wrote:
>
> Greetings,
>
> We are running a server and the CPU is at %99.99 at all times, after abou=
t
> 2-3 hours of processing queries just hang, sounds like our hardware is
> weak
> but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.
>
> I was hoping someone could look at our settings and would help us analyze
> this issue:
>
> Please advise.
>
> Here are the stats:
>
> Some version information:
>
> mySQL version: 5.0.16-standard
> Uname: Linux db.example.com
> 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
> GNU/Linux
> RAM: 4GB
> SWAP 1GB
> HD: 2 SCSI 10k RPM on 2 separat=
e
> controllers.
>
> Some information about the load:
> Queries per second avg: 16.346 (about)
> Our biggest table is 3.5 million records and we index 3 of the columns fo=
r
> fulltext search
> We do a lot of join queries on 2 tables.
>
> Some mySQL variables:
> [mysqld]
> tmpdir=3D/db.example.com/tmp
> query_cache_size=3D1
048576
> query_cache_limit =3D 33554432
> query_cache_size =3D 33554432
> myisam_sort_buffer_s
ize =3D 33554432
> sort_buffer_size =3D 33554432
> max_connections=3D50
0
> table_cache =3D 1000
> max_tmp_tables =3D 256
>
> Here is all of my mysql -e 'SHOW VARIABLES':
>
> +---------------------------------+--------------------------------------=
---
> -+
> | Variable_name | Value
> |
>
> +---------------------------------+--------------------------------------=
---
> -+
> | auto_increment_incre
ment | 1
> |
> | auto_increment_offse
t | 1
> |
> | automatic_sp_privile
ges | ON
> |
> | back_log | 50
> |
> | basedir | /
> |
> | binlog_cache_size | 32768
> |
> | bulk_insert_buffer_s
ize | 8388608
> |
> | character_set_client
| latin1
> |
> | character_set_connec
tion | latin1
> |
> | character_set_databa
se | latin1
> |
> | character_set_result
s | latin1
> |
> | character_set_server
| latin1
> |
> | character_set_system
| utf8
> |
> | character_sets_dir | /usr/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 | /var/lib/mysql/
> |
> | 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_timeo
ut | 300
> |
> | delayed_queue_size | 1000
> |
> | div_precision_increm
ent | 4
> |
> | engine_condition_pus
hdown | OFF
> |
> | expire_logs_days | 0
> |
> | flush | OFF
> |
> | flush_time | 0
> |
> | ft_boolean_syntax | + -><()~*:""&|
> |
> | ft_max_word_len | 84
> |
> | ft_min_word_len | 2
> |
> | ft_query_expansion_l
imit | 20
> |
> | ft_stopword_file | (built-in)
> |
> | group_concat_max_len
| 1024
> |
> | have_archive | YES
> |
> | have_bdb | NO
> |
> | have_blackhole_engin
e | NO
> |
> | have_compress | YES
> |
> | have_crypt | YES
> |
> | have_csv | NO
> |
> | have_example_engine | NO
> |
> | have_federated_engin
e | 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_me
m_pool_size | 1048576
> |
> | innodb_autoextend_in
crement | 8
> |
> | innodb_buffer_pool_a
we_mem_mb | 0
> |
> | innodb_buffer_pool_s
ize | 8388608
> |
> | innodb_checksums | ON
> |
> | innodb_commit_concur
rency | 0
> |
> | innodb_concurrency_t
ickets | 500
> |
> | innodb_data_file_pat
h | ibdata1:10M:autoexte
nd
> |
> | innodb_data_home_dir
|
> |
> | innodb_doublewrite | ON
> |
> | innodb_fast_shutdown
| 1
> |
> | innodb_file_io_threa
ds | 4
> |
> | innodb_file_per_tabl
e | OFF
> |
> | innodb_flush_log_at_
trx_commit | 1
> |
> | innodb_flush_method |
> |
> | innodb_force_recover
y | 0
> |
> | innodb_lock_wait_tim
eout | 50
> |
> | innodb_locks_unsafe_
for_binlog | OFF
> |
> | innodb_log_arch_dir |
> |
> | innodb_log_archive | OFF
> |
> | innodb_log_buffer_si
ze | 1048576
> |
> | innodb_log_file_size
| 5242880
> |
> | innodb_log_files_in_
group | 2
> |
> | innodb_log_group_hom
e_dir | ./
> |
> | innodb_max_dirty_pag
es_pct | 90
> |
> | innodb_max_purge_lag
| 0
> |
> | innodb_mirrored_log_
groups | 1
> |
> | innodb_open_files | 300
> |
> | innodb_support_xa | ON
> |
> | innodb_sync_spin_loo
ps | 20
> |
> | innodb_table_locks | ON
> |
> | innodb_thread_concur
rency | 20
> |
> | innodb_thread_sleep_
delay | 10000
> |
> | interactive_timeout | 28800
> |
> | join_buffer_size | 131072
> |
> | key_buffer_size | 8388600
> |
> | key_cache_age_thresh
old | 300
> |
> | key_cache_block_size
| 1024
> |
> | key_cache_division_l
imit | 100
> |
> | language | /usr/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 | OFF
> |
> | log_bin_trust_functi
on_creators | OFF
> |
> | log_error |
> |
> | log_slave_updates | OFF
> |
> | log_slow_queries | OFF
> |
> | log_warnings | 1
> |
> | long_query_time | 10
> |
> | low_priority_updates
| OFF
> |
> | lower_case_file_syst
em | OFF
> |
> | lower_case_table_nam
es | 0
> |
> | max_allowed_packet | 1048576
> |
> | max_binlog_cache_siz
e | 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_t
hreads | 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_tmp_tables | 256
> |
> | 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_optio
ns | OFF
> |
> | myisam_repair_thread
s | 1
> |
> | myisam_sort_buffer_s
ize | 33554432
> |
> | 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_leve
l | 1
> |
> | optimizer_search_dep
th | 62
> |
> | pid_file | /var/lib/mysql/db.example.com.pid =
|
> | port | 3306
> |
> | preload_buffer_size | 32768
> |
> | protocol_version | 10
> |
> | query_alloc_block_si
ze | 8192
> |
> | query_cache_limit | 33554432
> |
> | query_cache_min_res_
unit | 4096
> |
> | query_cache_size | 33554432
> |
> | query_cache_type | ON
> |
> | query_cache_wlock_in
validate | OFF
> |
> | query_prealloc_size | 8192
> |
> | range_alloc_block_si
ze | 2048
> |
> | read_buffer_size | 131072
> |
> | read_only | OFF
> |
> | read_rnd_buffer_size
| 262144
> |
> | relay_log_purge | ON
> |
> | relay_log_space_limi
t | 0
> |
> | rpl_recovery_rank | 0
> |
> | secure_auth | OFF
> |
> | server_id | 0
> |
> | skip_external_lockin
g | ON
> |
> | skip_networking | OFF
> |
> | skip_show_database | OFF
> |
> | slave_compressed_pro
tocol | OFF
> |
> | slave_load_tmpdir | /db.example.com/tmp/ =
|
> | slave_net_timeout | 3600
> |
> | slave_skip_errors | OFF
> |
> | slave_transaction_re
tries | 10
> |
> | slow_launch_time | 2
> |
> | socket | /var/lib/mysql/mysql.sock
> |
> | sort_buffer_size | 33554424
> |
> | sql_mode |
> |
> | sql_notes | ON
> |
> | sql_warnings | ON
> |
> | storage_engine | MyISAM
> |
> | sync_binlog | 0
> |
> | sync_frm | ON
> |
> | sync_replication | 0
> |
> | sync_replication_sla
ve_id | 0
> |
> | sync_replication_tim
eout | 10
> |
> | system_time_zone | CST
> |
> | table_cache | 1000
> |
> | table_lock_wait_time
out | 50
> |
> | table_type | MyISAM
> |
> | thread_cache_size | 0
> |
> | thread_stack | 196608
> |
> | time_format | %H:%i:%s
> |
> | time_zone | SYSTEM
> |
> | timed_mutexes | OFF
> |
> | tmp_table_size | 33554432
> |
> | tmpdir | /db.example.com/tmp =
|
> | transaction_alloc_bl
ock_size | 8192
> |
> | transaction_prealloc
_size | 4096
> |
> | tx_isolation | REPEATABLE-READ
> |
> | updatable_views_with
_limit | YES
> |
> | version | 5.0.16-standard
> |
> | version_comment | MySQL Community Edition - Standard
> (GPL)
> |
> | version_compile_mach
ine | x86_64
> |
> | version_compile_os | unknown-linux-gnu
> |
> | wait_timeout | 28800
> |
>
> +---------------------------------+--------------------------------------=
---
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=...ghman@gmail.com
>
>
------=_Part_8972_5001745.1141091233449--
| |
| leo huang 2006-02-28, 8:28 pm |
| ------ =_Part_13621_1984874
7.1141116426328
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
hi, Taiyo
>| innodb_buffer_pool_s
ize | 8388608
>| key_buffer_size | 8388600
Try to increase the innodb_buffer_pool_s
ize if you use the innodb storage o=
r
key_buffer_size if MyISAM storage is used or both.
You can get more information about innodb_buffer_pool_s
ize and
key_buffer_size from this:
http://dev.mysql.com/doc/refman/5.0...parameters.html
http://dev.mysql.com/doc/refman/5.0...-variables.html
Best regards,
Leo Huang
2006/2/28, Taiyo <taiyo@sortprice.com>:
> Greetings,
>
> We are running a server and the CPU is at %99.99 at all times, after abou=
t
> 2-3 hours of processing queries just hang, sounds like our hardware is
weak
> but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.
>
> I was hoping someone could look at our settings and would help us analyze
> this issue:
>
> Please advise.
>
> Here are the stats:
>
> Some version information:
>
> mySQL version: 5.0.16-standard
> Uname: Linux db.example.com
> 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
> GNU/Linux
> RAM: 4GB
> SWAP 1GB
> HD: 2 SCSI 10k RPM on 2 separat=
e
> controllers.
>
> Some information about the load:
> Queries per second avg: 16.346 (about)
> Our biggest table is 3.5 million records and we index 3 of the columns fo=
r
> fulltext search
> We do a lot of join queries on 2 tables.
>
> Some mySQL variables:
> [mysqld]
> tmpdir=3D/db.example.com/tmp
> query_cache_size=3D1
048576
> query_cache_limit =3D 33554432
> query_cache_size =3D 33554432
> myisam_sort_buffer_s
ize =3D 33554432
> sort_buffer_size =3D 33554432
> max_connections=3D50
0
> table_cache =3D 1000
> max_tmp_tables =3D 256
>
> Here is all of my mysql -e 'SHOW VARIABLES':
>
+---------------------------------+----------------------------------------=
-
> -+
> | Variable_name | Value
> |
>
+---------------------------------+----------------------------------------=
-
> -+
> | auto_increment_incre
ment | 1
> |
> | auto_increment_offse
t | 1
> |
> | automatic_sp_privile
ges | ON
> |
> | back_log | 50
> |
> | basedir | /
> |
> | binlog_cache_size | 32768
> |
> | bulk_insert_buffer_s
ize | 8388608
> |
> | character_set_client
| latin1
> |
> | character_set_connec
tion | latin1
> |
> | character_set_databa
se | latin1
> |
> | character_set_result
s | latin1
> |
> | character_set_server
| latin1
> |
> | character_set_system
| utf8
> |
> | character_sets_dir | /usr/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 | /var/lib/mysql/
> |
> | 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_timeo
ut | 300
> |
> | delayed_queue_size | 1000
> |
> | div_precision_increm
ent | 4
> |
> | engine_condition_pus
hdown | OFF
> |
> | expire_logs_days | 0
> |
> | flush | OFF
> |
> | flush_time | 0
> |
> | ft_boolean_syntax | + -><()~*:""&|
> |
> | ft_max_word_len | 84
> |
> | ft_min_word_len | 2
> |
> | ft_query_expansion_l
imit | 20
> |
> | ft_stopword_file | (built-in)
> |
> | group_concat_max_len
| 1024
> |
> | have_archive | YES
> |
> | have_bdb | NO
> |
> | have_blackhole_engin
e | NO
> |
> | have_compress | YES
> |
> | have_crypt | YES
> |
> | have_csv | NO
> |
> | have_example_engine | NO
> |
> | have_federated_engin
e | 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_me
m_pool_size | 1048576
> |
> | innodb_autoextend_in
crement | 8
> |
> | innodb_buffer_pool_a
we_mem_mb | 0
> |
> | innodb_buffer_pool_s
ize | 8388608
> |
> | innodb_checksums | ON
> |
> | innodb_commit_concur
rency | 0
> |
> | innodb_concurrency_t
ickets | 500
> |
> | innodb_data_file_pat
h | ibdata1:10M:autoexte
nd
> |
> | innodb_data_home_dir
|
> |
> | innodb_doublewrite | ON
> |
> | innodb_fast_shutdown
| 1
> |
> | innodb_file_io_threa
ds | 4
> |
> | innodb_file_per_tabl
e | OFF
> |
> | innodb_flush_log_at_
trx_commit | 1
> |
> | innodb_flush_method |
> |
> | innodb_force_recover
y | 0
> |
> | innodb_lock_wait_tim
eout | 50
> |
> | innodb_locks_unsafe_
for_binlog | OFF
> |
> | innodb_log_arch_dir |
> |
> | innodb_log_archive | OFF
> |
> | innodb_log_buffer_si
ze | 1048576
> |
> | innodb_log_file_size
| 5242880
> |
> | innodb_log_files_in_
group | 2
> |
> | innodb_log_group_hom
e_dir | ./
> |
> | innodb_max_dirty_pag
es_pct | 90
> |
> | innodb_max_purge_lag
| 0
> |
> | innodb_mirrored_log_
groups | 1
> |
> | innodb_open_files | 300
> |
> | innodb_support_xa | ON
> |
> | innodb_sync_spin_loo
ps | 20
> |
> | innodb_table_locks | ON
> |
> | innodb_thread_concur
rency | 20
> |
> | innodb_thread_sleep_
delay | 10000
> |
> | interactive_timeout | 28800
> |
> | join_buffer_size | 131072
> |
> | key_buffer_size | 8388600
> |
> | key_cache_age_thresh
old | 300
> |
> | key_cache_block_size
| 1024
> |
> | key_cache_division_l
imit | 100
> |
> | language | /usr/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 | OFF
> |
> | log_bin_trust_functi
on_creators | OFF
> |
> | log_error |
> |
> | log_slave_updates | OFF
> |
> | log_slow_queries | OFF
> |
> | log_warnings | 1
> |
> | long_query_time | 10
> |
> | low_priority_updates
| OFF
> |
> | lower_case_file_syst
em | OFF
> |
> | lower_case_table_nam
es | 0
> |
> | max_allowed_packet | 1048576
> |
> | max_binlog_cache_siz
e | 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_t
hreads | 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_tmp_tables | 256
> |
> | 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_optio
ns | OFF
> |
> | myisam_repair_thread
s | 1
> |
> | myisam_sort_buffer_s
ize | 33554432
> |
> | 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_leve
l | 1
> |
> | optimizer_search_dep
th | 62
> |
> | pid_file | /var/lib/mysql/db.example.com.pid =
|
> | port | 3306
> |
> | preload_buffer_size | 32768
> |
> | protocol_version | 10
> |
> | query_alloc_block_si
ze | 8192
> |
> | query_cache_limit | 33554432
> |
> | query_cache_min_res_
unit | 4096
> |
> | query_cache_size | 33554432
> |
> | query_cache_type | ON
> |
> | query_cache_wlock_in
validate | OFF
> |
> | query_prealloc_size | 8192
> |
> | range_alloc_block_si
ze | 2048
> |
> | read_buffer_size | 131072
> |
> | read_only | OFF
> |
> | read_rnd_buffer_size
| 262144
> |
> | relay_log_purge | ON
> |
> | relay_log_space_limi
t | 0
> |
> | rpl_recovery_rank | 0
> |
> | secure_auth | OFF
> |
> | server_id | 0
> |
> | skip_external_lockin
g | ON
> |
> | skip_networking | OFF
> |
> | skip_show_database | OFF
> |
> | slave_compressed_pro
tocol | OFF
> |
> | slave_load_tmpdir | /db.example.com/tmp/ =
|
> | slave_net_timeout | 3600
> |
> | slave_skip_errors | OFF
> |
> | slave_transaction_re
tries | 10
> |
> | slow_launch_time | 2
> |
> | socket | /var/lib/mysql/mysql.sock
> |
> | sort_buffer_size | 33554424
> |
> | sql_mode |
> |
> | sql_notes | ON
> |
> | sql_warnings | ON
> |
> | storage_engine | MyISAM
> |
> | sync_binlog | 0
> |
> | sync_frm | ON
> |
> | sync_replication | 0
> |
> | sync_replication_sla
ve_id | 0
> |
> | sync_replication_tim
eout | 10
> |
> | system_time_zone | CST
> |
> | table_cache | 1000
> |
> | table_lock_wait_time
out | 50
> |
> | table_type | MyISAM
> |
> | thread_cache_size | 0
> |
> | thread_stack | 196608
> |
> | time_format | %H:%i:%s
> |
> | time_zone | SYSTEM
> |
> | timed_mutexes | OFF
> |
> | tmp_table_size | 33554432
> |
> | tmpdir | /db.example.com/tmp =
|
> | transaction_alloc_bl
ock_size | 8192
> |
> | transaction_prealloc
_size | 4096
> |
> | tx_isolation | REPEATABLE-READ
> |
> | updatable_views_with
_limit | YES
> |
> | version | 5.0.16-standard
> |
> | version_comment | MySQL Community Edition - Standard
(GPL)
> |
> | version_compile_mach
ine | x86_64
> |
> | version_compile_os | unknown-linux-gnu
> |
> | wait_timeout | 28800
> |
>
+---------------------------------+----------------------------------------=
-
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=....list@gmail.com
>
>
------ =_Part_13621_1984874
7.1141116426328--
| |
|
| Thank you very much, I will have a look at it.
-----Original Message-----
From: leo huang [mailto:leo.huang.list@gmail.com]
Sent: Tuesday, February 28, 2006 3:47 AM
To: Taiyo
Cc: mysql@lists.mysql.com
Subject: Re: mySQL 5 and CPu at 99.99%
hi, Taiyo
>| innodb_buffer_pool_s
ize | 8388608
>| key_buffer_size | 8388600
Try to increase the innodb_buffer_pool_s
ize if you use the innodb storage or
key_buffer_size if MyISAM storage is used or both.
You can get more information about innodb_buffer_pool_s
ize and
key_buffer_size from this:
http://dev.mysql.com/doc/refman/5.0...parameters.html
http://dev.mysql.com/doc/refman/5.0...-variables.html
Best regards,
Leo Huang
2006/2/28, Taiyo <taiyo@sortprice.com>:
> Greetings,
>
> We are running a server and the CPU is at %99.99 at all times, after about
> 2-3 hours of processing queries just hang, sounds like our hardware is
weak
> but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.
>
> I was hoping someone could look at our settings and would help us analyze
> this issue:
>
> Please advise.
>
> Here are the stats:
>
> Some version information:
>
> mySQL version: 5.0.16-standard
> Uname: Linux db.example.com
> 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
> GNU/Linux
> RAM: 4GB
> SWAP 1GB
> HD: 2 SCSI 10k RPM on 2 separate
> controllers.
>
> Some information about the load:
> Queries per second avg: 16.346 (about)
> Our biggest table is 3.5 million records and we index 3 of the columns for
> fulltext search
> We do a lot of join queries on 2 tables.
>
> Some mySQL variables:
> [mysqld]
> tmpdir=/db.example.com/tmp
> query_cache_size=104
8576
> query_cache_limit = 33554432
> query_cache_size = 33554432
> myisam_sort_buffer_s
ize = 33554432
> sort_buffer_size = 33554432
> max_connections=500
> table_cache = 1000
> max_tmp_tables = 256
>
> Here is all of my mysql -e 'SHOW VARIABLES':
>
+---------------------------------+-----------------------------------------
> -+
> | Variable_name | Value
> |
>
+---------------------------------+-----------------------------------------
> -+
> | auto_increment_incre
ment | 1
> |
> | auto_increment_offse
t | 1
> |
> | automatic_sp_privile
ges | ON
> |
> | back_log | 50
> |
> | basedir | /
> |
> | binlog_cache_size | 32768
> |
> | bulk_insert_buffer_s
ize | 8388608
> |
> | character_set_client
| latin1
> |
> | character_set_connec
tion | latin1
> |
> | character_set_databa
se | latin1
> |
> | character_set_result
s | latin1
> |
> | character_set_server
| latin1
> |
> | character_set_system
| utf8
> |
> | character_sets_dir | /usr/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 | /var/lib/mysql/
> |
> | 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_timeo
ut | 300
> |
> | delayed_queue_size | 1000
> |
> | div_precision_increm
ent | 4
> |
> | engine_condition_pus
hdown | OFF
> |
> | expire_logs_days | 0
> |
> | flush | OFF
> |
> | flush_time | 0
> |
> | ft_boolean_syntax | + -><()~*:""&|
> |
> | ft_max_word_len | 84
> |
> | ft_min_word_len | 2
> |
> | ft_query_expansion_l
imit | 20
> |
> | ft_stopword_file | (built-in)
> |
> | group_concat_max_len
| 1024
> |
> | have_archive | YES
> |
> | have_bdb | NO
> |
> | have_blackhole_engin
e | NO
> |
> | have_compress | YES
> |
> | have_crypt | YES
> |
> | have_csv | NO
> |
> | have_example_engine | NO
> |
> | have_federated_engin
e | 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_me
m_pool_size | 1048576
> |
> | innodb_autoextend_in
crement | 8
> |
> | innodb_buffer_pool_a
we_mem_mb | 0
> |
> | innodb_buffer_pool_s
ize | 8388608
> |
> | innodb_checksums | ON
> |
> | innodb_commit_concur
rency | 0
> |
> | innodb_concurrency_t
ickets | 500
> |
> | innodb_data_file_pat
h | ibdata1:10M:autoexte
nd
> |
> | innodb_data_home_dir
|
> |
> | innodb_doublewrite | ON
> |
> | innodb_fast_shutdown
| 1
> |
> | innodb_file_io_threa
ds | 4
> |
> | innodb_file_per_tabl
e | OFF
> |
> | innodb_flush_log_at_
trx_commit | 1
> |
> | innodb_flush_method |
> |
> | innodb_force_recover
y | 0
> |
> | innodb_lock_wait_tim
eout | 50
> |
> | innodb_locks_unsafe_
for_binlog | OFF
> |
> | innodb_log_arch_dir |
> |
> | innodb_log_archive | OFF
> |
> | innodb_log_buffer_si
ze | 1048576
> |
> | innodb_log_file_size
| 5242880
> |
> | innodb_log_files_in_
group | 2
> |
> | innodb_log_group_hom
e_dir | ./
> |
> | innodb_max_dirty_pag
es_pct | 90
> |
> | innodb_max_purge_lag
| 0
> |
> | innodb_mirrored_log_
groups | 1
> |
> | innodb_open_files | 300
> |
> | innodb_support_xa | ON
> |
> | innodb_sync_spin_loo
ps | 20
> |
> | innodb_table_locks | ON
> |
> | innodb_thread_concur
rency | 20
> |
> | innodb_thread_sleep_
delay | 10000
> |
> | interactive_timeout | 28800
> |
> | join_buffer_size | 131072
> |
> | key_buffer_size | 8388600
> |
> | key_cache_age_thresh
old | 300
> |
> | key_cache_block_size
| 1024
> |
> | key_cache_division_l
imit | 100
> |
> | language | /usr/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 | OFF
> |
> | log_bin_trust_functi
on_creators | OFF
> |
> | log_error |
> |
> | log_slave_updates | OFF
> |
> | log_slow_queries | OFF
> |
> | log_warnings | 1
> |
> | long_query_time | 10
> |
> | low_priority_updates
| OFF
> |
> | lower_case_file_syst
em | OFF
> |
> | lower_case_table_nam
es | 0
> |
> | max_allowed_packet | 1048576
> |
> | max_binlog_cache_siz
e | 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_t
hreads | 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_tmp_tables | 256
> |
> | 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_optio
ns | OFF
> |
> | myisam_repair_thread
s | 1
> |
> | myisam_sort_buffer_s
ize | 33554432
> |
> | 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_leve
l | 1
> |
> | optimizer_search_dep
th | 62
> |
> | pid_file | /var/lib/mysql/db.example.com.pid |
> | port | 3306
> |
> | preload_buffer_size | 32768
> |
> | protocol_version | 10
> |
> | query_alloc_block_si
ze | 8192
> |
> | query_cache_limit | 33554432
> |
> | query_cache_min_res_
unit | 4096
> |
> | query_cache_size | 33554432
> |
> | query_cache_type | ON
> |
> | query_cache_wlock_in
validate | OFF
> |
> | query_prealloc_size | 8192
> |
> | range_alloc_block_si
ze | 2048
> |
> | read_buffer_size | 131072
> |
> | read_only | OFF
> |
> | read_rnd_buffer_size
| 262144
> |
> | relay_log_purge | ON
> |
> | relay_log_space_limi
t | 0
> |
> | rpl_recovery_rank | 0
> |
> | secure_auth | OFF
> |
> | server_id | 0
> |
> | skip_external_lockin
g | ON
> |
> | skip_networking | OFF
> |
> | skip_show_database | OFF
> |
> | slave_compressed_pro
tocol | OFF
> |
> | slave_load_tmpdir | /db.example.com/tmp/ |
> | slave_net_timeout | 3600
> |
> | slave_skip_errors | OFF
> |
> | slave_transaction_re
tries | 10
> |
> | slow_launch_time | 2
> |
> | socket | /var/lib/mysql/mysql.sock
> |
> | sort_buffer_size | 33554424
> |
> | sql_mode |
> |
> | sql_notes | ON
> |
> | sql_warnings | ON
> |
> | storage_engine | MyISAM
> |
> | sync_binlog | 0
> |
> | sync_frm | ON
> |
> | sync_replication | 0
> |
> | sync_replication_sla
ve_id | 0
> |
> | sync_replication_tim
eout | 10
> |
> | system_time_zone | CST
> |
> | table_cache | 1000
> |
> | table_lock_wait_time
out | 50
> |
> | table_type | MyISAM
> |
> | thread_cache_size | 0
> |
> | thread_stack | 196608
> |
> | time_format | %H:%i:%s
> |
> | time_zone | SYSTEM
> |
> | timed_mutexes | OFF
> |
> | tmp_table_size | 33554432
> |
> | tmpdir | /db.example.com/tmp |
> | transaction_alloc_bl
ock_size | 8192
> |
> | transaction_prealloc
_size | 4096
> |
> | tx_isolation | REPEATABLE-READ
> |
> | updatable_views_with
_limit | YES
> |
> | version | 5.0.16-standard
> |
> | version_comment | MySQL Community Edition - Standard
(GPL)
> |
> | version_compile_mach
ine | x86_64
> |
> | version_compile_os | unknown-linux-gnu
> |
> | wait_timeout | 28800
> |
>
+---------------------------------+-----------------------------------------
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=....list@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|