| View Issue Details [ Jump to Notes ] | [ Print ] | ||||||||
| ID | Project | Category | View Status | Date Submitted | Last Update | ||||
| 0008160 | CDash | public | 2008-11-24 21:43 | 2008-11-29 14:46 | |||||
| Reporter | Emmanuel Christophe | ||||||||
| Assigned To | Julien Jomier | ||||||||
| Priority | normal | Severity | major | Reproducibility | always | ||||
| Status | closed | Resolution | fixed | ||||||
| Platform | OS | OS Version | |||||||
| Product Version | 1.2 | ||||||||
| Target Version | Fixed in Version | 1.3 | |||||||
| Summary | 0008160: Performance issue when submiting test | ||||||||
| Description | When the test database is reaching a high volume, the processing for the xml test file by cdash is around 7 min. This result in a time out for the submission process with ctest: "Error message was: Empty reply from server" And following files uploading (update, coverage, etc) is aborded. This is coming from the mysql requests. I noticed a large iowait during the submission. The mysql log also report slow queries: # Time: 081124 10:31:33 # User@Host: cdashuser[cdashuser] @ localhost [] # Query_time: 15 Lock_time: 0 Rows_sent: 0 Rows_examined: 11696 SELECT id FROM image WHERE checksum = '3404840672'; for example. phpmyadmin interface also signal some issues with: Slow_queries Innodb_buffer_pool_reads Handler_read_rnd Handler_read_rnd_next We are using a mysql database with about - 200000 records in build2test, - 12000 in image, - 130000 in test, - 25000 in test2image and - 38000 in testmeasurement. CDash version is 1.2.1 | ||||||||
| Tags | No tags attached. | ||||||||
| Attached Files | |||||||||
| Relationships | |
| Relationships |
| Notes | |
|
(0014174) Julien Jomier (manager) 2008-11-25 09:06 |
You probably need to tune the configuration of your MySQL server. We are running CDash with the current following entries: - 49,670,254 records in build2test, - 22,347 in image, - 12,631,218 in test, - 512,200 in test2image and - 22,068,809 in testmeasurement. What type of storage are you using for MySQL: InnoDB or MyISAM? |
|
(0014188) Emmanuel Christophe (reporter) 2008-11-25 18:33 |
Using MyISAM |
|
(0014189) Julien Jomier (manager) 2008-11-25 18:40 |
Try the tuning-primer.sh script at http://www.day32.com/MySQL/, [^] we found it really useful to tune MySQL server. let me know how it goes. |
|
(0014193) Emmanuel Christophe (reporter) 2008-11-26 01:30 |
Tried the tuning-primer.sh scrip and changed a few parameters according to its recommendations but without success. Tried also the myisamchk -r. After uploading the test file, mysql is using only 10-15% cpu, the rest is on iowait. According to iostat, the read rate on the disk is above 50MB/s continuously during this period. Many slow query appears as: # Time: 081126 7:25:09 # User@Host: cdashuser[cdashuser] @ localhost [] # Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 13457 SELECT id FROM image WHERE checksum = '3637848088'; # Time: 081126 7:25:27 # User@Host: cdashuser[cdashuser] @ localhost [] # Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 13458 SELECT id FROM image WHERE checksum = '2922541316'; About 20 sec each. The image table has about 13500 records and is around 1GB. From what I understand (but I'm far from being a mysql expert), temporary table containing blob can't be kept on memory. Could it be coming from that? What would be the mysql parameter to adjust to avoid this problem? |
|
(0014197) Julien Jomier (manager) 2008-11-26 10:11 |
I'm wondering if the table is indexed correctly on the checksum key, could you check that and maybe force a reindexing of the table? We are not dealing with blob (that's why we have the checksum) so it shouldn't be a problem. |
|
(0014202) Emmanuel Christophe (reporter) 2008-11-26 18:25 |
You may have to guide me a bit for that as I'm quite new to mysql. What I've got so far is this information Keyname Type Cardinality Field id INDEX 14146 id checksum INDEX 328 checksum Seem like the checksum is an index but not the only one. |
|
(0014203) Julien Jomier (manager) 2008-11-26 19:01 |
Sorry about that. So it seems that the checksum cardinality is wrong. You shouldn't have two images with the same checksum. Could you tell me if this is the case? |
|
(0014204) Emmanuel Christophe (reporter) 2008-11-26 19:54 |
Yes, I confirm that. For example, the first three row are: id img extension checksum 5485 [BLOB - 57.2 KiB] image/png 2147483647 133 [BLOB - 14.6 KiB] image/png 2147483647 134 [BLOB - 14.6 KiB] image/png 2147483647 |
|
(0014205) Julien Jomier (manager) 2008-11-26 20:02 |
Something is wrong. The checksum should be totally different if the blob are different and there shouldn't be two identical checksums. I'll take a look at this when I get a chance. |
|
(0014206) Emmanuel Christophe (reporter) 2008-11-26 20:13 |
Is the size to store the checksum enough? |
|
(0014207) Julien Jomier (manager) 2008-11-26 20:16 |
good point, are you on a 64bits server? |
|
(0014208) Emmanuel Christophe (reporter) 2008-11-26 20:22 |
Yes, 64bit serveur and 64 bit OS. The curious stuff is that the checksum above is exactly 2^31-1... |
|
(0014209) Julien Jomier (manager) 2008-11-26 20:33 |
That's probably the problem, could you try to change to 'big int' for the checksum. Best is to install the phpmyadmin package and do it from there. Otherwise let me know I'll send you the SQL query. |
|
(0014210) Emmanuel Christophe (reporter) 2008-11-26 20:45 |
Is bigint(11) enough, do I have to change the 11? How to clean up the table now to make the checksum unique again? |
|
(0014212) Julien Jomier (manager) 2008-11-27 08:29 |
bigint(20) should be ok. I need to write a script for you to fix this problem. You are using CDash 1.2.1, is that correct? |
|
(0014213) Emmanuel Christophe (reporter) 2008-11-27 08:37 |
Correct: CDash 1.2.1 |
|
(0014232) Julien Jomier (manager) 2008-11-29 14:46 |
The problem was the size of crc32() on 64bits machine was too big to be stored in the database. This is now fixed. Thanks for the report. |
| Notes |
| Issue History | |||
| Date Modified | Username | Field | Change |
| 2008-11-24 21:43 | Emmanuel Christophe | New Issue | |
| 2008-11-25 09:06 | Julien Jomier | Note Added: 0014174 | |
| 2008-11-25 18:33 | Emmanuel Christophe | Note Added: 0014188 | |
| 2008-11-25 18:40 | Julien Jomier | Note Added: 0014189 | |
| 2008-11-26 01:30 | Emmanuel Christophe | Note Added: 0014193 | |
| 2008-11-26 10:11 | Julien Jomier | Note Added: 0014197 | |
| 2008-11-26 18:25 | Emmanuel Christophe | Note Added: 0014202 | |
| 2008-11-26 19:00 | Julien Jomier | Status | new => assigned |
| 2008-11-26 19:00 | Julien Jomier | Assigned To | => Julien Jomier |
| 2008-11-26 19:01 | Julien Jomier | Note Added: 0014203 | |
| 2008-11-26 19:54 | Emmanuel Christophe | Note Added: 0014204 | |
| 2008-11-26 20:02 | Julien Jomier | Note Added: 0014205 | |
| 2008-11-26 20:13 | Emmanuel Christophe | Note Added: 0014206 | |
| 2008-11-26 20:16 | Julien Jomier | Note Added: 0014207 | |
| 2008-11-26 20:22 | Emmanuel Christophe | Note Added: 0014208 | |
| 2008-11-26 20:33 | Julien Jomier | Note Added: 0014209 | |
| 2008-11-26 20:45 | Emmanuel Christophe | Note Added: 0014210 | |
| 2008-11-27 08:29 | Julien Jomier | Note Added: 0014212 | |
| 2008-11-27 08:37 | Emmanuel Christophe | Note Added: 0014213 | |
| 2008-11-29 14:46 | Julien Jomier | Note Added: 0014232 | |
| 2008-11-29 14:46 | Julien Jomier | Status | assigned => closed |
| 2008-11-29 14:46 | Julien Jomier | Resolution | open => fixed |
| 2008-11-29 14:46 | Julien Jomier | Fixed in Version | => 1.3 |
| Issue History |
| Copyright © 2000 - 2018 MantisBT Team |