Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

stress testing

Posted on 2009-05-14
13
Medium Priority
?
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Fine Tune your automatic Updates for Ubuntu / Debian
In this article, we’ll look at how to deploy ProxySQL.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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