Tune mysql for a heavy load

I have mysql5.1 installed on centos
memory: 128G
disk space 200G
CPU : 16 with the following specifics:
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 62
model name      : Intel(R) Xeon(R) CPU E5-2670 v2 @ 2.50GHz
stepping        : 4
microcode       : 1046
cpu MHz         : 2500.193
cache size      : 25600 KB
physical id     : 0
siblings        : 16
core id         : 0
cpu cores       : 8
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good xtopology unfair_spinlock pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm xsaveopt fsgsbase smep erms
bogomips        : 5000.38
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual

Open in new window

I have a simple select query that takes 4.883 seconds to run with 1000 concurrent clients and take 0.001 seconds when no other clients are connected to mysql server.

note that when 1000 clients are connected to the server and running the same query, CPU goes up to 800% !!!!

how can I tune mysql to run execute queries faster in a heavy load?

thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Here is a list of tools I use.

I use the config file generator. You can add all your needs to it and it will return an optimized my.conf file. I also recommend to use persona. It has great tools and it full replacement for mysql (nothing to configure.) Another one is mariaDB. (Just to let you know that I'm not hired by Percona)

You can optimize your query's. It is allot of work but you can use neorProfiler for this.
It is a really cool tool. Connect your app to it and it will anylize execution times and will give information on what indexes are used or should be used. So you can add and remove indexes to optimize everything.

Another great thing to check is that your db size can be loaded into your memory. It will increase performance the most.

To start again over Percona. You can build a cluster with it and start reading about sharding.
Good luck.

kind regards.
CCVOIPAuthor Commented:
thanks for the response.

how to connect your apps to profilesql ?

I am using mysqlslap to stress load the database.


Neor profiler will be connected to your mysql while you configure it.
And then it will listen to another port. You can choose that port yourself.

Then in your app you can simply change connection string to connect to the port of your profiler.
Then everything will work the same becouse neor will forward everything but monitors in between.

kind regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

CCVOIPAuthor Commented:
I was able to configure my load testing tool to go through NeorProfiler, how ever I couldn't exceed 400 concurrent threads, CPU goes really high and this for just one select query.

I ran mysqlslap tool on the db host, I couldn't perform tests for --concurrency >1000 ( I was limited to 1000 concurrent clients)
the same query took less time for : --iterations=1 --concurrency=1000 after I tuned  innodb_thread_concurrency from =8 to =0, but CPU still more than 800%

CCVOIPAuthor Commented:
and running mysqlslap on db host I getting :Error when connecting to server: 2001 Can't create UNIX socket (24)  when --concurrency >1000
CCVOIPAuthor Commented:
thanks, this helped a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.