mysql> SHOW STATUS;±---------------------------±-----------+| Variable_name | Value |±---------------------------±-----------+| Aborted_clients | 8010 || Aborted_connects | 2 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 3657146200 || Bytes_sent | 224393733 || Com_admin_commands | 383491 || Com_alter_db | 0 || Com_alter_table | 0 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 383522 || 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 | 261857 || 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 | 78712 || Com_insert_select | 3713 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 0 || 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 | 2134999 || Com_set_option | 0 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_charsets | 0 || Com_show_collations | 0 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 0 || Com_show_databases | 4 || Com_show_errors | 0 || Com_show_fields | 0 || Com_show_grants | 0 || 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 | 32 || Com_show_storage_engines | 0 || Com_show_tables | 13 || Com_show_variables | 30 || 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 | 0 || Com_update | 555381 || Com_update_multi | 0 || Connections | 5208 || Created_tmp_disk_tables | 93842 || Created_tmp_files | 0 || Created_tmp_tables | 373179 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 2 || Handler_commit | 0 || Handler_delete | 87953 || Handler_discover | 0 || Handler_read_first | 39857 || Handler_read_key | 927004079 || Handler_read_next | 701287506 || Handler_read_prev | 0 || Handler_read_rnd | 54818936 || Handler_read_rnd_next | 3501770244 || Handler_rollback | 0 || Handler_update | 270485714 || Handler_write | 59013466 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 377684 || Key_blocks_used | 144707 || Key_read_requests | 2502344390 || Key_reads | 238504 || Key_write_requests | 2206636 || Key_writes | 524553 || Max_used_connections | 17 || Not_flushed_delayed_rows | 0 || Open_files | 170 || Open_streams | 0 || Open_tables | 146 || Opened_tables | 146 || Qcache_free_blocks | 4496 || Qcache_free_memory | 93720960 || Qcache_hits | 3359978 || Qcache_inserts | 2132197 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 2802 || Qcache_queries_in_cache | 10143 || Qcache_total_blocks | 24808 || Questions | 9585208 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 224361 || Select_range_check | 0 || Select_scan | 591332 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 0 || Slow_queries | 653 || Sort_merge_passes | 0 || Sort_range | 342065 || Sort_rows | 1262701352 || Sort_scan | 395665 || Table_locks_immediate | 5921110 || Table_locks_waited | 7383 || Threads_cached | 2 || Threads_connected | 15 || Threads_created | 21 || Threads_running | 1 || Uptime | 145450 |±---------------------------±-----------+163 rows in set (0.44 sec)

this is what SHOW STATUS; commands give me can anyone provide me any helpful tips on what I can adjust in my.cnf to lower my server load which is above 10 during peak hours and mysql is the only thing eating this serve up I have lots og 3gb ram and a 64bit AMD 2800 server

Heres what my.cnf looks like

[mysqld]safe-show-database#old_passwordsback_log = 100skip-innodbmax_connections = 600key_buffer = 768Mmyisam_sort_buffer_size = 64Mjoin_buffer_size = 1Mread_buffer_size = 1Msort_buffer_size = 3Mtable_cache = 93072thread_cache_size = 320wait_timeout = 30connect_timeout = 10tmp_table_size = 128Mmax_heap_table_size = 64Mmax_allowed_packet = 64Mmax_connect_errors = 10read_rnd_buffer_size = 5Mbulk_insert_buffer_size = 16Mquery_cache_limit = 5Mquery_cache_size = 100Mquery_cache_type = 1query_prealloc_size = 163840query_alloc_block_size = 32768default-storage-engine = MyISAMlow_priority_updates=1

Heres a screenshot of TOP COmmand
[URL=“http://img300.imageshack.us/img300/3771/untitledaf0.gif”]http://img300.imageshack.us/img300/3771/untitledaf0.gif[/URL]

If you need anything else tell me the command and I will run it and give screenshot please help me

Adjusting my.cnf won’t help much. There are however some things that can be changed:

1. decrease size of key buffer since you’re not using most of it
2. unset low_priority_updates unless you really did it knowingly and it helped
3. unset query_prealloc_size and query_alloc_block_size unless your values really work better than default ones
4. decrease table_cache - you’re using 146, there’s no need to set it at 93k (although, like with key_buffer, having larger value doesn’t hurt)
5. set both tmp_table_size and max_heap_table_size to the same value (having them different is weird).
6. decrease sort_buffer_size as MySQL has a bug with it

Remember, all this stuff won’t help much with performance. You’ll have to analyze slow queries and optimize indexes and/or queries.

well do but can you tell me the exact size to lower down to?

what else can I do to make Mysql use more ram and increase performances

a good place to start is to turn on the slow queries log

log slow queries

log-slow-queries=/var/log/mysql/slow-queries.log

defines a slow query as any query >= 1 second

long_query_time=1

then run those queries with EXPLAIN to find out if they are using indexes etc and how you might improve them.

if you are using 5.0 you can also log-queries-not-using-indexes

after you do that, then start tweaking your other settings for memory etc.

I added that in my.cnf and restarted mysql but nothing was logged its a empty blank file??

log slow queries

log-slow-queries=/www/mysql-slow.log

defines a slow query as any query >= 1 second

long_query_time=1

thats what I placed in my.cnf

does the mysql user have permissions to write to /www/mysql-slow.sql ?

probably not, and you probably do not want mysql to have write permission to /www

make a log directory for mysql under /var/log

mkdir -p /var/log/mysql

chown it to the mysql user

chown mysql.mysql /var/log/mysql

then change your log-slow-queries line to this…

log-slow-queries=/var/log/mysql/mysql-slow.log

I followed your instruction I will check back in 3 hours for any logs and will post them

if you have the proper permissions you should get a little message at the beginning of your slow log like this…

/usr/sbin/mysqld, Version: 4.1.11-standard-log. started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock

Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin_db[admin_db] @ localhost # Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 13273805use admin_db17;SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 259734;

This is one of the logged queries, what can I do to optimze this query?

There’s nothing you can do about it. It’s a very bad application design.
Consider disabling phpBB search, or finding some advanced phpBB fulltext search extension.

For a big forum I’d switch to one of the proprietary scripts like VB or IPB.