|
Notes |
|
|
(0014174)
|
|
Julien Jomier
|
|
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
|
|
2008-11-25 18:33
|
|
|
|
|
(0014189)
|
|
Julien Jomier
|
|
2008-11-25 18:40
|
|
|
|
|
(0014193)
|
|
Emmanuel Christophe
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
2008-11-26 20:13
|
|
|
Is the size to store the checksum enough? |
|
|
|
(0014207)
|
|
Julien Jomier
|
|
2008-11-26 20:16
|
|
|
good point, are you on a 64bits server? |
|
|
|
(0014208)
|
|
Emmanuel Christophe
|
|
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
|
|
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
|
|
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
|
|
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
|
|
2008-11-27 08:37
|
|
|
|
|
(0014232)
|
|
Julien Jomier
|
|
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. |
|