Help, please walkmethrough Mysql optimization

[K2K Bigbird]

I have read numerous threads about tuning your MYSQL system on dedicated servers, but I am unable to do it. I am not exactly a newbie, but I am having problems understanding it and doing it myself.

I have a large message board, 150 people at a time, and even though its an insanely fast server, it is very slow. I am using default mysql , not even using my.cnf.

What can I do to make it smooth and fast?

If someone can help me I can offer FREE advertising on my website of whatever.

Much apreciated, please email at webmaster@altimas.net if necessary.

[nuno]

first: you should upgrade to latest vB ver
second: i dont think your board is slow, in fact, its loading pretty damn fast here :rolleyes:

[eva2000]

mysql optimisation may be needed but before that you can do some simple page code/image optimisation

1. your forum logo is tooooooo large in size 21kb

http://www.altimas.net/forum/images/bbtitle5.gif

2. sponsor image 14kb

http://www.altimas.net/forum/psbstillen.jpg

3. 2 bottom banner ads and 1 header banner ad

these all add to your load time

4. do you have PHP compiled with zlib so you can enable vB's inbuilt gzip compression for vB php pages ?

[K2K Bigbird]

1+2+3

Yes, the files could be large, and maybe theres a few of them, but nobody complained about them before.

4) No, I do not know what gzip is, and can't tell you if its enabled or if it is installed. Most likely however, I don't think my provider compiled it with gzip support.

PS. As for the slightly outdated software, I have alot of mods installed on it, and see no real point in upgrading to the newest version. Well, maybe I should?

But the basic problem is hesitation, and it seems like its mysql hesitation to pull up the information. This same forum was running lightning fast on the previous server, which was also MUCH weaker and slower than the currect serv/connection.

[nuno]

is this a dedicated server?
who compiled MySQL?
RPM or tarball packages?

[K2K Bigbird]

Yes, this is a dedicated red hat server, 1Ghz 512RAM ...

The PHP/Mysql were installed by the host, but he is not really familiar with the tweaking mysql , and I do not know which way it was installed/compiled.

So I am wondering if I can do something to speed up the server if at all possible.

[eva2000]

well yes but you'd need the following info from your web host? who is it btw ?

1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

5. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is located at http://vbulletin.com/forum/showthread.php?threadid=3477

and post url to that here

6. oh and is your vB the only thing on the server? or other scripts? sites?

7. how many average and max concurrent users on your vB forum ?

[K2K Bigbird]

1) RED HAT DEDICATED SERV. 1GHZ 512RAM , mysql ver 3.23.47 I think; can't tell which PHP is installed, but I am assuming that its really fresh, because the server is 3-4 weeks old.

2) Still no answer from the host on how it was compiled, do not know unfortunately.

3) TOP shows <1 for 150 people, maxes around 1.4 during high high loads.

4)
+---------------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos|
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_long_queries | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_recover_options | 0 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /var/lib/mysql/ALTIMASNET.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| record_rnd_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.47 |
| wait_timeout | 28800 |
+---------------------------------+--------------------------------------------------------------+

[K2K Bigbird]

extended:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 6 |
| Aborted_connects | 3 |
| Bytes_received | 1818048884 |
| Bytes_sent | 2487213297 |
| Com_admin_commands | 0 |
| Com_alter_table | 1 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 463531 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 38 |
| Com_delete | 27321 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 38 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 136027 |
| Com_insert_select | 90709 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 38 |
| Com_optimize | 1 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 235736 |
| Com_replace_select | 2888 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 6783339 |
| Com_set_option | 2 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_stat | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_stat | 0 |
| Com_show_status | 1 |
| Com_show_tables | 1 |
| Com_show_variables | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 38 |
| Com_update | 817727 |
| Connections | 463553 |
| Created_tmp_disk_tables | 127539 |
| Created_tmp_tables | 396210 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 52008 |
| Handler_read_first | 211527 |
| Handler_read_key | 98504789 |
| Handler_read_next | 214403595 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 47950157 |
| Handler_read_rnd_next | 2649198250 |
| Handler_update | 1168145 |
| Handler_write | 16923516 |
| Key_blocks_used | 7793 |
| Key_read_requests | 404224027 |
| Key_reads | 820633 |
| Key_write_requests | 9403171 |
| Key_writes | 4104737 |
| Max_used_connections | 41 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 89 |
| Open_streams | 0 |
| Opened_tables | 466 |
| Questions | 9020981 |
| Select_full_join | 128398 |
| Select_full_range_join | 6 |
| Select_range | 1678824 |
| Select_range_check | 0 |
| Select_scan | 957521 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 13 |
| Sort_merge_passes | 0 |
| Sort_range | 1337199 |
| Sort_rows | 52473548 |
| Sort_scan | 787277 |
| Table_locks_immediate | 9932870 |
| Table_locks_waited | 5236 |
| Threads_cached | 0 |
| Threads_created | 463552 |
| Threads_connected | 12 |
| Threads_running | 1 |
| Uptime | 639142 |
+--------------------------+------------+

[K2K Bigbird]

Anyone? Please ?

[eva2000]

okay replace contents of your /etc/my.cnf file with below and restart mysql


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=600
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=3M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10

[safe_mysqld]
open-files-limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=128M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[K2K Bigbird]

Wow, what happened here???

I receive an error message stating that No mysqld pid file found. Its looking for altimas.net.pid but the file is called altimasnet.pid. How do I remedy that?

[eva2000]

Originally posted by K2K Bigbird
Wow, what happened here???

I receive an error message stating that No mysqld pid file found. Its looking for altimas.net.pid but the file is called altimasnet.pid. How do I remedy that? what were you doing when you got this error message ?

[nuno]

are you running mysql as root?
try this

locate safe_mysqld

this will return your bindir/safe_mysqld

bindir/safe_mysqld --user=root &

if you get an error message check your
datadir/hostname.err file

[K2K Bigbird]

I was doing the mysql stop command , trying to restart the mysql

[K2K Bigbird]

Nevermind, I fixed it, added the new MY.CNF and restarted mysql. I myself haven't really notice any difference, but hopefully the members will.

Thanks eva

[eva2000]

Originally posted by K2K Bigbird
Nevermind, I fixed it, added the new MY.CNF and restarted mysql. I myself haven't really notice any difference, but hopefully the members will.

Thanks eva no probs !