Re: How To Use More Resources

[Rick James]

Glad to hear that things are working better.

> but I can not tell if is because the changes you sugested the ones I changed or both

It is hard to say. All should be beneficial. Combinations of changes may be more beneficial than the sum of the individual changes; so, again, it is hard to say.

> My server has RAID 5+0 with 8Hdds total

This helps explain how your system can handle the traffic that GLOBAL STATUS implied.

5+0?? That's a new one on me. Does that have the capacity of 6 drives? Is there much advantage over RAID-5?

Keep in mind when going for fancy RAID that RAID protects against drive-failures; there are many other things that can go wrong with the whole system. You might consider Replication or Clustering (Galera) as other ways of assuring High Availability. (The Replication forum has a bunch of chatter about such.)

Do you have a _hardware_ controller with battery-backed-write-cache? That makes the writes virtually instantaneous from the MySQL's point of view. Still, there is a steady-state limit on the bandwidth to disk. Some of my recommended changes were aimed at decreasing the need for I/O bandwidth. Your changes hopefully increase the bandwidth. (It seems likely, but it depends on drives, controllers, cores, workload, etc, etc. And I do not have enough breadth of experience to do more than guess.)

> we migrated the DB from Sybase To Mysql made al decimal type.

That's what I thought. No problem.

> The Stored Procedures and Functions, I Already Checked One by One, and Made changes to them

Did you get rid of any inefficient cursors? Change the transactions? 100 transactions of 1 UPDATE each (1-row each) is a lot slower than 1 transaction of 100 1-row UPDATEs, which in turn is a lot slower than 1 transaction with 1 UPDATE doing 100 rows. (There are caveats to that generalization.)