Announcement

Collapse
No announcement yet.

Any speedup suggestions welcome

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mvigod
    replied
    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.

    Leave a comment:


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

    Leave a comment:


  • telescopi
    replied
    +---------------+----------------------+------+-----+---------+----------------+
    | 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 | |
    +---------------+----------------------+------+-----+---------+----------------+

    Leave a comment:


  • mvigod
    replied
    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...

    Leave a comment:


  • telescopi
    replied
    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

    Leave a comment:


  • mvigod
    replied
    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

    Leave a comment:


  • telescopi
    replied
    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' ?

    Leave a comment:


  • George L
    replied
    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

    Leave a comment:


  • telescopi
    replied
    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
    Last edited by telescopi; Sat 2 Feb '02, 7:02am.

    Leave a comment:


  • George L
    replied
    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

    Leave a comment:


  • telescopi
    replied
    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?

    Leave a comment:


  • George L
    replied
    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

    Leave a comment:


  • telescopi
    replied
    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

    Leave a comment:


  • telescopi
    replied
    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 |
    +--------------------------+------------+

    Leave a comment:


  • telescopi
    started a topic Any speedup suggestions welcome

    Any speedup suggestions welcome

    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).
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X