hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Dedicated Server : Huge server but slow performance (20sec per page)
Reply

Forum Jump

Huge server but slow performance (20sec per page)

Reply Post New Thread In Dedicated Server Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 03-13-2010, 02:45 PM
hostyourdream hostyourdream is offline
Web Hosting Master
 
Join Date: May 2009
Location: Greece
Posts: 633
Question

Huge server but slow performance (20sec per page)


Hello

I own a server:

# 2x Intel Xeon Quad 5405
# 12GB DDR2 RAM
# 4 SAS disks Raid 10
# 1.000 Mbit unshared port

And about 400 users per minute and i need 20 seconds to open a page in my invision forum.

I use Apache 2.2 and php 5.3.2 om a Centos server.

Cpanel as control panel and i use fastcgi and mpm worker.

I think i must raise some limits but i don;t know which and where

Some stats:

top

Code:
top - 20:14:03 up 2 days,  6:45,  2 users,  load average: 16.61, 17.32, 17.76
Tasks: 288 total,   7 running, 277 sleeping,   2 stopped,   2 zombie
Cpu(s): 23.9%us, 18.9%sy,  0.0%ni, 56.3%id,  0.5%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  12306280k total, 10687768k used,  1618512k free,  1840352k buffers
Swap: 15623172k total,        0k used, 15623172k free,  3876288k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15181 mysql     15   0 1931m 453m 2996 S 441.9  3.8  15:31.79 mysqld
13084 amorem    16   0  256m  47m  24m S 33.2  0.4   0:07.69 php5
13080 amorem    15   0  257m  47m  24m S  9.8  0.4   0:07.49 php5
13331 amorem    15   0  256m  46m  24m S  9.8  0.4   0:07.80 php5

netstat -apn | grep :80 |wc -l

3158


mysql

\s

Threads: 76 Questions: 41093 Slow queries: 52 Opens: 379 Flush tables: 1 Open tables: 373 Queries per second avg: 112.276



vmstat

Code:
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 1608404 1841076 3946172    0    0     2   787    4    0 24 19 56  1  0

mysqladmin processlist

Code:
+------+----------------+-----------+----------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id   | User           | Host      | db             | Command | Time | State          | Info                                                                                                 |
+------+----------------+-----------+----------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 3325 | eximstats      | localhost | eximstats      | Sleep   | 18   |                |                                                                                                      |
| 4380 | datab_user | localhost | datab_name | Query   | 9    | Locked         | UPDATE ibf_members SET login_anonymous='0&1', last_activity=1268504302 WHERE id=23130                |
| 4381 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109102 WHERE id=21461                             |
| 4385 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109104 WHERE id=4848                              |
| 4388 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=31455                             |
| 4394 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4393 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4392 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4390 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=38242                             |
| 4396 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4397 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4400 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=31821                             |
| 4401 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=19414                             |
| 4410 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4415 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=53541                             |
| 4412 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET login_anonymous='0&1', last_visit=last_activity, last_activity=1268504304 WHE |
| 4409 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=42860                             |
| 4407 | datab_user | localhost | datab_name | Query   | 9    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=43876                             |
| 4402 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=41752                             |
| 4399 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4417 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4419 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4420 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4424 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=25264                             |
| 4422 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=52429                             |
| 4425 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4426 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4427 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4428 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4430 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4431 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4436 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4437 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4440 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4442 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4444 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4441 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4439 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=45779                             |
| 4438 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4445 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4446 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4448 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=34334                             |
| 4449 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4450 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=35502                             |
| 4452 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4453 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4455 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=18162 WHERE id = 4038                                            |
| 4457 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=38502 WHERE id = 17015                                           |
| 4458 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=37168                             |
| 4459 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=2479 WHERE id = 480                                              |
| 4460 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4461 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4462 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4464 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4466 | datab_user | localhost | datab_name | Query   | 1    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=34334                             |
| 4468 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, mgroup FROM ibf_members WHERE id IN (1,40471,33056,45688,26198,36973,30786,40471,26380,41 |
| 4470 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT name, id FROM ibf_members WHERE members_l_username='despoina2012' LIMIT 0,1                   |
| 4471 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4473 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4474 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4475 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT p.*, pp.*,
                                m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |
| 4476 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, mgroup FROM ibf_members WHERE id IN (1,40471,40471,45688,28343,32299,30786,40471,26380,41 |
| 4477 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT p.*, pp.*,
                                m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |
| 4478 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4479 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4480 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4481 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4482 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4484 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4485 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4486 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4487 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4488 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4490 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4492 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4494 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT m.id, m.name, m.members_display_name, m.members_created_remote, m.email, m.mgroup, m.member_l |
| 4495 | root           | localhost |                | Query   | 0    |                | show processlist
When i run a reindex command in the database is working much faster but i need a permanent solution.

Any ideas are welcome



Sponsored Links
  #2  
Old 03-13-2010, 02:49 PM
devonblzx devonblzx is offline
Web Hosting Master
 
Join Date: Nov 2005
Location: Michigan, USA
Posts: 3,660
Have you optimized your mysql server (my.cnf) at all?

  #3  
Old 03-13-2010, 02:53 PM
yajur yajur is offline
Web Hosting Master
 
Join Date: Nov 2007
Location: India
Posts: 843
what your apache maxclient value ? if it 250 something rise to 400 like that also increase the serverlimit value to the same

__________________
HostNotch Hosting Services 99.9% uptime Shared Hosting, Reseller Hosting
yajur | Sales Team
CPanel Hosting R1 Soft Offsite-Backup Great Uptime
http://hostnotch.com sales @ hostnotch.com

Sponsored Links
  #4  
Old 03-13-2010, 02:59 PM
hostyourdream hostyourdream is offline
Web Hosting Master
 
Join Date: May 2009
Location: Greece
Posts: 633
my.cnf:

[mysqld]
skip-innodb
skip-networking
skip-locking
max_connections = 200
key_buffer_size = 400M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 8M
long_query_time = 5
table_cache = 1800
thread_cache_size = 384
wait_timeout = 120
connect_timeout = 60
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
concurrent_insert = 2

Mysql tunner reports only as a problem that i have some locked tables.

  #5  
Old 03-13-2010, 03:02 PM
NoSupportLinuxHostin NoSupportLinuxHostin is offline
Web Hosting Master
 
Join Date: Jan 2010
Location: USA
Posts: 2,076
What type of MySQL tables are you using? I would suggest InnoDB rather than MyISAM due to locking issues. MyISAM only allows table level locking, while InnoDB can do row level. If you use MyISAM, the entire table gets locked and this causes some of the simultaneous connections to deadlock each other, resulting is terrible performance.

__________________
No Support Linux Hosting Bargain cPanel Hosting Experts Only
We IGNORE the support questions, and pass the SAVINGS on to YOU!
We also ignore questions about VPS Hosting

  #6  
Old 03-13-2010, 03:25 PM
Bono_ Bono_ is offline
Junior Guru
 
Join Date: Apr 2007
Posts: 181
InnoDB is not recommended for IPB 3.x and for searching you can use sphinx.

  #7  
Old 03-13-2010, 03:42 PM
hostyourdream hostyourdream is offline
Web Hosting Master
 
Join Date: May 2009
Location: Greece
Posts: 633
I am using invision v2.3.6 and MyISAM tables.

  #8  
Old 03-13-2010, 03:52 PM
Zachary Nelson Zachary Nelson is offline
Premium Member
 
Join Date: Apr 2009
Posts: 6,366
Why are you not using the latest version of IP.Board?

  #9  
Old 03-13-2010, 04:26 PM
mariushm mariushm is offline
Web Hosting Master
 
Join Date: May 2008
Posts: 857
At the amount of memory your server has, you could give mySQL more memory.

I'd increase the maximum number of connections, I'd convert to innoDB tables, I'd consider setting up memcached to do some caching (but that's only after you optimize sql a lot).

If there are still issues, look into Apache's configuration and you might want to try and see if there's any difference with apache 2.0.x instead of 2.2.x

Any reason you use php with FastCGI instead of Apache module? AFAIK it's faster this way...

  #10  
Old 03-13-2010, 06:14 PM
barry[CoffeeSprout] barry[CoffeeSprout] is offline
Premium Member
 
Join Date: Oct 2003
Location: The Netherlands
Posts: 1,208
Quote:
Originally Posted by Bono_ View Post
InnoDB is not recommended for IPB 3.x and for searching you can use sphinx.
I wonder why though :-/

The query output shows a lot of locking on the same table, caused by the login code:

4415 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=53541 |
| 4412 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET login_anonymous='0&1', last_visit=last_activity, last_activity=1268504304 WHE |
| 4409 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=42860 |
| 4407 | datab_user | localhost | datab_name | Query | 9 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=43876 |


MyISAM basically only lets one thread update itself at a time.
InnoDB does not have this problem.

Yes, you'll need something like Spynx for FTS as InnoDB does not support this
My guess is that your problems will vanish as soon as those tables are rebuilt into InnoDB

  #11  
Old 03-13-2010, 06:26 PM
funkywizard funkywizard is offline
unghhh... Baaandwidth....
 
Join Date: Jan 2005
Posts: 8,631
it's definitely mysql from what you're looking at. upgrade invision if you can, change to innodb if you can, and set up a cron to reindex / optimize tables nightly. if that doesn't do the deal, you may want a dedicated database server with 2x xeon 5520 cpus.

__________________
IOFLOOD.com -- We Love Servers
Need More Ram? 12 core 72gb ram servers are here!
Email (sales [at] ioflood . com) for details.

  #12  
Old 03-13-2010, 06:27 PM
hostyourdream hostyourdream is offline
Web Hosting Master
 
Join Date: May 2009
Location: Greece
Posts: 633
@OneManHost

A lot more resources need it by v3 of invision.

@funkywizard

A better server will not help at all as i already have a lot of free resources ram and cpu.



Ok so which tables do you thing that i must convert to InnoDB?

Please check my report in first post and recommend me the correct tables.

InnoDB maybe solve my problems but does huge forums use it or i wil have problems later with sql performance when i have more users and more posts?

Thank you


Last edited by hostyourdream; 03-13-2010 at 06:33 PM.
  #13  
Old 03-13-2010, 06:51 PM
Arber Arber is offline
Aspiring Evangelist
 
Join Date: Jun 2008
Posts: 404
you should try
Dual 5570
16GB DDR3RAM
4x1TB HD on raid-10

IT may be your server location. Try using site load time checker
Quote:
Originally Posted by hostyourdream View Post
Hello

I own a server:

# 2x Intel Xeon Quad 5405
# 12GB DDR2 RAM
# 4 SAS disks Raid 10
# 1.000 Mbit unshared port

And about 400 users per minute and i need 20 seconds to open a page in my invision forum.

I use Apache 2.2 and php 5.3.2 om a Centos server.

Cpanel as control panel and i use fastcgi and mpm worker.

I think i must raise some limits but i don;t know which and where

Some stats:

top

Code:
top - 20:14:03 up 2 days,  6:45,  2 users,  load average: 16.61, 17.32, 17.76
Tasks: 288 total,   7 running, 277 sleeping,   2 stopped,   2 zombie
Cpu(s): 23.9%us, 18.9%sy,  0.0%ni, 56.3%id,  0.5%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  12306280k total, 10687768k used,  1618512k free,  1840352k buffers
Swap: 15623172k total,        0k used, 15623172k free,  3876288k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15181 mysql     15   0 1931m 453m 2996 S 441.9  3.8  15:31.79 mysqld
13084 amorem    16   0  256m  47m  24m S 33.2  0.4   0:07.69 php5
13080 amorem    15   0  257m  47m  24m S  9.8  0.4   0:07.49 php5
13331 amorem    15   0  256m  46m  24m S  9.8  0.4   0:07.80 php5

netstat -apn | grep :80 |wc -l

3158


mysql

\s

Threads: 76 Questions: 41093 Slow queries: 52 Opens: 379 Flush tables: 1 Open tables: 373 Queries per second avg: 112.276



vmstat

Code:
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 1608404 1841076 3946172    0    0     2   787    4    0 24 19 56  1  0

mysqladmin processlist

Code:
+------+----------------+-----------+----------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id   | User           | Host      | db             | Command | Time | State          | Info                                                                                                 |
+------+----------------+-----------+----------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 3325 | eximstats      | localhost | eximstats      | Sleep   | 18   |                |                                                                                                      |
| 4380 | datab_user | localhost | datab_name | Query   | 9    | Locked         | UPDATE ibf_members SET login_anonymous='0&1', last_activity=1268504302 WHERE id=23130                |
| 4381 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109102 WHERE id=21461                             |
| 4385 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109104 WHERE id=4848                              |
| 4388 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=31455                             |
| 4394 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4393 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4392 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4390 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=38242                             |
| 4396 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4397 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4400 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=31821                             |
| 4401 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=19414                             |
| 4410 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4415 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=53541                             |
| 4412 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET login_anonymous='0&1', last_visit=last_activity, last_activity=1268504304 WHE |
| 4409 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=42860                             |
| 4407 | datab_user | localhost | datab_name | Query   | 9    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=43876                             |
| 4402 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=41752                             |
| 4399 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4417 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4419 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4420 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4424 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=25264                             |
| 4422 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=52429                             |
| 4425 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4426 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4427 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4428 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4430 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4431 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4436 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4437 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4440 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4442 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4444 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4441 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4439 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=45779                             |
| 4438 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4445 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4446 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4448 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=34334                             |
| 4449 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4450 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=35502                             |
| 4452 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4453 | datab_user | localhost | datab_name | Query   | 9    | Sorting result | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4455 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=18162 WHERE id = 4038                                            |
| 4457 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=38502 WHERE id = 17015                                           |
| 4458 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=37168                             |
| 4459 | datab_user | localhost | datab_name | Query   | 6    | Locked         | UPDATE ibf_members SET thanks_point=2479 WHERE id = 480                                              |
| 4460 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT t.title, t.tid, t.last_poster_name, t.last_poster_id, t.views, t.posts as replies, m.mgroup F |
| 4461 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4462 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4464 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4466 | datab_user | localhost | datab_name | Query   | 1    | Locked         | UPDATE ibf_members SET member_login_key_expire=1269109109 WHERE id=34334                             |
| 4468 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, mgroup FROM ibf_members WHERE id IN (1,40471,33056,45688,26198,36973,30786,40471,26380,41 |
| 4470 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT name, id FROM ibf_members WHERE members_l_username='despoina2012' LIMIT 0,1                   |
| 4471 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4473 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, posts, members_display_name, mgroup FROM ibf_members ORDER BY posts desc LIMIT 0,10       |
| 4474 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4475 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT p.*, pp.*,
                                m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |
| 4476 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, mgroup FROM ibf_members WHERE id IN (1,40471,40471,45688,28343,32299,30786,40471,26380,41 |
| 4477 | datab_user | localhost | datab_name | Query   | 6    | Locked         | SELECT p.*, pp.*,
                                m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m |
| 4478 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4479 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4480 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4481 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT t.tid,s.mgroup as s_group,p.mgroup as p_group FROM ibf_topics t  LEFT JOIN ibf_members s ON ( |
| 4482 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4484 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4485 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4486 | datab_user | localhost | datab_name | Query   | 9    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4487 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4488 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4490 | datab_user | localhost | datab_name | Query   | 8    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4492 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT id, name, mgroup, member_login_key, member_login_key_expire, email, restrict_post, view_sigs, |
| 4494 | datab_user | localhost | datab_name | Query   | 1    | Locked         | SELECT m.id, m.name, m.members_display_name, m.members_created_remote, m.email, m.mgroup, m.member_l |
| 4495 | root           | localhost |                | Query   | 0    |                | show processlist
When i run a reindex command in the database is working much faster but i need a permanent solution.

Any ideas are welcome

  #14  
Old 03-13-2010, 06:54 PM
Bono_ Bono_ is offline
Junior Guru
 
Join Date: Apr 2007
Posts: 181
Quote:
Originally Posted by barry[CoffeeSprout] View Post
I wonder why though :-/

The query output shows a lot of locking on the same table, caused by the login code:

4415 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=53541 |
| 4412 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET login_anonymous='0&1', last_visit=last_activity, last_activity=1268504304 WHE |
| 4409 | datab_user | localhost | datab_name | Query | 6 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=42860 |
| 4407 | datab_user | localhost | datab_name | Query | 9 | Locked | UPDATE ibf_members SET member_login_key_expire=1269109108 WHERE id=43876 |


MyISAM basically only lets one thread update itself at a time.
InnoDB does not have this problem.

Yes, you'll need something like Spynx for FTS as InnoDB does not support this
My guess is that your problems will vanish as soon as those tables are rebuilt into InnoDB
He should find what is causing those locks probably some custom code or addon and he needs to resolve that before converting on InnoDB or throwing more hw on problem. And of course he should avoid converting to version 3 because it's even more intesive than v2.
And amount of data he is pushing with his server is nothing for that server, I push twice as much on my server which is only single quad core with 4GB of ram.


Last edited by Bono_; 03-13-2010 at 07:01 PM.
  #15  
Old 03-13-2010, 07:00 PM
mariushm mariushm is offline
Web Hosting Master
 
Join Date: May 2008
Posts: 857
Table ibf_members looks like it's locked a lot... as it was said myISAM would lock the whole table on each insert or update.

So I would convert that one first to innoDB. I doubt it has some fields that would be needed for searching - in the worst case functionality of searching user descriptions or something like that would be lost.

But don't forget to enable innoDB in your mysql configuration file and give it some good memory to work with.

Reply

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dedicate server with huge RAM and HDD,good cost performance och007 Dedicated Server 8 01-12-2010 02:42 PM
VPS incredibly slow, huge numbers of slow MySQL queries for Plesk/Watchdog lumpysimon Hosting Security and Technology 3 11-11-2008 12:31 PM
Do control panels slow down server performance? Adam W Dedicated Server 6 12-29-2007 12:00 AM
Server slow loading full page photoads Dedicated Server 4 04-24-2007 11:10 AM
Slow Server / Help Maximizing My Servers Performance some_random_dude Hosting Security and Technology 9 06-20-2006 07:59 PM

Related posts from TheWhir.com
Title Type Date Posted
Strong Uptime for Online Retailers as Holiday Shopping Kicks Off Web Hosting News 2013-12-03 10:42:42
The Impact of Third-Party Ad Networks on Site Load Times Web Hosting News 2013-08-12 11:14:55
WiredTree Decreases Customer Website Loading Times with SSD Caching Web Hosting News 2013-04-02 13:56:08
Q&A: NetDNA VP Samir Said on Web Host Strategies for Working With CDNs Web Hosting News 2012-09-13 12:06:29
1&1 Study Shows 72 Percent of US Online Shoppers Will Abandon Slow Sites for Competitors Web Hosting News 2012-07-13 16:18:05


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?