Solved

stress testing

Posted on 2009-05-14
13
473 Views
Last Modified: 2013-12-16
hello there,
i've been working for 2 weeks already doing some stress testing on a server with a forum software written in php that uses mysql as the database.
the first test was to write posts into the forum as fast as we could to see the stats of server load and how many posts the server can handle without clogging.
now the second test was to see how many posts the server can have before it starts to slow down (when browsing the forum) after 2.2 million posts
with all random data text posted - the server load is at 1 or 1.50 which is alright but when browsing the forum some pages load slow.. we're using the same forum
software with a different database and a different domain on the same server with 10,000 posts and browsing it also loads fast.. what could be the problem here?
all tables in the database have been checked and 'ANALYZE' and 'OPTIMIZE' them.. all indexes are also checked and fine.. when I say load slow sometimes to load
a page takes 8 seconds sometimes takes less than 1 second sometimes takes 5 seconds, this is the only issue we are trying to figure out and find a solution to...
maybe running the database off memory can make things faster? although the size of the forum is over 4.2GB while for the 10k forum its just 17MB
0
Comment
Question by:XK8ER
  • 5
  • 5
  • 2
13 Comments
 
LVL 5

Expert Comment

by:TheVeee
ID: 24383960
With the volume your using your may need a faster processor and more memory on your server.   During your testing have you went to the task manager and monitored the memory use?  
0
 
LVL 14

Expert Comment

by:cjl7
ID: 24385612
Do you have IO-Wait?  (need faster disks)
 Does the system swap? (need more memory)
 Do you have a lot of memory left? Have you enabled caching in the database? How large is your db-cache?
 
 
 Do you do use any caching system? Using memcache and caching for say 30 seconds will dramatically lower the load and stress on the db-server!
 
 If you can't or don't want to cache stuff you should make sure that you only search for the data you need, don't use any "select  * from..." and try to pinpoint exactly which SQL's are running slow.
 
 This can be done by turning on slow_log and log_all_queries_not_using_indexes in the my.cnf file.
 
 Another good thing is to have multiple read-only databases (for searches) and one read-write since most web-based software has a ratio of 10:1 read vs write.
 
 Good luck,
 
 //jonas
0
 
LVL 1

Author Comment

by:XK8ER
ID: 24387820
how can I check if the server has (IO-Wait)

yes while testing I enabled log slow queries and also queries not using indexes.. slow seem good..

>>Using memcache and caching for say 30 seconds will dramatically lower the load and stress on the db-server!
sounds very interesting.. how exactly can I do that?
>>my.cnf

table_cache = 2048

thread_cache_size = 256

query_cache_size = 800M

query_cache_limit = 384M
 
 

>>Processors  	4

Model 	Intel(R) Xeon(TM) CPU 2.80GHz

CPU Speed 	2.8 GHz
 
 

>>Memory

Free           Used            Size

364.45 MB      3.11 GB         3.46 GB
 
 

>>Top results

top - 14:19:56 up 14 days,  2:50,  1 user,  load average: 3.11, 2.35, 2.25

Tasks: 327 total,   3 running, 317 sleeping,   5 stopped,   2 zombie

Cpu(s): 20.5%us, 12.8%sy,  0.0%ni, 59.4%id,  6.9%wa,  0.0%hi,  0.3%si,  0.0%st

Mem:   3631752k total,  3291776k used,   339976k free,    86624k buffers

Swap:  4192956k total,   192088k used,  4000868k free,   527516k cached 

Open in new window

0
 
LVL 5

Expert Comment

by:TheVeee
ID: 24389252
0
 
LVL 14

Expert Comment

by:cjl7
ID: 24393058
You may have disk problems. You have some IO-wait (6,9%).

To get a better view you might wanna run

sar -d 3 10

And post the result (while the server is busy).

//jonas
0
 
LVL 1

Author Comment

by:XK8ER
ID: 24393076

-(04:03 52 AM)-(root@dedicated)-(~) $ sar -d 3 10

-bash: sar: command not found

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:cjl7
ID: 24393207
RHEL/Fedora = yum install sysstat
Debian/Ubuntu = apt-get install sysstat
Gentoo = emerge -av sysstat

//jonas
0
 
LVL 1

Author Comment

by:XK8ER
ID: 24393243
ok, here's the results that I got
-(04:32 18 AM)-(root@dedicated)-(~) $ sar -d 3 10

Linux 2.6.18-128.1.6.el5 (dedicated.microkinetic.net)   05/15/2009
 

04:32:45 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:32:48 AM    dev8-0      0.67      0.00      5.33      8.00      0.00      3.00      1.50      0.10

04:32:48 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:48 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:48 AM    dev8-3      0.67      0.00      5.33      8.00      0.00      3.00      1.50      0.10
 

04:32:48 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:32:51 AM    dev8-0     56.67     50.67    880.67     16.44      0.68      7.12      1.05      5.93

04:32:51 AM    dev8-1      0.33      0.00      0.67      2.00      0.00      1.00      1.00      0.03

04:32:51 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:51 AM    dev8-3     56.33     50.67    880.00     16.52      0.68      7.16      1.05      5.93
 

04:32:51 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:32:54 AM    dev8-0    196.66     96.32   2793.31     14.69     28.35    145.55      1.99     39.16

04:32:54 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:54 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:54 AM    dev8-3    196.66     96.32   2793.31     14.69     28.35    145.55      1.99     39.16
 

04:32:54 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:32:57 AM    dev8-0     24.17    116.56    121.85      9.86      0.13      5.33      3.49      8.44

04:32:57 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:57 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:32:57 AM    dev8-3     24.17    116.56    121.85      9.86      0.13      5.33      3.49      8.44
 

04:32:57 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:00 AM    dev8-0     54.36    177.18    829.53     18.52      0.46      8.55      2.51     13.62

04:33:00 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:00 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:00 AM    dev8-3     54.36    177.18    829.53     18.52      0.46      8.55      2.51     13.62
 

04:33:00 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:03 AM    dev8-0    210.26     39.74   3645.03     17.52     49.78    229.46      2.60     54.77

04:33:03 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:03 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:03 AM    dev8-3    210.26     39.74   3645.03     17.52     49.78    229.46      2.60     54.77
 

04:33:03 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:06 AM    dev8-0    219.73    240.80   4005.35     19.32     15.24     76.41      3.71     81.44

04:33:06 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:06 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:06 AM    dev8-3    219.73    240.80   4005.35     19.32     15.24     76.41      3.71     81.44
 

04:33:06 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:09 AM    dev8-0      7.64     61.13      0.00      8.00      0.07      9.13      9.13      6.98

04:33:09 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:09 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:09 AM    dev8-3      7.64     61.13      0.00      8.00      0.07      9.13      9.13      6.98
 

04:33:09 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:12 AM    dev8-0    394.67      8.00   3760.00      9.55     76.81    178.51      1.37     53.97

04:33:12 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:12 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:12 AM    dev8-3    394.67      8.00   3760.00      9.55     76.81    178.51      1.37     53.97
 

04:33:12 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

04:33:15 AM    dev8-0    143.14     77.59   5380.60     38.13     40.60    328.19      2.88     41.24

04:33:15 AM    dev8-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:15 AM    dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

04:33:15 AM    dev8-3    143.14     77.59   5380.60     38.13     40.60    328.19      2.88     41.24
 

Average:          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

Average:       dev8-0    130.73     86.67   2140.07     17.03     21.22    162.29      2.34     30.55

Average:       dev8-1      0.03      0.00      0.07      2.00      0.00      1.00      1.00      0.00

Average:       dev8-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

Average:       dev8-3    130.70     86.67   2140.00     17.04     21.22    162.33      2.34     30.55

Open in new window

0
 
LVL 1

Author Comment

by:XK8ER
ID: 24393302
MySQLTuner results are also here
-(04:44 45 AM)-(root@dedicated)-(~) $ ./mysqltuner.pl
 

 >>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:
 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.45-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 2G (Tables: 915)

[--] Data in MEMORY tables: 7M (Tables: 6)

[!!] Total fragmented tables: 44
 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 4d 9h 40m 55s (8M q [21.160 qps], 412K conn, TX: 1B, RX: 2B)

[--] Reads / Writes: 66% / 34%

[--] Total buffers: 2.5G global + 67.2M per thread (500 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 35.3G (1018% of installed RAM)

[OK] Slow queries: 0% (1K/8M)

[OK] Highest usage of available connections: 14% (73/500)

[OK] Key buffer size / total MyISAM indexes: 1.3G/1.8G

[OK] Key buffer hit rate: 99.9% (1B cached / 676K reads)

[OK] Query cache efficiency: 50.8% (2M cached / 5M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 3% (17K temp sorts / 511K sorts)

[!!] Temporary tables created on disk: 48% (133K on disk / 277K total)

[OK] Thread cache hit rate: 99% (73 created / 412K connections)

[OK] Table cache hit rate: 25% (1K open / 7K opened)

[OK] Open file limit used: 58% (2K/4K)

[OK] Table locks acquired immediately: 99% (5M immediate / 6M locks)
 

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Temporary table size is already large - reduce result set size

    Reduce your SELECT DISTINCT queries without LIMIT clauses

Open in new window

0
 
LVL 14

Accepted Solution

by:
cjl7 earned 500 total points
ID: 24393380
OK, so your disk is ok. Some high numbers but still it's not to bad.

What is your long_query_time set to?

show variables like 'long_query_time';

Otherwise you might just get exactly what you ask for?! Can you reduce the size of the results/searches? Use temporary tables for common searches? Any database can become slow depending on the appliation (or rather the SQL running).

Can you setup replica-databases?

//jonas
0
 
LVL 1

Author Comment

by:XK8ER
ID: 24401272
in the my.cnf this line is shown like this
#long_query_time = 5

the reason why im doing all of these stress testing is because there are out there live forums with over 2million posts and a lot of traffic browsing all the pages and everything seems perfectly normal with pages loading fast and smooth..

I dont see whats the issue on my dedicated server.. why are the pages sometimes loading slow..
0
 
LVL 14

Expert Comment

by:cjl7
ID: 24406082
uncomment the #long_query_time = 5 and restart MySQL.

"I dont see whats the issue on my dedicated server.. why are the pages sometimes loading slow.." is it a physical server or a virtual?

One problem with virtual servers are perfomance and especially IO.

"there are out there live forums with over 2million posts" - yes, but probably not on a single server (virtual?)...

Scalability is basically math + money... You can only do so much with a single box, that's why everyone want's to "scale out" instead of "scale up".

//jonas
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now