0

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues.

i testing it of on a developer server ( 2 cores, 4gb ram ) but i still got issues problems, here is my stored procedure i have typed.

DROP PROCEDURE IF EXISTS insertDistributorProduct;

DELIMITER $$

CREATE PROCEDURE insertDistributorProduct(
    distributorUUID VARCHAR(36),
    distributorTitle VARCHAR(256),
    distributorDescription TEXT,
    distributorSKU VARCHAR(128),
    manufacturer VARCHAR(64),
    manufacturerSKU VARCHAR(128),
    productEan BIGINT(20),
    productCostPrice INT(11),
    productStock INT(11),
    productStockExpected DATE
)
BEGIN
    DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL;
    DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL;

    SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m
            WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 );

    -- If manufacturer not exists insert it
    IF ( _manufacturerUUID IS NULL ) THEN

        SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma
                WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 );

        -- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert
        IF ( _manufacturerUUID IS NULL ) THEN

            SET _manufacturerUUID = UUID();

            INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added)
            VALUES(_manufacturerUUID,manufacturer,NOW());

        END IF;

    END IF;

    SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp
            WHERE dp.distributorUUID = distributorUUID
              AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) );

    -- Update distributorProduct if its all ready found
    IF ( _distributorProductUUID IS NOT NULL ) THEN

        UPDATE 
            distributorProduct dp

        SET 
            dp.distributorTitle = distributorTitle,
            dp.distributorDescription = distributorDescription,
            dp.manufacturerUUID = _manufacturerUUID,
            dp.manufacturerSKU = manufacturerSKU,
            dp.productEan = productEan,
            dp.productCostPrice = productCostPrice,
            dp.productStock = productStock,
            dp.productStockExpected = productStockExpected,
            dp.updated = NOW(),
            dp.activated = 1

        WHERE
            dp.distributorProductUUID = _distributorProductUUID

        LIMIT
            1;

    -- Insert new product to distributorProduct if not exists.
    ELSE

        INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added)
        VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW());

    END IF;

END
$$

My problem is that every time I get over 50-100k rows, this script takes longer and longer time, and I have to try to look up my index but still nothing happens.

how can I debug and perfume my database more? every day i need to run this producer like 1-1,4milion times between 4-5 hours max, its update all our products but right now it's useless.

The first time 60k rows takes 15min, and after that 60k more ( total 120k ) this time its taken like 30-45min to run, and I still don't know why it happened. What did I do wrong?

Everything I tested here is running in virtual box software, I don't have to test this on real hardware.

  • 1
    Have you tried committing every ~5k records? – Vérace Mar 26 '15 at 20:49
  • What you mean when you say committing every 5k records? like transaction begin ...... commit ? – ParisNakitaKejser Mar 26 '15 at 20:52
  • 1
    The procedure seems to insert 2 rows only, 1 in each table. Do you run it 100k times? – ypercubeᵀᴹ Mar 26 '15 at 21:27
  • this procedure will be running like 1-1,4 million times every day so after 50-100k times its make perfomes issues :/ – ParisNakitaKejser Mar 26 '15 at 22:11
  • 1
    i found the issue, its because VirtualBox not have the perfume, i have normal hardware right now ( 1x Xeon dual-core HT ( 4 cores ), 8gb ddr2, 1x 1tb HHD ( no SSD ) and the same setup perfume like 53% faster then VirtualBox do wit 6 cores, 8gb ram and flash-drive(ssd) thanks a lot to all helping! – ParisNakitaKejser Mar 27 '15 at 13:07
0

This is what happened:

UUID VARCHAR(36)

UUIDs (GUIDs, MD5s, etc) are very random. That is, if you can't keep the entire table (or index) cached in RAM, the 'next' lookup is likely to cause a disk hit. And disk hits are a big factor (think 90%) of the cost of performing queries.

More discussion here. That includes a few partial remedies.

More importantly

WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer)

has terrible performance because it hides an indexable column inside a function (LOWER()). Instead, make sure those two columns have a collation ending with _ci (as in "Case Insensitive"). Then simply do

WHERE ma.aliasTitle = manufacturer

and have

INDEX(aliasTitle)
INDEX(manufacturerTitle)
INDEX(distributorSKU)

If you run into some index limit size, please provide MySQL version and SHOW CREATE TABLE. Meanwhile, distributorTitle VARCHAR(256) is likely to cause trouble. Changing to (255) may work; (191) may be needed for 5.5 or 5.6; beware of truncating strings.

One more thing -- When you CREATE the procedure, be sure to have established the collation for the connection that is consistent with the columns. For example, if you are using CHARACTER SET utf8 COLLATE utf8_general_ci for the columns, then you could do it this way:

SET NAMES utf8 COLLATE utf8_general_ci;
DELIMITER //
CREATE ...
//
DELIMITER ;
  • That is a theoretical issue - but with 100k rows it should be a total non issue unless you run mysql on a 10 year old smartphone or something like that. The amount of data is waaaay too small to not just allow caching everything in memory, particularly with the times involved. – TomTom Jan 27 at 8:59
  • Thanks @TomTom; that prompted me to comment on the real problems: don't hide columns in functions; add indexes; establish collation for proc. – Rick James Jan 27 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.