Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

8 Experts available now in Live!

Get 1:1 Help Now