cpu load 100%

[mesti]

I have a debian server with mysql 4.1
1 gb ddr2 ram, intel 3.200 cpu
cpu load always 100%

mysql> SHOW GLOBAL VARIABLES;±--------------------------------±-----------------------------+| Variable_name | Value |±--------------------------------±-----------------------------+| back_log | 50 || basedir | /usr/ || 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/share/mysql/charsets/ || collation_connection | latin1_swedish_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci || concurrent_insert | ON || 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_timeout | 300 || delayed_queue_size | 1000 || expire_logs_days | 5 || flush | OFF || flush_time | 0 || ft_boolean_syntax | + -><()~*:""&| || 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 | YES || have_example_engine | NO || have_geometry | YES || have_innodb | YES || have_isam | YES || have_ndbcluster | DISABLED || 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 | 1048576 || innodb_autoextend_increment | 8 || innodb_buffer_pool_awe_mem_mb | 0 || innodb_buffer_pool_size | 8388608 || innodb_data_file_path | ibdata1:10M:autoextend || innodb_data_home_dir | || innodb_fast_shutdown | ON || 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 | || innodb_log_archive | OFF || innodb_log_buffer_size | 1048576 || innodb_log_file_size | 5242880 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | ./ || innodb_max_dirty_pages_pct | 90 || innodb_max_purge_lag | 0 || innodb_mirrored_log_groups | 1 || innodb_open_files | 300 || innodb_table_locks | ON || innodb_thread_concurrency | 8 || interactive_timeout | 28800 || join_buffer_size | 131072 || key_buffer_size | 33554432 || key_cache_age_threshold | 300 || key_cache_block_size | 1024 || key_cache_division_limit | 100 || language | /usr/share/mysql/english/ || large_files_support | ON || license | GPL || local_infile | ON || locked_in_memory | OFF || log | OFF || log_bin | ON || log_error | || log_slave_updates | OFF || log_slow_queries | OFF || log_update | OFF || log_warnings | 1 || long_query_time | 4 || low_priority_updates | OFF || lower_case_file_system | OFF || lower_case_table_names | 0 || max_allowed_packet | 1073740800 || max_binlog_cache_size | 4294967295 || max_binlog_size | 104857600 || max_connect_errors | 10 || max_connections | 300 || 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 | 4294967295 || max_sort_length | 1024 || max_tmp_tables | 32 || max_user_connections | 0 || max_write_lock_count | 4294967295 || myisam_data_pointer_size | 4 || myisam_max_extra_sort_file_size | 2147483648 || myisam_max_sort_file_size | 2147483647 || myisam_recover_options | OFF || myisam_repair_threads | 1 || myisam_sort_buffer_size | 8388608 || myisam_stats_method | nulls_unequal || ndb_autoincrement_prefetch_sz | 32 || ndb_force_send | ON || ndb_use_exact_count | ON || ndb_use_transactions | OFF || net_buffer_length | 16384 || net_read_timeout | 30 || net_retry_count | 10 || net_write_timeout | 60 || new | OFF || old_passwords | OFF || open_files_limit | 1510 || pid_file | /var/run/mysqld/mysqld.pid || port | 3306 || preload_buffer_size | 32768 || protocol_version | 10 || query_alloc_block_size | 8192 || query_cache_limit | 16777216 || query_cache_min_res_unit | 4096 || query_cache_size | 102400 || query_cache_type | ON || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || range_alloc_block_size | 2048 || read_buffer_size | 131072 || 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 | 1 || skip_external_locking | ON || skip_networking | OFF || skip_show_database | OFF || slave_net_timeout | 3600 || slave_transaction_retries | 0 || slow_launch_time | 2 || socket | /var/run/mysqld/mysqld.sock || sort_buffer_size | 2097144 || 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 | 0 || system_time_zone | CET || table_cache | 512 || table_type | MyISAM || thread_cache_size | 8 || thread_stack | 262144 || time_format | %H:%i:%s || time_zone | SYSTEM || tmp_table_size | 33554432 || tmpdir | /tmp || transaction_alloc_block_size | 8192 || transaction_prealloc_size | 4096 || tx_isolation | REPEATABLE-READ || version | 4.1.15-Debian_0.dotdeb.4-log || version_comment | Source distribution || version_compile_machine | i386 || version_compile_os | pc-linux-gnu || wait_timeout | 28800 |±--------------------------------±-----------------------------+189 rows in set (0.01 sec)

my.cnf:

* Basic Settings#user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplanguage = /usr/share/mysql/englishskip-external-locking## localhost which is more compatible and is not less secure.#bind-address = 127.0.0.1## * Fine Tuning#key_buffer = 32Mmax_allowed_packet = 1Gthread_stack = 256Kthread_cache_size = 8max_connections = 300table_cache = 512thread_concurrency = 20## * Query Cache Configuration#query_cache_limit = 16Mquery_cache_size = 100K## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.#log = /var/log/mysql/mysql.log## Error logging goes to syslog. This is a Debian improvement :)## Here you can see queries with especially long duration#log_slow_queries = /var/log/mysql/mysql-slow.loglong_query_time = 4log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.#server-id = 1log_bin = /var/log/mysql/mysql-bin.log# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!expire_logs_days = 5max_binlog_size = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name## * BerkeleyDB## Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.skip-bdb## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.#skip-innodb## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem[mysqldump]quickquote-namesmax_allowed_packet = 612M[mysql]#no-auto-rehash # faster start of mysql but no tab completition[isamchk]key_buffer = 128M

please help…)

sorry for bad english…

[sterin]

Some immediate thoughts:
1.
How large is your DB?
Because your setting:
max_allowed_packet = 1G
seems very odd.
It is incredibly huge while the rest of your values are very small.

Post the output from SHOW STATUS so that we can see what your database is actually doing.

[mesti]

mysql> SHOW STATUS ;±---------------------------±-----------+| Variable_name | Value |±---------------------------±-----------+| Aborted_clients | 3540 || Aborted_connects | 2 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 49348886 || Bytes_sent | 2512562367 || Com_admin_commands | 5252 || Com_alter_db | 0 || Com_alter_table | 0 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 18806 || Com_change_master | 0 || Com_check | 0 || Com_checksum | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_dealloc_sql | 0 || Com_delete | 767 || Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_drop_user | 0 || Com_execute_sql | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 3926 || Com_insert_select | 0 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 4 || Com_optimize | 7 || Com_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 0 || Com_rename_table | 0 || Com_repair | 0 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_restore_table | 0 || Com_revoke | 0 || Com_revoke_all | 0 || Com_rollback | 0 || Com_savepoint | 0 || Com_select | 265725 || Com_set_option | 428 || Com_show_binlog_events | 0 || Com_show_binlogs | 1 || Com_show_charsets | 13 || Com_show_collations | 13 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 372 || Com_show_databases | 13 || Com_show_errors | 0 || Com_show_fields | 248 || Com_show_grants | 7 || Com_show_innodb_status | 0 || Com_show_keys | 0 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_ndb_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_privileges | 0 || Com_show_processlist | 0 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 2 || Com_show_storage_engines | 0 || Com_show_tables | 395 || Com_show_variables | 35 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_stmt_close | 0 || Com_stmt_execute | 0 || Com_stmt_prepare | 0 || Com_stmt_reset | 0 || Com_stmt_send_long_data | 0 || Com_truncate | 0 || Com_unlock_tables | 4 || Com_update | 34342 || Com_update_multi | 3481 || Connections | 13117 || Created_tmp_disk_tables | 13 || Created_tmp_files | 9 || Created_tmp_tables | 24347 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 2366 || Handler_discover | 0 || Handler_read_first | 9433 || Handler_read_key | 565826 || Handler_read_next | 33616984 || Handler_read_prev | 14422 || Handler_read_rnd | 1588681 || Handler_read_rnd_next | 987408255 || Handler_rollback | 0 || Handler_update | 105713 || Handler_write | 65458 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 52225 || Key_blocks_used | 5765 || Key_read_requests | 4927747 || Key_reads | 21262 || Key_write_requests | 61873 || Key_writes | 39608 || Max_used_connections | 242 || Not_flushed_delayed_rows | 0 || Open_files | 398 || Open_streams | 0 || Open_tables | 234 || Opened_tables | 712 || Qcache_free_blocks | 2 || Qcache_free_memory | 137312 || Qcache_hits | 230520 || Qcache_inserts | 224278 || Qcache_lowmem_prunes | 143039 || Qcache_not_cached | 41170 || Qcache_queries_in_cache | 54 || Qcache_total_blocks | 116 || Questions | 570348 || Rpl_status | NULL || Select_full_join | 57 || Select_full_range_join | 0 || Select_range | 5734 || Select_range_check | 0 || Select_scan | 39636 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 27 || Slow_queries | 39979 || Sort_merge_passes | 3 || Sort_range | 6516 || Sort_rows | 1761065 || Sort_scan | 25291 || Table_locks_immediate | 366575 || Table_locks_waited | 2787 || Threads_cached | 0 || Threads_connected | 107 || Threads_created | 1053 || Threads_running | 3 || Uptime | 4214 |±---------------------------±-----------+163 rows in set (0.00 sec)

my DB is 300mb

[sterin]

OK, this:
Select_full_join | 57

Tells us that you have at least one JOIN that doesn’t use an index at all.

| Select_scan | 39636 |
Tells us that about 1/10 of your queries does a table scan.

So I suggest that you turn on the slow query log and start examining what queries ends up there. Because you seem to lack some important indexes in your database.

Another thing that is good to know for you is that if you add the column that you order by as the last column in a combined index the DBMS can use that index to retrieve the rows in sorted order. And that avoids the need for sorting the rows later in the query execution. Although it only works on some types of queries it is worthwhile to check out.

[mesti]

You would write it down in detail that what make with commands because I do not understand it reallya

sorry for the bad english…( confused:

[sterin]

Add these rows to your my.cnf file:

loglog_slow_querieslong_query_time=1log_long_formatlog_output=FILE

Then read about the slow query here:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Then you are able to see in the slow query log which queries that are slow.

[mesti]

I put it in but:

mpm:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld…failed.
Please take a look at the syslog.
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysq ld.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!

:S

[sterin]

And what does your syslog file say?

And what does it say in the mysql error log file?
Which is usually placed at:
/var/lib/mysql/[hostname].err

[mesti]

I not found in /var/lib/mysql/ nothing

I run the tuning-primer.sh

mpm:~# ./tuning-primer.sh – MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery -MySQL Version 4.1.15-Debian_0.dotdeb.4-log i386Uptime = 1 days 23 hrs 7 min 5 secAvg. qps = 100Total Questions = 17099276Threads Connected = 180Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.htmlVisit http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL’s Enterprise Monitoring and Advisory ServiceSLOW QUERIESCurrent long_query_time = 4 sec.You have 590528 out of 17110206 that take longer than 4 sec. to completeThe slow query log is NOT enabled.Your long_query_time seems to be fineWORKER THREADSCurrent thread_cache_size = 8Current threads_cached = 7Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = 400Current threads_connected = 171Historic max_used_connections = 277The number of used connections is 69% of the configured maximum.Your max_connections variable seems to be fine.MEMORY USAGEMax Memory Ever Allocated : 3 GConfigured Max Per-thread Buffers : 4 GConfigured Max Global Buffers : 650 MConfigured Max Memory Limit : 5 GPhysical Memory : 1002.26 MMax memory limit exceeds 90% of physical memoryKEY BUFFERCurrent MyISAM index space = 30 MCurrent key_buffer_size = 512 MKey cache miss rate is 1 : 289Key buffer fill ratio = 2.00 %Your key_buffer_size seems to be too high.Perhaps you can use these resources elsewhereQUERY CACHEQuery cache is enabledCurrent query_cache_size = 128 MCurrent query_cache_used = 45 MCurrent query_cache_limit = 1 MCurrent Query cache Memory fill ratio = 35.22 %Current query_cache_min_res_unit = 4 KQuery Cache is 21 % fragmentedRun “FLUSH QUERY CACHE” periodically to defragment the query cache memoryIf you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.MySQL won’t cache query results that are larger than query_cache_limit in sizeSORT OPERATIONSCurrent sort_buffer_size = 2 MCurrent record/read_rnd_buffer_size = 7 MSort buffer seems to be fineJOINSCurrent join_buffer_size = 132.00 KYou have had 880 queries where a join could not use an index properlyYou should enable "log-queries-not-using-indexes"Then look for non indexed joins in the slow query log.If you are unable to optimize your queries you may want to increase yourjoin_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size whenANY joins not using indexes are found.OPEN FILES LIMITCurrent open_files_limit = 2010 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHECurrent table_cache value = 512 tablesYou have a total of 435 tablesYou have 475 open tables.The table_cache value seems to be fineTEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 760854 temp tables, 0% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Created disk tmp tables ratio seems fineTABLE SCANSCurrent read_buffer_size = 1 MCurrent table scan ratio = 263 : 1read_buffer_size seems to be fineTABLE LOCKINGCurrent Lock Wait ratio = 1 : 121You may benefit from selective use of InnoDB.If you have long running SELECT’s against MyISAM tables and performfrequent updates consider setting ‘low_priority_updates=1’

useful onto something these informations?

[yumkoolaid]

I’m having a 100% CPU load problem, here is my extended-status:

| Com_show_innodb_status | 0 |
| Com_show_keys | 47 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 20 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 20 |
| Com_show_triggers | 420 |
| Com_show_variables | 3 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 8 |
| Com_update | 688258 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1049870 |
| Created_tmp_disk_tables | 904 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 115028 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 100608 |
| Handler_delete | 100748 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 247460 |
| Handler_read_key | 7975322 |
| Handler_read_next | 598651518 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 3186674 |
| Handler_read_rnd_next | 336080470215 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1768219 |
| Handler_write | 3458345 |
| Innodb_buffer_pool_pages_data | 511 |
| Innodb_buffer_pool_pages_dirty | 8 |
| Innodb_buffer_pool_pages_flushed | 106578 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 61 |
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 364929 |
| Innodb_buffer_pool_read_ahead_seq | 9057576 |
| Innodb_buffer_pool_read_requests | 4112664707 |
| Innodb_buffer_pool_reads | 3576220 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 703111 |
| Innodb_data_fsyncs | 119576 |
| Innodb_data_pending_fsyncs | 1 |
| Innodb_data_pending_reads | 1 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1826147913728 |
| Innodb_data_reads | 11085477 |
| Innodb_data_writes | 202873 |
| Innodb_data_written | 3599413248 |
| Innodb_dblwr_pages_written | 106586 |
| Innodb_dblwr_writes | 13943 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 118546 |
| Innodb_log_writes | 85221 |
| Innodb_os_log_fsyncs | 91712 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 103616000 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 3232 |
| Innodb_pages_read | 111459049 |
| Innodb_pages_written | 106578 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 734 |
| Innodb_row_lock_time_avg | 7 |
| Innodb_row_lock_time_max | 77 |
| Innodb_row_lock_waits | 99 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 120665 |
| Innodb_rows_read | 27997512861 |
| Innodb_rows_updated | 81024 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 244225 |
| Key_blocks_used | 93697 |
| Key_read_requests | 72199803 |
| Key_reads | 532073 |
| Key_write_requests | 2016215 |
| Key_writes | 1860529 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 251 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1070 |
| Open_streams | 0 |
| Open_tables | 879 |
| Opened_tables | 1293 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 4 |
| Qcache_free_memory | 1023296 |
| Qcache_hits | 0 |
| Qcache_inserts | 10 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4850590 |
| Qcache_queries_in_cache | 10 |
| Qcache_total_blocks | 17 |
| Questions | 8123575 |
| Rpl_status | NULL |
| Select_full_join | 11226 |
| Select_full_range_join | 0 |
| Select_range | 78562 |
| Select_range_check | 0 |
| Select_scan | 2596597 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2602764 |
| Sort_merge_passes | 0 |
| Sort_range | 197715 |
| Sort_rows | 3356236 |
| Sort_scan | 210944 |
| Table_locks_immediate | 5883022 |
| Table_locks_waited | 153360 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 199 |
| Threads_connected | 52 |
| Threads_created | 251 |
| Threads_running | 23 |
| Uptime | 63080 |
| Uptime_since_flush_status | 63080 |
±----------------------------------±--------------+

I am logging slow queries, and the log file is about 600mb now, I am tailing it and it just scrolls and scrolls and scrolls with data…

Any help would be appreciated.

[sterin]

OK, you might have indexes but are the queries written so that MySQL can use them?

This figure:

[B]Quote:[/B]

| Handler_read_rnd_next | 336080470215 |

Indicates that you have seem to have a lot of table scans.

Test the queries in your slow query log with the EXPLAIN keyword in front of them and check the execution plan.
If you want help to interpret the output from EXPLAIN you can post it here.

[yumkoolaid]

Alright, here’s one reply to EXPLAIN:

ID: 1 Select Type: SIMPLE Table: gu_tracker_query Type: ALL Rows: 363028 Extra: Using where

possible_keys, keys, key_len and ref are all null.

Thanks again for your time.