Solved

stress testing

Posted on 2009-05-14
13
470 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 14

Expert Comment

by:cjl7
Comment Utility
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
Comment Utility

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

-bash: sar: command not found

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 14

Expert Comment

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

//jonas
0
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

10 Experts available now in Live!

Get 1:1 Help Now