Solved

how do you know mysql 5.5 actually using multi thread??

Posted on 2012-03-26
4
955 Views
Last Modified: 2012-04-09
hi
i know mysql 5.5 is a multithread

and i should see atleast 3 process againt one mysql .

but for a busy mysql server, i m only seeing 1 process which is using almost 100% cpu ...

ref :http://dev.mysql.com/doc/refman/5.5/en/faqs-general.html#qandaitem-B-1-1-8

thats why i am bit confused ..
i am expecting the mysql load to spread over the core ( i have 8 core)
but only 1 core is using

here
innodb_thread_concurrency  = 8
is enable.


example :

top - 15:58:13 up 2 days, 23:14,  1 user,  load average: 1.18, 1.46, 1.48
Tasks: 169 total,   2 running, 167 sleeping,   0 stopped,   0 zombie
Cpu0  : 12.6%us,  3.0%sy,  0.0%ni,  6.3%id, 78.1%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  18874368k total, 18791312k used,    83056k free,    32672k buffers
Swap:  7373824k total,  4055872k used,  3317952k free,  2754756k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6388 mysql     15   0 16.6g  13g 5112 S 14.0 77.7 133:39.70 mysqld

i haved compilde this mysql from source ..
not sure if i have to enable some option to make it multithread ..

(although, i belived i dont have to)

thanks
0
Comment
Question by:fosiul01
[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
  • 2
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
ravenpl earned 500 total points
ID: 37767373
AFAIK mysql is using thread per connection, what is Your database usage pattern? Maybe You have one client at time only?

Refer http://dev.mysql.com/doc/refman/5.5/en/mysql-threads.html

And true, if many threads are using cpu(within one process) the process is shown more than 100% cpu usage.

To see each process' threads: "ps axm" or "ps -eLf" or "top -H" or while in top shift+h
0
 
LVL 29

Author Comment

by:fosiul01
ID: 37768268
hmm still not clear...
i read that one .. and
innodb_read_io_threads and innodb_write_io_threads  are set to 8 as its 8 core ..

yes, shift+h shows more then one process under mysql ..
but i check a server which has one core .. its same out put as 8 core ..

what do you mean by what is Your database usage pattern? ??

this server is working as slave and for reporting purpose ..
0
 
LVL 43

Expert Comment

by:ravenpl
ID: 37770041
> this server is working as slave and for reporting purpose ..
Maybe that's the problem. One concurrent client is using one thread only.
There are of course other threads (like read/write threads) but they will not use cpu extensively.
Maybe You should perform a test: connect many concurrent clients each executing cpu intensive operation?
0
 
LVL 29

Author Comment

by:fosiul01
ID: 37770102
Maybe You should perform a test: connect many concurrent clients each executing cpu intensive operation?  :

is there any tools to use that ??
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

705 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