As usual .. Performance checks.

Started by Feldunost, March 07, 2012, 11:45:22 AM

Previous topic - Next topic

Feldunost

Hello,


I was using phpbb at old times, but engine is far too much boring for me and i went on SMF, it was great but some problems appeared, then i took one dedicated server which slows kinda at 20~30 concurrent connections.

Here is the status.php:

Basic Information
March 07, 2012, 05:34:58 PM
Operating System:    Microsoft Windows [version 5.2.3790]
Current processes:    59 (59 running, 0 zombie)
Processes by CPU:    System Idle Process (1) 85.9%, httpd.exe (2) 8.2%, WS_.bin (1) 1.9%
MySQL Statistics
MySQL 5.0.x
Connections per second:    2.3065
Kilobytes received per second:    7.091
Kilobytes sent per second:    24.2237
Queries per second:    23.4511
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    4.4483 (should be <= 80)
Table cache usage:
(table_cache)    0.1133 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0011 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.6498 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    554.125 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.1094 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)


I'm using the following:
Apache 2 & PHP 5
MyISAM tables storage
SMF (lastest stable version: 2.0.2)
SimpleBoardPortal (Shoutbox activated and only visible on index and not shown by guests, 5sec refresh)
SMF Shop


I have done some great efforts but the forum is really slow to loads: 2 min ...

Feldunost

My.conf that i modified a little ...
I'm french if it helps talking in french, just tell me :)
Thanks.


#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#

[client]

port=3306

[mysql]

default-character-set = utf8

# SERVER SECTION

[mysqld]

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

# 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
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'

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

# Set the SQL mode to strict
# sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=1024
#old_passwords   = 1
query_cache_size=64
table_cache=256
tmp_table_size=64M
thread_cache_size=64

#*** MyISAM Specific options

myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=17M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
log-slow-queries=mysql-slow.log
log-error=mysql.err

#*** INNODB Specific options ***

skip-innodb
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=512M
innodb_log_file_size=10M
innodb_thread_concurrency=8

Roph

What stats for your forum? How many posts and users? How many users online?

Have you done any of the steps from this topic?

Feldunost

Hello,


I went throught my.conf and edited then i have this:



Basic Information
March 07, 2012, 09:27:29 PM
Operating System:    Microsoft Windows [version 5.2.3790]
Current processes:    66 (66 running, 0 zombie)
Processes by CPU:    System Idle Process (1) 84.0%, httpd.exe (3) 8.9%, WS_.bin (1) 2.3%, explorer.exe (3) 0.7%
MySQL Statistics
MySQL 5.0.x
Connections per second:    2.2799
Kilobytes received per second:    6.4304
Kilobytes sent per second:    23.534
Queries per second:    22.6728
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    5.1 (should be <= 80)
Table cache usage:
(table_cache)    0.0586 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0056 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.6232 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1650.6667 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.875 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.3343 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)






my.conf



[client]
#password   = your_password
port      = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port      = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout






Actual Stats

232 Posts in 66 Topics by 275 Members.
3 Guests, 7 Users
Most Online Today: 21. Most Online Ever: 21 (Today at 04:03:10 pm)



Predictable Stats in less than 2 months:

4000 - 5000 Posts in 500 Topics by 1200 Members.
10 Guests, 25-30 Users
Most Online Today: 50. Most Online Ever: 50 (Today at 04:03:10 pm)



Dedicated server running on VmWare ESXi.
-> VM: Windos 2003 R2 SP2
Allowed: 3 cores @ 2.80 Ghz
Allowed: 12 Gb DDR3 ECC
Allowed: 400 Gb HDD
Process: Mysql 96 Mb Ram
Process: Httpd (3x) count: 56 Mb + 54 Mb + 33 Mb


Average consumption:
3.38 Gb Ram
15 - 20% Cpu
4% of allowed bandwidth




I have already read your topic, but i saw some problems seems with Innodb on this topic.
So i'm not really sure.

Actually performance problem is solved, but it can be better i think.

Feldunost

Hello,


Actually we are getting some security problems with ddos.

If someone could help me, feel free to contact me by private message, since there is some strange things which occured.


Thanks.



Advertisement: