MySQL Bugs: #58211: Server not responding for a long time during heavy load

Bug #58211	Server not responding for a long time during heavy load
Submitted:	15 Nov 2010 20:14	Modified:	26 Mar 2012 17:39
Reporter:	Louis Breda van	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.5.7	OS:	Windows (WINDOWS7 64)
Assigned to:		CPU Architecture:	Any
Tags:	multi, performance, SSD, thread, timeout

Description:
Hello,

In order to get more performance out of my vb.net application <> mysql server combination. I decided to rewrite part of the application in a multi threaded way. The MySQL database is heavely accessed by the worker threads primairely storing data.

I noticed two things:
1) after some time the vb.net application times out on the temporairely not reacting server. Where at the same moment also the MySQL query browser is not reacting for many seconds. Note that the server is not dead. It reacts normally after lets say 15 seconds
2) For some unkown reason the combination of vb.net and mysql is only using lets say 40% of the cpu capacity despite the (near) absence of natural waiting points.

I regard especially the first point as very sevire if not critical and think(!)  it is related to the server. I can NOT be sure, it could also be related to windows, vb.net or the controllers within the used SSD's.

Sincerely,

Louis    

How to repeat:
From the principle thee application looks like this:

Start
- start thread1 to proces data type1
- start threat2 tp proces data type2

for each of those threads
- threadx start worker threats
- main read data object from file
- worker stores info in table 1
- worker stores info in table 2
- main waits for workers
- main reads next

After some time and processing a couple of 100.000 perhaps mln records
one of the worker treads stops on an dataadapter.update because of a timeout

 
For information:
- windows7 64 bit, quad core CPU, 8 GB, MySQL on Vertex2 SSD, innodb data dir on Vertex2 SSD Raid0 array whithin a truecrypt container
- server 557 64 bit, .net connector 635, odbc connector 5.1.8
- most of the storage is done via the .net connector which is faster. I only noticed the problem when using that connector (impression is, that the server is causing the problem)
- it takes some time, but the problem is can be reproduced, is occuring every couple of hours (about 2)
- tables involved to have some indexes 
- noticed performance is not in line with the incrediable fast access times of the used SSDs
- file source directory is on the windows ssd partion (very short access times as well).

Please, send your mysql.ini file content and 2 sets of the results of SHOW GLOBAL STATUS under heavy load taken with 1 minute interval.

my.ini as used at during the test

Attachment: Louis_my.ini (application/octet-stream, text), 9.44 KiB.

Valeriy,

I did start the application in order to provide you with the requested statistics (attached to this report just as the actual my.ini used)

Note that:
- I did started the appication just before the point wher I know that the problem mostly occurs
- I only started one of the two main threads. Since I know that is engough to reproduce the problem
- within the stats file you will find. A statslist just after starting the application, one stats just before a hanger occured.
- two times a two minute cyclys as requested

Sincerely,

Louis

Hello,

I did more testing especially related to the verdict that the VERTEX2 RAID0 SSD-pair was not responding for many(!) seconds.

I left the idea of a HW-raid0 and changed the AMD SB750 driver settings from RAID to AHCI. I reconfigered the SSD's to a WINDOWS7 stripe set with a 4K cluster size.

The idea behind this is:
- verdict that the raid0 was not working (driver + SSD-controller combo)
- raid drivers do not (jet) support trim
- there are rumours(!) that the very latest AMD-AHCI-drivers (> 10.9) do support trim (I can not verify!)
- the HW-driver smallest raid0-stripe size in fact too big in relation to the SSD short responce times
- it has to be a raid set-up because due to the caracter of the SSD's the data has to be "equaly" spread over both drives

What ever, where a HW-raid should have advantages over a SW-raid the opposite is true.

I did two big database runs and both wend OK, where the HW-raid allways timed out. Does not garantee it is completely OK now, but it is much better. Speed is also better (surely good, but not even close to the (SSD) advertised speed).

So, this solved a lot.

However .......

I it must be possible to gain another 100% performance increase. When running the multi threated application in combination with a SSD for source data and an SSD-pair as storage for the DB, there are no natural waiting points left. Processing should fly with 100% cpu-load .....

Not true, cpu load is on average < 50%. So somewhere .... windows, drivers, mysql-server, mysql-connection .... there are waiting points there should not be :>

Sincerely,

Louis

Please, check if with recent version, 5.5.19, situation is still the same.

Valeriy,

I would have loved to test something for mysql/oracle. But now over a year later it would be to much work. 

Appart from that the envirement has changed, so I am hardly able to test under comparable conditions. 

My computer system changend the programm changed and I have been using 5.6.3 for a long time now. And I have not be doing havy database processintg in the past year as well. 

So, I can not help you.

But perhaps you can help me .....
I (and others) have been complaining about ODBC bugs like chineese charactes for years, nothing happens, quite annoying 

Sincerely,

Louis

So I can't help

Would you mind to give specific bug numbers you care about? I see only one active ODBC-related bug reported by you: http://bugs.mysql.com/bug.php?id=35640

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Valeriy,

I spend some time checking if the chinese problem is still there. It is :(

What I did is the following:
- created an empty msaccess 2010 database
- created an ODBC-link to a couple of mysql databases I have on the server
- opend a lot of tables from the odbc-linked-tables in the msaccess gui
- lots of tables opend ok
- but there where also schemas where (all?) tables where showing the chinees charactes. Despite that the storage engine used and other table options looked the same (strange)

So, it came to my mind to test what would happen if I would recreate a chinese schema from a backup.

To do so I started the workbench and tryed to manage the server. 
No way!! I was facing an ancient WB bug related to WMI.

No Idea how to solve that problem yet!!
Please take a look at my comments addes in bug #59933

Sincerely,

Louis

Valeriy,

Good news.
- I checked about ten MySQL DB's / schemas on "chinese" characters using ODBC links to a test msaccess DB. 
- with the actual software (5.6.4M7 64 bit server, ODBC 5.1.9, msaccess 2010), the odbc links seems to work OK.
- the only acception (but that is no real fault) is when using a varbinairy, which is displayed as normal charactors in the ,ysql quiry browser (I still use the old good one :)), where msaccess query browser shows chinese (but I can live with that, it is more ore less correct as well)

- related to the WMI problem in the WorkBench, there is quite a different bug than the reported problem !!
- The Workbench is using / fetching older definitions from earlier WB versions, does not know how to handle them and is generating the strange error messages
- the solution is to open the workbench, remove all definitions e.g. server links and start again
- but doing so, I noticed that there is a deadlock, to access the server it has to be running, to start it you need to go to the server window, what is not possible if the server is not running

You know, in a lot of aspects I still prefer the old gui tools !!

Sincerely,

Louis

Louis,

Can you, please, create new bug report for every Workbench (and remaining server or ODBC, if any) problem mentioned in your previous comment? Recent complains are not related to the original report or bug synopsis, so I'd prefer to close it.

Valeriy,

It is OK. 
- the ODBC-problem seems to be gone
- the original problem is so long ago, that every thing has changed since then.  

Louis

Not repeatable with recent versions.