Jump to content

Extremely Slow on IIS6 & IIS7


Recommended Posts

Hi IP

Hope someone can help me out.

Im currently running IP.Board 2.3.6 on my homeserver windows 2003 32bit 4 gb ram, php5, MySQL5 & test on 2008 64bit 8gb ram, php5, mysql5, and its a heavy visited site with many users and 11000+ games and its running very fast on both servers no delay, loadtime is 0.232

When I install 3.0.5 for test with same versions as above without any mods installed, its running extremely slow on both 2003 & 2008 and load time is 8.253 sec. :(

What settings do I need to change?

I would be very sad if I had to skip IPB and change to Bullentin, as it runs very fast to.

Please help.

Link to comment
Share on other sites

I'll move your topic to Server Management (as its more likely those who will be able to help will see it here) :)

I can't myself help you directly as its not my area, however what version of php are you using ? 5.2.x seems to work better than 5.3.x in my limited testing. I'm still using 5.2.9 for now.

Link to comment
Share on other sites

Maybe someone could comfirm my MySQL setup is ok?

Setup:
Windows 2003 Standard Edition 32bit
Intel E2160 @ 1.8 GHz Dual Core Intel
4 GB RAM

PHP 5.2
Mysql 5


I have set MyISAM to default

# MySQL Server Instance Configuration File

# ----------------------------------------------------------------------

# Generated by the MySQL Server Instance Configuration Wizard

#

#

# Installation Instructions

# ----------------------------------------------------------------------

#

# On Linux you can copy this file to /etc/my.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options

# (@localstatedir@ for this installation) or to

# ~/.my.cnf to set user-specific options.

#

# On Windows you should keep this file in the installation directory 

# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To

# make sure the server reads the config file use the startup option 

# "--defaults-file". 

#

# To run run the server from the command line, execute this in a 

# command line shell, e.g.

# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# To install the server as a Windows service manually, execute this in a 

# command line shell, e.g.

# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# And then execute this in a command line shell to start the server, e.g.

# net start MySQLXY

#

#

# Guildlines for editing this file

# ----------------------------------------------------------------------

#

# In this file, you can use all long options that the program supports.

# If you want to know the options a program supports, start the program

# with the "--help" option.

#

# More detailed information about the individual options can also be

# found in the manual.

#

#

# CLIENT SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by MySQL client applications.

# Note that only client applications shipped by MySQL are guaranteed

# to read this section. If you want your own MySQL client program to

# honor these values, you need to specify it as an option during the

# MySQL client library initialization.

#

[client]


port=3306


[mysql]


default-character-set=utf8



# SERVER SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by the MySQL Server. Make sure that

# you have installed the server correctly (see above) so it reads this 

# file.

#

[mysqld]


# The TCP/IP Port the MySQL Server will listen on

port=3306



#Path to installation directory. All paths are usually resolved relative to this.

basedir="D:/Programmer/MySQL/"


#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"


# The default character set that will be used when a new schema or table is

# created and no character set is defined

default-character-set=utf8


# The default storage engine that will be used when create new tables when

default-storage-engine=MyISAM



# The maximum amount of concurrent sessions the MySQL server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

max_connections=800


# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

query_cache_size=256M


# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

table_cache=1520


# Maximum size for internal (in-memory) temporary tables. If a table

# grows larger than this value, it is automatically converted to disk

# based table This limitation is for a single table. There can be many

# of them.

tmp_table_size=30M



# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before.  This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

thread_cache_size=38


#*** MyISAM Specific options


# The maximum size of the temporary file MySQL is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

myisam_max_sort_file_size=100G


# If the temporary file used for fast index creation would be bigger

# than using the key cache by the amount specified here, then prefer the

# key cache method.  This is mainly used to force long character keys in

# large tables to use the slower key cache method to create the index.

myisam_sort_buffer_size=30M


# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

key_buffer_size=256M


# Size of the buffer used for doing full table scans of MyISAM tables.

# Allocated per thread, if a full scan is needed.

read_buffer_size=64K

read_rnd_buffer_size=256K


# This buffer is allocated when MySQL needs to rebuild the index in

# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

sort_buffer_size=256K



#*** INNODB Specific options ***



# Use this option if you have a MySQL server with InnoDB support enabled

# but you do not plan to use it. This will save memory and disk space

# and speed up some things.

#skip-innodb


# Additional memory pool that is used by InnoDB to store metadata

# information.  If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS.  As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

innodb_additional_mem_pool_size=6M


# If set to 1, InnoDB will flush (fsync) the transaction logs to the

# disk at each commit, which offers full ACID behavior. If you are

# willing to compromise this safety, and you are running small

# transactions, you may set this to 0 or 2 to reduce disk I/O to the

# logs. Value 0 means that the log is only written to the log file and

# the log file flushed to disk approximately once per second. Value 2

# means the log is written to the log file at each commit, but the log

# file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=1


# The size of the buffer InnoDB uses for buffering log data. As soon as

# it is full, InnoDB will have to flush it to disk. As it is flushed

# once per second anyway, it does not make sense to have it very large

# (even with long transactions).

innodb_log_buffer_size=3M


# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system.  Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

innodb_buffer_pool_size=250M


# Size of each log file in a log group. You should set the combined size

# of log files to about 25%-100% of your buffer pool size to avoid

# unneeded buffer pool flush activity on log file overwrite. However,

# note that a larger logfile size will increase the time needed for the

# recovery process.

innodb_log_file_size=50M


# Number of threads allowed inside the InnoDB kernel. The optimal value

# depends highly on the application, hardware as well as the OS

# scheduler properties. A too high value may lead to thread thrashing.

innodb_thread_concurrency=8

Link to comment
Share on other sites

Currently use the settings below, try adjusting them to your server ...



[client]


port=3306



[mysql]


default-character-set=latin1



[mysqld]


# The TCP/IP Port the MySQL Server will listen on


#port=3306




#Path to installation directory. All paths are usually resolved relative to this.


basedir="C:/Program Files (x86)/Parallels/Plesk/Databases/MySQL/"



#Path to the database root


datadir="C:/Program Files (x86)/Parallels/Plesk/Databases/MySQL/Data/"




safe-show-database


back_log = 50


skip-innodb


max_connections = 500


key_buffer_size = 640M


myisam_sort_buffer_size = 128M


join_buffer_size = 1M


read_buffer_size = 1M


sort_buffer_size = 2M


table_cache = 8000


thread_cache_size = 512


wait_timeout = 360


connect_timeout=1600


tmp_table_size = 128M


max_heap_table_size = 128M


max_allowed_packet = 128M


net_buffer_length = 16384


max_connect_errors = 100


thread_concurrency = 16


table_lock_wait_timeout = 30


read_rnd_buffer_size = 1M


bulk_insert_buffer_size = 8M


query_cache_limit = 7M


query_cache_size=256M


query_cache_type = 1


query_prealloc_size=262144k


query_alloc_block_size = 65536


transaction_alloc_block_size = 8192


transaction_prealloc_size = 4096


default-storage-engine = MyISAM




#Don't allow connections via TCP/IP.


#skip-networking


#Allow connections via named pipes (Windows NT+ only). Note: you can specify a pipe name on the advanced network page, if required.


#enable-named-pipe




#Don't resolve hostnames. All hostnames are IP's or 'localhost'.


skip-name-resolve




#Port number to use for connections.


port=3306




#Allow connections via named pipes (Windows NT+ only). Note: you can specify a pipe name on the advanced network page, if required.


enable-named-pipe




[mysqld_safe]


nice = -10


open_files_limit = 8192



[mysqldump]


quick


max_allowed_packet = 128M



[myisamchk]


key_buffer = 256M


sort_buffer = 16M


read_buffer = 16M


write_buffer = 16M



Link to comment
Share on other sites

In php.ini. Should be near the bottom of the loadable modules section.

Look in C:\WINDOWS\system32\inetsrv and open fcgi.ini and post you configuration. In all honesty other than max_connection in your mysql ini file being a little high it looks good. I would change max connections to 500.

Just to be sure open a command prompt and and give mysql the show status command. Post the results here. Maybe your key_buffer_size could be adjusted a bit.

I'm leaning more towards fastcgi not being tuned correctly.

I also wouldn't turn off wincache. It could use some tuning also. If your running it at default values a small adjustment will make a huge difference.

Upload the wincache admin file to your server and open it in your browser or look at the wincache section of php.ini and post the values.

Link to comment
Share on other sites

Actually, I'd argue that you do want to tweak my.cnf. However, first you need to determine where the problem is. If IIS is what's using up the cpu for instance, you can tweak mysql till the cows come home and you won't "solve" the problem.

join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M

key_buffer_size = 640M




These are the most important things I'd suggest to check (copied from Fmoraes posted config). key_buffer_size being the most important (recommendations I've seen usually suggest setting this to about 25% of the available system memory). The other settings there can have a tremendous impact with IPB, due to the complex joins and sorting it does. I would recommend trying key_buffer_size of 512M (since you have 4GB memory), and use the above values for the other 3 settings. See if that helps.

But again - confirm if mysql is the application using the resources on the server, or if it's the webserver (or something else?).

Link to comment
Share on other sites

isapi on my currently public server on 2003



There is your problem.

You can't make a call on whether mysql needs to be tuned until you see his stats. Adjusting them without seeing stats could and most times will make things worse.

Do show stats from mysql prompt and post the data. Then it's simple to calculate the values needed.
Link to comment
Share on other sites

+--------------------------------+-----------+

| Variable_name                  | Value     |

+--------------------------------+-----------+

| Aborted_clients                | 2         |

| Aborted_connects               | 1         |

| Binlog_cache_disk_use          | 0         |

| Binlog_cache_use               | 0         |

| Bytes_received                 | 11494245  |

| Bytes_sent                     | 559815708 |

| Com_admin_commands             | 0         |

| Com_assign_to_keycache         | 0         |

| Com_alter_db                   | 0         |

| Com_alter_db_upgrade           | 0         |

| Com_alter_event                | 0         |

| Com_alter_function             | 0         |

| Com_alter_procedure            | 0         |

| Com_alter_server               | 0         |

| Com_alter_table                | 0         |

| Com_alter_tablespace           | 0         |

| Com_analyze                    | 0         |

| Com_backup_table               | 0         |

| Com_begin                      | 0         |

| Com_binlog                     | 0         |

| Com_call_procedure             | 0         |

| Com_change_db                  | 2         |

| Com_change_master              | 0         |

| Com_check                      | 0         |

| Com_checksum                   | 0         |

| Com_commit                     | 0         |

| Com_create_db                  | 0         |

| Com_create_event               | 0         |

| Com_create_function            | 0         |

| Com_create_index               | 0         |

| Com_create_procedure           | 0         |

| Com_create_server              | 0         |

| Com_create_table               | 0         |

| Com_create_trigger             | 0         |

| Com_create_udf                 | 0         |

| Com_create_user                | 0         |

| Com_create_view                | 0         |

| Com_dealloc_sql                | 0         |

| Com_delete                     | 116       |

| Com_delete_multi               | 0         |

| Com_do                         | 0         |

| Com_drop_db                    | 0         |

| Com_drop_event                 | 0         |

| Com_drop_function              | 0         |

| Com_drop_index                 | 0         |

| Com_drop_procedure             | 0         |

| Com_drop_server                | 0         |

| Com_drop_table                 | 0         |

| Com_drop_trigger               | 0         |

| Com_drop_user                  | 0         |

| Com_drop_view                  | 0         |

| Com_empty_query                | 0         |

| Com_execute_sql                | 0         |

| Com_flush                      | 0         |

| Com_grant                      | 0         |

| Com_ha_close                   | 0         |

| Com_ha_open                    | 0         |

| Com_ha_read                    | 0         |

| Com_help                       | 0         |

| Com_insert                     | 154       |

| Com_insert_select              | 0         |

| Com_install_plugin             | 0         |

| Com_kill                       | 0         |

| Com_load                       | 0         |

| Com_load_master_data           | 0         |

| Com_load_master_table          | 0         |

| Com_lock_tables                | 0         |

| Com_optimize                   | 228       |

| Com_preload_keys               | 0         |

| Com_prepare_sql                | 0         |

| Com_purge                      | 0         |

| Com_purge_before_date          | 0         |

| Com_release_savepoint          | 0         |

| Com_rename_table               | 0         |

| Com_rename_user                | 0         |

| Com_repair                     | 0         |

| Com_replace                    | 811       |

| Com_replace_select             | 0         |

| Com_reset                      | 0         |

| Com_restore_table              | 0         |

| Com_revoke                     | 0         |

| Com_revoke_all                 | 0         |

| Com_rollback                   | 0         |

| Com_rollback_to_savepoint      | 0         |

| Com_savepoint                  | 0         |

| Com_select                     | 5307      |

| Com_set_option                 | 1039      |

| Com_show_authors               | 0         |

| Com_show_binlog_events         | 0         |

| Com_show_binlogs               | 2         |

| Com_show_charsets              | 1         |

| Com_show_collations            | 1         |

| Com_show_column_types          | 0         |

| Com_show_contributors          | 0         |

| Com_show_create_db             | 0         |

| Com_show_create_event          | 0         |

| Com_show_create_func           | 0         |

| Com_show_create_proc           | 0         |

| Com_show_create_table          | 232       |

| Com_show_create_trigger        | 0         |

| Com_show_databases             | 1         |

| Com_show_engine_logs           | 0         |

| Com_show_engine_mutex          | 0         |

| Com_show_engine_status         | 0         |

| Com_show_events                | 0         |

| Com_show_errors                | 0         |

| Com_show_fields                | 11        |

| Com_show_function_status       | 0         |

| Com_show_grants                | 1         |

| Com_show_keys                  | 0         |

| Com_show_master_status         | 0         |

| Com_show_new_master            | 0         |

| Com_show_open_tables           | 0         |

| Com_show_plugins               | 4         |

| Com_show_privileges            | 0         |

| Com_show_procedure_status      | 0         |

| Com_show_processlist           | 0         |

| Com_show_profile               | 0         |

| Com_show_profiles              | 0         |

| Com_show_slave_hosts           | 0         |

| Com_show_slave_status          | 0         |

| Com_show_status                | 2         |

| Com_show_storage_engines       | 0         |

| Com_show_table_status          | 1         |

| Com_show_tables                | 11        |

| Com_show_triggers              | 0         |

| Com_show_variables             | 1         |

| Com_show_warnings              | 0         |

| Com_slave_start                | 0         |

| Com_slave_stop                 | 0         |

| Com_stmt_close                 | 0         |

| Com_stmt_execute               | 0         |

| Com_stmt_fetch                 | 0         |

| Com_stmt_prepare               | 0         |

| Com_stmt_reprepare             | 0         |

| Com_stmt_reset                 | 0         |

| Com_stmt_send_long_data        | 0         |

| Com_truncate                   | 3         |

| Com_uninstall_plugin           | 0         |

| Com_unlock_tables              | 0         |

| Com_update                     | 874       |

| Com_update_multi               | 0         |

| Com_xa_commit                  | 0         |

| Com_xa_end                     | 0         |

| Com_xa_prepare                 | 0         |

| Com_xa_recover                 | 0         |

| Com_xa_rollback                | 0         |

| Com_xa_start                   | 0         |

| Compression                    | OFF       |

| Connections                    | 928       |

| Created_tmp_disk_tables        | 91        |

| Created_tmp_files              | 106       |

| Created_tmp_tables             | 191       |

| Delayed_errors                 | 0         |

| Delayed_insert_threads         | 0         |

| Delayed_writes                 | 0         |

| Flush_commands                 | 1         |

| Handler_commit                 | 0         |

| Handler_delete                 | 103       |

| Handler_discover               | 0         |

| Handler_prepare                | 0         |

| Handler_read_first             | 581       |

| Handler_read_key               | 311036    |

| Handler_read_next              | 19464     |

| Handler_read_prev              | 1453      |

| Handler_read_rnd               | 50264     |

| Handler_read_rnd_next          | 3625819   |

| Handler_rollback               | 0         |

| Handler_savepoint              | 0         |

| Handler_savepoint_rollback     | 0         |

| Handler_update                 | 82679     |

| Handler_write                  | 99262     |

| Key_blocks_not_flushed         | 0         |

| Key_blocks_unused              | 570693    |

| Key_blocks_used                | 723       |

| Key_read_requests              | 425279    |

| Key_reads                      | 2915      |

| Key_write_requests             | 1382      |

| Key_writes                     | 497       |

| Last_query_cost                | 0.000000  |

| Max_used_connections           | 3         |

| Not_flushed_delayed_rows       | 0         |

| Open_files                     | 564       |

| Open_streams                   | 0         |

| Open_table_definitions         | 280       |

| Open_tables                    | 284       |

| Opened_files                   | 3043      |

| Opened_table_definitions       | 521       |

| Opened_tables                  | 526       |

| Prepared_stmt_count            | 0         |

| Qcache_free_blocks             | 57        |

| Qcache_free_memory             | 263390552 |

| Qcache_hits                    | 9662      |

| Qcache_inserts                 | 5212      |

| Qcache_lowmem_prunes           | 0         |

| Qcache_not_cached              | 101       |

| Qcache_queries_in_cache        | 418       |

| Qcache_total_blocks            | 1187      |

| Questions                      | 19393     |

| Rpl_status                     | NULL      |

| Select_full_join               | 1         |

| Select_full_range_join         | 0         |

| Select_range                   | 1070      |

| Select_range_check             | 0         |

| Select_scan                    | 1192      |

| Slave_open_temp_tables         | 0         |

| Slave_retried_transactions     | 0         |

| Slave_running                  | OFF       |

| Slow_launch_threads            | 0         |

| Slow_queries                   | 0         |

| Sort_merge_passes              | 53        |

| Sort_range                     | 46        |

| Sort_rows                      | 547054    |

| Sort_scan                      | 551       |

| Ssl_accept_renegotiates        | 0         |

| Ssl_accepts                    | 0         |

| Ssl_callback_cache_hits        | 0         |

| Ssl_cipher                     |           |

| Ssl_cipher_list                |           |

| Ssl_client_connects            | 0         |

| Ssl_connect_renegotiates       | 0         |

| Ssl_ctx_verify_depth           | 0         |

| Ssl_ctx_verify_mode            | 0         |

| Ssl_default_timeout            | 0         |

| Ssl_finished_accepts           | 0         |

| Ssl_finished_connects          | 0         |

| Ssl_session_cache_hits         | 0         |

| Ssl_session_cache_misses       | 0         |

| Ssl_session_cache_mode         | NONE      |

| Ssl_session_cache_overflows    | 0         |

| Ssl_session_cache_size         | 0         |

| Ssl_session_cache_timeouts     | 0         |

| Ssl_sessions_reused            | 0         |

| Ssl_used_session_cache_entries | 0         |

| Ssl_verify_depth               | 0         |

| Ssl_verify_mode                | 0         |

| Ssl_version                    |           |

| Table_locks_immediate          | 9058      |

| Table_locks_waited             | 1         |

| Tc_log_max_pages_used          | 0         |

| Tc_log_page_size               | 0         |

| Tc_log_page_waits              | 0         |

| Threads_cached                 | 2         |

| Threads_connected              | 1         |

| Threads_created                | 3         |

| Threads_running                | 1         |

| Uptime                         | 1236      |

| Uptime_since_flush_status      | 1236      |

+--------------------------------+-----------+

Link to comment
Share on other sites

You're stats look good. Key reads/key read requests is 0.006 which is below recommended max or 0.01. Your key write/key write request is also good. It should be below 1 and yours is at 0.35.

Lower your table cache to 1024. Windows has a hard limit on open files which is 2048 so you should set it at half that.

Other than that it looks good. In fact you could lower key_buffer to 256. It might help performance a bit. Giving too much memory can affect performance as much as not enough.

Link to comment
Share on other sites

After some testing, I discovered that the Wincache 1.0 is causing crashes on my site, after installing xcache, everything was back to normal ..

try to install xcache, and as an alternative, make sure that uninstalling wincache you see some improvement in site performance.

Using xcache on Windows server is possible, following attach:

Link to comment
Share on other sites


You're stats look good. Key reads/key read requests is 0.006 which is below recommended max or 0.01. Your key write/key write request is also good. It should be below 1 and yours is at 0.35.



Lower your table cache to 1024. Windows has a hard limit on open files which is 2048 so you should set it at half that.



Other than that it looks good. In fact you could lower key_buffer to 256. It might help performance a bit. Giving too much memory can affect performance as much as not enough.




Can you confirm theese settings would be ok, when running alot of sites on my server, and 2 of them should run IPB 2.3.6 and another 3.0.5
Link to comment
Share on other sites


After some testing, I discovered that the Wincache 1.0 is causing crashes on my site, after installing xcache, everything was back to normal ..



try to install xcache, and as an alternative, make sure that uninstalling wincache you see some improvement in site performance.



Using xcache on Windows server is possible, following attach:




I didn't know that xcache worked with NTS binaries. I tried to install it and it wouldn't load. I would prefer xcache, but, wincache was better than no cache and it runs smoothly on my servers.
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...