UBB.Developers Forums General Discussion Suggestions and Feedback Need happy place... help us make our site a fast Threads site

I'm not sure what forum to post this in, I'm too swamped this month to read all threads on here...

Hi, we are www.ls1tech.com We are hosted by a friend, and we are on a windows box and will not be going to Unix anytime soon.

We have a copy of mysql.

Our site is occasionally very fast, but most of the time it's slower than we had ever experienced with Classic. It's not a bandwidth issue or a space issue.

Are there reccomended configurations for mysql that we should be using?

We had 42 million hits last month, 80,000 unique IP visits... probably about 30,000 regular readers... Lot's of searches are run in pm hours.... that's why we switched from Classic.

Any suggestions are greatly appreciated. Everytime I bump into users they tend to throw stuff at me since the site is slower than a dead pigeon during pm hours.

Do you have any specs on your server? Such as how much ram? What speed CPU? And are you using IDE or SCSI hard drive(s)? That will help determine where and what tweaks need to be made to optimize performance

Your current MySQL configuration and the version, that you're running would also help.

Additionally, you might want to have a look at these threads:
one
two
three

Okay, thanks for the questions, I will have answers in the next hour.

What version of mysql is ideal for Threads and a board like ours? I will find out what version we have in the next hour.

I recommend the latest stable version, which ist 4.0.12 at the moment.
The speed improvments to prior 3.x versions is significant.

We have:
version 4.0.12-nt

More answers coming.

windows 2000 server
dual Pentium 3, 795 mhz
1.5 gb ram
megariad ld 0 raid 5 scsi drive
total of 3 drives
diverse T1 (2 T1 lines with split traffic), and not dedicated to our site (www.ls1tech.com)

Any more questions?

Also the database is on a different server, and that server has our site and mysql and nothing else on there.

Oh and I dig the quicky reply window we need that!

The specs of the database server are much more interesting.
It needs to do the work.
Posting your my.cnf (mysql configuration file) would help also..
Load of your server etc.

Does this info help?
************
John-

In mysql, there are some sample scripts. One for a huge, large, medium, and
small server. I just implemented the huge script. Attached is the file
that you can open in word in case it doesn't show up correctly in your
email.

Daren




# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
server-id = 1

# Uncomment the following if you want to log updates
#log-bin

# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/


# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=384M
#set-variable = bdb_max_lock=100000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
#innodb_data_home_dir = c:\ibdata
#innodb_log_group_home_dir = c:\iblogs
#innodb_log_arch_dir = c:\iblogs
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

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

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

Yes, the database server specs will be one of the largest factors here as well.. if your datbase server doesn't at least match the specs you provided of the main server, that will be a pretty big factor alone.

Web server and database server have the same specs...

windows 2000 server
dual Pentium 3, 795 mhz
1.5 gb ram
megariad ld 0 raid 5 scsi drive
total of 3 drives

Also, what is the configuration of the www.ubbdev.com server, it seems to be very fast?

A Linux server would work better. I think Threadsdev uses Redhat Linux.

While I do agree a linux server would run your site much better, that seems to be out of the question per your initial post. Some initial things to check, are the hard drives on the datbase server optimized? I mean with a site that large and with that much disk access, having properly optimized disks would speed up seek times tremendously.

The next big thing to check would be your mysql configuration. Which is for the most part in the my.cnf file (or equivelent for a windows machine). So if you could post the contents of that file, we could see where changes could be made to optimize that as much as possible for your hardware.

Actually it was pointed out to me that threadsdev at points today had 38 folks on... and my site was averaging over 250 most of the day... perhaps not a good comparison.

I'll try to get more info.

A totally different server is not going to happen anytime soon unless it's totally clear that it's the problem.

Are some pro's reading this thread? Show me a site of yours that is as big as ours and maybe we can work something out... But I need to see the site.

If the TrekBBS guy is on here I'd like to get advice from you your site is moving pretty good.

[]ProStockJohn said:
Also, what is the configuration of the www.ubbdev.com server, it seems to be very fast? [/]

Threadsdev is on Infopop's shared hosting. Speed is pretty good. The site dogs down at a certain time of night while it's all being backed up, but it only lasts 15-20 minutes.

TrekBBS.... isn't that Charles Capps site?

For what it's worth I'd highly recommend Astaran for server optimization and tuning. He's done several things for me, server wise, that were over my head, and I've referred some clients to him that had poorly configured MySQL setups.

The most important addition for optimal performance is the addition of three new SQL variables. I HIGHLY recommend that you add them to my.cnf if your running any type of busy server. Those SQL variables are;

query_cache_limit
query_cache_size
query_cache_type

These additions have dropped my server loads dramatically. You can look up the meaning of these variable on the MySQL site and i will not attempt to explain them here. All i know is they work wonders.

In your my.cnf file


I would STRONGLY suggest taking a look at your mysql config file, as by default, that is usually installed with NO optimizations. Meaning, the config file is literally empty

Those three variables are new to MySQL 4 and will be a great help in speeding up your queries. You may need to tweak your values slightly, but that is what I use on a single P4 with a gig of ram and it dropped my server loads by quite a bit and sped up queries also by a noticable amount.

There are many other settings you can use to help tune mysql, but without seeing what your config has already (if anything) it isn't going to help just throwing out random settings.

His server guy says they are using the 'huge' my.cnf file, as posted above.

Win2K will handle the site well, as long as the server is setup correctly, no need to move to linux As mentioned tho, the defrag utility should be run maybe weekly, it can be set to automatically.

I have a friend running a similar configuration, hardware and software-wise, I'll see what he's using for the my.cnf. Tho I doubt it's any different from the default.

stop fooling yourself Allen, we all know linux is faster

Jeremy, thank you for the ideas, we are trying them now and they seem to be helping.

I posted the cnf file, I think, earlier in the thread, did you see that? If that's not the right thing tell me and I will get the info.

Thanks for your help, keep it coming, maybe I can send you an Amazon gift certificate for taking the time to help us out.

I got my first sponsor complaint about site speed yesterday so your suggestion is quite timely...

Indeed the tuning up of mysql is essential. Although my site is really small in terms of traffic and users (80 members averaging 77 posts a day), the upgrade from 3.x to 4.x on mysql sliced the query times to a third.

Windows 2K will definitely cope with your site, especially with the configuration of your boxes. However I have to agree with Jeremy that a similar box running linux would cope easily with double the load.

Having said that, you need also to see your task manager - what is running constantly on the site. Even by installing Winzip, that damned Quick WinZip Center that sits on your system tray will occupy some mb of your memory.

Lastly have a look at your antivirus solution (if any). Ensure that the *.frm, *.myd, *.myi files are excluded from the real time scanning.

John, it's very difficult to give some hints without monitoring your server load and mysql status. Additionally, you might want to log all slow queries to determine which causes the db to slow down.

Is the threads db the only database on that server or do you use it for other databases also?

How many users are online at peak times? Have you turned on the debug information to see the time that is spend for generating the site and the database queries?

As Jeremy mentioned, query caching can help a bit, but i won't set the cache size so high. I use "set-variable = query_cache_size=4M" on a large database with over 900.000 postings.

As Astaran said, you may want to adjust the query cache size down a bit.. I just provided what I use on my server. I assumed 32M would be about right considering you are running 1.5GB of RAM, and I use 1 GB. Also, I have about 1200 posts per day on a database with about 250,000 posts and that copes quite well.

But as always, servers are very different so playing with the cache size may yield better results

Thanks for the additional comments, we are looking into all of those things.

We I think will have 300 users on at peak, that's what I have seen so far but I'm sure that if the board was faster that number could increase to 500 at nighttime.

My partner just added "quick reply" and that was a big hit.

Our cpu is showing use of about 200mbs right now sound right?

Jeremy, your how-to/help section rocks.

If you use IM 'ware, could you IM me at:

msn: [][email protected][/]
yahoo: rsmrjohn

Or i can log on AOL if you use that.

I think we have similar boards.

Some recent message stats pulled off admin....

Posted in the last 24 hours 1403
Posted in the last week 7839
Posted in the last month 42116
Total # of topics 74160
Total # of replies 444844

[]Astaran said:
As Jeremy mentioned, query caching can help a bit, but i won't set the cache size so high. I use "set-variable = query_cache_size=4M" on a large database with over 900.000 postings.
[/]

We've had that at 32, now trying 4.

Sorry John, I have been away most of the week (I got engaged), but hopefully you are seeing some improvements. If you care to chat, you can find me on AIM as "MSULA2003". If I'm around, I'd be happy to see if there is anything else that could help your site out

Thank you Astaran for your help with our site!