Any speedup suggestions welcome

[telescopi]

Our forums at http://forums.overclockers.co.uk are in need of some help I think.

They're down today for some harsh pruning (30 days), but I'm more interested in mySQL configration - there's so much in it that I don't even begin to understand (and my host is useless) but I'm sure we could reap great benefits from optimising it a little.

We have about 800,000 posts (atm), 1GB ram, Dual P3-600, SCSI 10k - dedicted server (freeBSD) which runs both apache/php and mysql. I loaded the medium cnf to get going and it seems okay but I awnt to make sure we're getting everything we can (every time I say anything to hour host I get told the prices for RAM and CPU upgrades - very helpful).

I'm also wondering how to turn off mySQL's logging - we just don't need a log, I wouldn't know how to rollback even if we did have a problem and we have a daily backup of the DB anyway - I'm thinking saving on that disc access would be a bit of a boost?

Also - with all the talk of slow deleting threads I've noted a lot of stuff about attachments - they're disabled on our forums, totally - I'm assuming they're still being checked for even though we don't use them? Any suggestions where I could 'tweak' to save on that wasted process?

At busy periods TOP shows mysqld using about 70% of the CPU, with apache eating the rest - aout 100-200MB free (varies).

[telescopi]

Extended-Status

+--------------------------+------------+
| Aborted_clients | 98 |
| Aborted_connects | 0 |
| Bytes_received | 2780260373 |
| Bytes_sent | 3783267873 |
| Connections | 677323 |
| Created_tmp_disk_tables | 14 |
| Created_tmp_tables | 534766 |
| Created_tmp_files | 28 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 259413 |
| Handler_read_first | 1094580 |
| Handler_read_key | 145418839 |
| Handler_read_next | 814965143 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 142534009 |
| Handler_read_rnd_next | 1207559483 |
| Handler_update | 1533323 |
| Handler_write | 377019948 |
| Key_blocks_used | 249376 |
| Key_read_requests | 648379793 |
| Key_reads | 515370 |
| Key_write_requests | 1387481 |
| Key_writes | 1222706 |
| Max_used_connections | 100 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 293 |
| Open_streams | 0 |
| Opened_tables | 43659 |
| Questions | 16116102 |
| Select_full_join | 14 |
| Select_full_range_join | 0 |
| Select_range | 2377051 |
| Select_range_check | 0 |
| Select_scan | 2625627 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 3975 |
| Sort_merge_passes | 14 |
| Sort_range | 2086004 |
| Sort_rows | 145106114 |
| Sort_scan | 1856675 |
| Table_locks_immediate | 17496050 |
| Table_locks_waited | 942306 |
| Threads_cached | 4 |
| Threads_created | 52704 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 470864 |
+--------------------------+------------+

[telescopi]

Some useful variables

| version 3.23.37log |
| wait_timeout | 28800
tmp_table_size | 1048576
transaction_isolation | READ-COMMITTED
thread_stack | 65536
thread_cache_size | 8
table_type | MYISAM
table_cache | 256
sort_buffer | 1048568
query_buffer_size | 0
record_buffer | 1044480
open_files_limit | 0
myisam_sort_buffer_size | 67108864
myisam_max_sort_file_size | 2047
myisam_max_extra_sort_file_size | 256
max_write_lock_count | 4294967295
max_tmp_tables | 32
max_user_connections | 0
max_sort_length | 1024
max_join_size | 4294967295
max_heap_table_size | 16777216
max_delayed_threads | 20
max_connect_errors | 10
max_connections | 100
max_binlog_size | 1073741824
max_binlog_cache_size | 4294967295
max_allowed_packet | 1047552
log_bin | ON
log_update | OFF
log | OFF
large_files_support | ON
key_buffer_size | 268431360
join_buffer_size | 131072
interactive_timeout | 28800
have_isam | YES
have_innodb | NO
have_gemini | NO
have_bdb | NO
flush | OFF
delayed_queue_size | 1000
delayed_insert_timeout | 300
delayed_insert_limit | 100
delay_key_write | ON
connect_timeout | 5
binlog_cache_size | 32768
back_log | 50
ansi_mode | OFF

I hope someone can make sense of that lot - cause I'm way out of my depth here

[George L]

you using mysql 3.23.47 ???

replace your my.cnf file with this one 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=500
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

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

other additional benefits would be

1. upgrade to vB 2.2.2 - should fix mass prune problem
2. latest PHP and MySQL versions
3. add a 2nd scsi 10k hdd and move mysql data to that disk

[telescopi]

Thanks for the cnf info eva - just what I needed

We put vb2.2.2 on the other day, we pruning now to get rid of the slack that built up in 2.2.1. Although I did notice a funny thing immediatley after installing 2.2.2, it would take 15 seconds to open a thread and TOP would show we had 20% cpu idle and 200MB free - ftp+shell was fast and responsive also, funny that.

Umm, we got a second 10k scsi, I'm not entirely sure what is on it though - backups I know are on it. How would I go about telling mySQL to look there for it's data ?

I think I shall pester my host about versions - they don't like changing anything but I reckon an upgrade to latest freebsd may be in order too (on 4.0 atm, 4.4 is latest and is much faster on PIII apparently). They gonna hate me

Checking mysql -version I got :
mysql Ver 11.15 Distrib 3.23.37, for unknown-freebsdelf4.0 (i386)

I presume mysql has better optimisation in later versions?

[George L]

yes mysql 3.23.47 or later would have more bug fixes etc

also 2.2.2 thread 2nd post here http://www.vbulletin.com/forum/showt...?postid=237893 be sure to replace the forumdisplay.php with the new one

if you're upgrading mysql you could compile it to place mysql data files in the 2nd disk partition with --localstatedir=/home2 option

i.e. if 2nd disk is /home2

compile with configure options

./configure --prefix=/usr/local/mysql --localstatedir=/home2 --with-unix-socket-path=/home2/mysql.sock --with-innodb --with-berkeley-db --without-debug --with-extra-charsets=none --enable-assembler

assuming --prefix=/usr/local/mysql is where mysql client apps are you can find that out by locating a mysql client app like mysqladmin

locate mysqladmin

if it returns

/usr/local/mysql/bin/mysqladmin

then the path --prefix=/usr/local/mysql

of course ask your host about this as well

[telescopi]

Thanks, I just uplaoded all the files in the uplaod dir, as I allways do - so I presume everything was replaced.

I'll bundle that info up and send it off to my host - I'm not even confident about restarting mysql never mind the rest of it

Thanks for all your help.

[update] Ah gotcha - found the updated forumdisplay now

[George L]

forgot to mention if you compile new mysql to place data in new disk partitioned as /home2

you'd need to while mysql was down copy the data files from where they were into the new location /home2

but if your /home2 has backup files

then you might want to specify mysql to placed in

/home2/mysql instead so in that case you need to copy data files from current location to /home2/mysql and use this configure line to compile mysql new/latest version

./configure --prefix=/usr/local/mysql --localstatedir=/home2/mysql --with-unix-socket-path=/home2/mysql.sock --with-innodb --with-berkeley-db --without-debug --with-extra-charsets=none --enable-assembler

[telescopi]

Thanks for the tips eva - I've implemented the cnf change and host has scheduled an upgrade (at same time as upgrading to freebsd 4.4). Server seems to be responding faster than it was when under load

My boss has been asking about server upgrades, currently running dual P3 600's with 1GB RAM and 10k SCSI.

Our host is suggesting (heavily) RAM or CPU upgrades, what do you think? I'm sure we could use more RAM, and CPU is allways helpful - but is there a better way? Is it much better if running two servers (one powerful one for mysql and another, maybe RAQ4 for the apache/php) ? If we stick with one box how much RAM would you suggest would be 'enough' ?

[mvigod]

How many users online at the same time do you ever have at peak?

Do you have a slow-queries log to see what those slow queries are coming from?

Also, try setting up another apache server stripped down without all the unnecessary modules to run all your images from on the server. Make a domain like images.mydomain.com and set vbulletin to pull the images from there instead of wasting all the resources from a full apache compile.

Marc

[telescopi]

Pretty steady 300 users mid-evening, once or twice a week we can get 350 on - but 300 is normal.

I'll pass that idea to server admins, I allways wondered why some people had 'image servers' - guess I know now

[mvigod]

We also get 300-350 peak so we are in the same boat...I just moved to a dual 1Ghz with 2GB RAM.

Prior to this I was on 800Mhz w/ 1GB RAM.

Check to see what your post table looks like. Post it here:

Issue this at the mySQL command line or phpmyadmin:

DESCRIBE post;


Post what you have here...

[telescopi]

+---------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+----------------+
| postid | int(10) unsigned | | PRI | NULL | auto_increment |
| threadid | int(10) unsigned | | MUL | 0 | |
| username | varchar(50) | | | | |
| userid | int(10) unsigned | | MUL | 0 | |
| title | varchar(100) | | | | |
| dateline | int(10) unsigned | | | 0 | |
| attachmentid | smallint(5) unsigned | | | 0 | |
| pagetext | mediumtext | YES | | NULL | |
| allowsmilie | smallint(6) | | | 0 | |
| showsignature | smallint(6) | | | 0 | |
| ipaddress | varchar(16) | | | | |
| iconid | smallint(5) unsigned | | MUL | 0 | |
| visible | smallint(6) | | | 0 | |
| edituserid | int(10) unsigned | | | 0 | |
| editdate | int(10) unsigned | | | 0 | |
+---------------+----------------------+------+-----+---------+----------------+

[telescopi]

Do you edit your database? Allways thought vbulletin would throw a wobbly if I played

[mvigod]

Not so much editing but tweaking little things...

Try adding an index to your post table on the dateline column. This may speed things up a bit...I tried it right before my hardware upgrade and it wiped out my slow queries coming from users checking for new posts.

Here is the command.

ALTER TABLE post ADD INDEX dateline;

after that when you rerun the "DESCRIBE post" query "MUL" should appear under the key column for dateline.

This should speed things up and eliminate what I'm guessing is the cause for many of your slow-queries. Howerver enabling the slow-query log will at least tell you what other queries may be causing a slowdown.

Add this to my.cnf for that log:

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


make sure whatever path to the log directory is writable by mysql (in the above example /var/log/mysql is writable for mysql daemon.