Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » MySQL Server Really High Load Question
MySQL Server Really High Load Question [message #3565] Wed, 24 September 2008 09:58 Go to next message
kostik83 is currently offline kostik83
Messages: 4
Registered: February 2008
Junior Member
Hey Guys, How are you?

I am running a starting-to-get-really-popular community website and, although my server specs are pretty high, starting to have mysql problems. At times when I get around 500 people on the site, mysql process jumps to 170% and the site becomes unresponsive. The Database Engine is InnoDB. I have tried numerous configurations but still the problem remains the same. I think i am simply overlooking something and i hope you can point me in the right direction.

Server:
PowerEdge 2950
2 x Quad Core Intel® Xeon® E5310, 2x4MB Cache, 1.60GHz
16 Gb RAM

OS & MySQL:
Running FreeBSD 6.2 with mysql community server 5.1.22-rc

Here are some of the important specs/config variables:

Total buffers: 3.4G global + 58.2M per thread (512 max threads)
Reads / Writes: 89% / 11%
InnoDB data size / buffer pool: 1.9G/3.0G

query_cache_min_res_unit = 1024
thread_cache = 64
query_cache_size = 128M
wait_timeout = 60
interactive_timeout = 120
connect_timeout = 10

max_connections = 512
max_allowed_packet = 64M
max_heap_table_size = 256M

tmp_table_size = 256M
max_tmp_tables = 256

thread_cache_size = 64
thread_concurrency = 16

innodb_buffer_pool_size = 3G
innodb_data_file_path = ibdata1:200M:autoextend
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 15
innodb_thread_concurrency = 16

Anyone can suggest improvements without going master-slave direction?

PS: There is already a layer of caching implemented.
Report message to a moderator
Re: MySQL Server Really High Load Question [message #3570 is a reply to message #3565 ] Thu, 25 September 2008 18:59 Go to previous messageGo to next message
MarkRose is currently offline MarkRose
Messages: 94
Registered: January 2008
Member
What do 'iostat' and 'gstat' show for disk usage?

[Updated on: Thu, 25 September 2008 19:01]

Report message to a moderator
Re: MySQL Server Really High Load Question [message #3572 is a reply to message #3570 ] Fri, 26 September 2008 09:57 Go to previous messageGo to next message
kostik83 is currently offline kostik83
Messages: 4
Registered: February 2008
Junior Member
during moderate stress (CPU at about 88%)
everything is at 0

gstat

dT: 0.501s w: 0.500s
L(q) ops/s r/s kBps ms/r w/s kBps ms/w %busy Name
0 0 0 0 0.0 0 0 0.0 0.0| acd0
0 0 0 0 0.0 0 0 0.0 0.0| mfid0
0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1
0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1a
0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1b
0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1c


iostat
tty mfid0 cpu
tin tout KB/t tps MB/s us ni sy in id
0 85 25.10 10 0.26 0 0 0 0 99
Report message to a moderator
Re: MySQL Server Really High Load Question [message #3573 is a reply to message #3572 ] Fri, 26 September 2008 11:34 Go to previous messageGo to next message
kostik83 is currently offline kostik83
Messages: 4
Registered: February 2008
Junior Member
i think the issue is with innodb_thread_concurrency = 16

i changed the value to 0, will see during next stress time how it will perform.

[Updated on: Fri, 26 September 2008 11:34]

Report message to a moderator
Re: MySQL Server Really High Load Question [message #3593 is a reply to message #3565 ] Tue, 30 September 2008 01:23 Go to previous messageGo to next message
Mr.liping is currently offline Mr.liping
Messages: 1
Registered: September 2008
Location: shanghai of China
Junior Member
I think ,your Os need 64-bit!
Because in the 32-bit operating system, the largest single process of addressing space for the 4GB

[Updated on: Tue, 30 September 2008 01:24]

Report message to a moderator
Re: MySQL Server Really High Load Question [message #3595 is a reply to message #3593 ] Tue, 30 September 2008 10:33 Go to previous message
kostik83 is currently offline kostik83
Messages: 4
Registered: February 2008
Junior Member
we r actually running 64 bit OS

$ uname -a
FreeBSD 6.2-RELEASE #0: Fri Jan 12 08:43:30 UTC 2007 root@portnoy.cse.buffalo.edu:/usr/obj/usr/src/sys/SMP amd64
Report message to a moderator
Previous Topic:Slow Log Analyser
Next Topic:FLUSH_TABLE Statement
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 05:51:38 EDT 2009

Total time taken to generate the page: 0.00810 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.