optimized MYSQL config?? (db running slow)

[Triple 6]

Awhile back i posted that my MYSQL was running super slow. Someone posted a MYSQL config file that really tweaked my MYSQL nice, allowing more connections, etc.... but i recently updated my server and I think because of that I lost my tweaked MYSQL config file... ... and I cannot find that post I made here from back in the day..

Does anyone have one they can post?

[xanx]

if you can't find it here or no one knows, take a peek at the vbulletin forums, there is a guy there that can really tweak the shit out of the my.cnf and httpd etc..

[Triple 6]

having trouble locating a good one...

my serv is a p4 2.2ghz with 1gig ram.

I ran an extended status mysql command and it fed me this:

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 5913 |
| Aborted_connects | 12 |
| Bytes_received | 936712449 |
| Bytes_sent | 773136170 |
| Com_admin_commands | 0 |
| Com_alter_table | 13 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 325680 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 6 |
| Com_delete | 56030 |
| Com_delete_multi | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 10 |
| Com_flush | 7 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 169335 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 3114 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1154 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1321982 |
| Com_set_option | 5724 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 77 |
| Com_show_databases | 4 |
| Com_show_fields | 119 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 28 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 1168 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_tables | 637 |
| Com_show_variables | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 3114 |
| Com_update | 547727 |
| Com_update_multi | 272 |
| Connections | 334037 |
| Created_tmp_disk_tables | 527 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 5407 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 48824 |
| Handler_read_first | 38165 |
| Handler_read_key | 11594620 |
| Handler_read_next | 168174566 |
| Handler_read_prev | 1098976 |
| Handler_read_rnd | 7758930 |
| Handler_read_rnd_next | 187884189 |
| Handler_rollback | 0 |
| Handler_update | 796837 |
| Handler_write | 283430 |
| Key_blocks_used | 14692 |
| Key_read_requests | 68774927 |
| Key_reads | 13869 |
| Key_write_requests | 870479 |
| Key_writes | 755278 |
| Max_used_connections | 134 |
| Not_flushed_delayed_rows | 0 |
| Not_flushed_key_blocks | 0 |
| Open_files | 944 |
| Open_streams | 0 |
| Open_tables | 699 |
| Opened_tables | 784 |
| Qcache_free_blocks | 4362 |
| Qcache_free_memory | 15276416 |
| Qcache_hits | 1243992 |
| Qcache_inserts | 1306711 |
| Qcache_lowmem_prunes | 23386 |
| Qcache_not_cached | 15505 |
| Qcache_queries_in_cache | 7060 |
| Qcache_total_blocks | 18808 |
| Questions | 4012112 |
| Rpl_status | NULL |
| Select_full_join | 6 |
| Select_full_range_join | 0 |
| Select_range | 203248 |
| Select_range_check | 0 |
| Select_scan | 72718 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 11 |
| Slow_queries | 4346 |
| Sort_merge_passes | 0 |
| Sort_range | 3622 |
| Sort_rows | 7766495 |
| Sort_scan | 10823 |
| Table_locks_immediate | 2415392 |
| Table_locks_waited | 41182 |
| Threads_cached | 61 |
| Threads_connected | 67 |
| Threads_created | 138 |
| Threads_running | 35 |
| Uptime | 350378 |
+--------------------------+-----------+

[Triple 6]

nevermind. I increased the max connects by 1k and it seems to be running a lot better

[Calvinguy]

Do you have very long connections coursed by large file downloads?
Otherwise 1000 connections seems a lot as you don't have that many queries

[borked]

Absolutely turn on the query cache

query_cache_type=1
query_cache_limit=2M
query_cache_size=64M

and log long/slow queries

log_slow_queries=/var/log/mysql/slow-queries.log
long_query_time=3

The cache can speed up complex queries requiring multiple connections amazingly...I've one query that w/o the cache takes 35 secs (2.5million entries), w/ the cache is immediate.