Solved

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

Posted on 2012-03-26
4
933 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
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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…
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.

929 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

9 Experts available now in Live!

Get 1:1 Help Now