#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102

    Select * on a large table...


    Hello everyone,

    If in MySQL 5.0.51a a person does a remote select from one server to another....

    <here is the process>

    | 10955 | root | ttstcias2:52273 | Q2DataMart | Query | 1668 | Sending data
    | SELECT /*Q2DataMart.TradeBasisHoldings:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, LOWER(CONV(BIT_XOR(
    CAS |


    So if this process were running would if cause a LOCK on that table "TradeBasisHoldings" which is the largest in the DB and lots of others are trying to update, select, and deletes from it. This Select * process seems to have blocked all other processes from working.

    I just want to make sure it was the cause and how better to explain this to the "End Users that issued this Select *"

    Thoughts....?
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Correct!
    In databases you have two major kinds of locks, READ LOCK and WRITE LOCK.
    Where SELECTs create READ LOCKs while INSERT/UPDATE/DELETE create WRITE LOCKs.

    You can have several READ LOCKs at the same time since a read is non destructive and doesn't alter the data, eg several SELECTs running in parallel on the same data.

    But WRITE LOCKs must have _exclusive_ access to the data that it is going to alter, which means that it will have to wait until there is a moment when it can get exclusive access.

    That's the basics.

    But then we have to talk about the SCOPE of the locks.
    Because the two major database engines in MySQL: MyISAM and InnoDB behaves differently.
    MyISAM is the simplest and locks the entire table, so if you issue a heavy select the table will be locked with a READ LOCK and all INSERT/UPDATE/DELETE will just have to wait in line until the READ LOCK is released.
    If you have a server under heavy load with long running selects combined with insert/updates/deletes against the same table then this can pretty quickly become a problem as you describe.

    With InnoDB you have quite an improvement with the addition of MVCC and row level locking. So you can both SELECT things while another thread is updating the table and you don't have to have exclusive access to the whole table only the records that you are about to modify.

    So basically if you are running MyISAM you will definitely have problems.
    But if you are running InnoDB you can get much more parallelism.

    Although you can still often get an issue with that a heavy query against a large table more or less makes the entire server so busy (high cpu, high disk IO, throwing out data from caches to be able to read in the new etc ) that everything else seems to be on hold anyway.

    So to make a long post short, yes a heavy query can basically feel like it takes over the entire server.
    But then the other question is if the customer maybe needs the output from that query and needs to execute it.
    Or if that query in itself actually could be optimized.
    /Stefan
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Here is the table which has many records and is constantly being used.....select, update, deletes as this Select * was being done.

    Create Table: CREATE TABLE `TradeBasisHoldings` (
    `EvaluationDate` date NOT NULL,
    `PositionID` bigint(20) NOT NULL,
    `PortfolioId` tinyint(4) NOT NULL,
    `BookValueLocal` decimal(14,2) default NULL,
    `BookValueBase` decimal(14,2) default NULL,
    `MarketValueLocal` decimal(14,2) default NULL,
    `MarketValueBase` decimal(14,2) default NULL,
    `OriginalFaceQuantity` decimal(14,2) default NULL,
    `QuantityHeld` decimal(14,2) default NULL,
    `QuantityEncumbered` decimal(14,2) default NULL,
    `QuantityOnLoan` decimal(14,2) default NULL,
    `LoanBasisValue` decimal(14,2) default NULL,
    `MarketPriceLocal` decimal(12,6) default NULL,
    `MarketPriceBase` decimal(12,6) default NULL,
    `PriceDate` date default NULL,
    `PriceOrigin` varchar(8) default NULL,
    `PricingVendor` varchar(4) default NULL,
    `ExchangeRate` decimal(15,9) default NULL,
    `InterestRateCurrent` decimal(14,2) default NULL,
    `NextMaturityDate` date default NULL,
    `OriginalCostLocal` decimal(14,2) default NULL,
    `OriginalCostBase` decimal(14,2) default NULL,
    `AverageFXOriginalCost` decimal(14,9) default NULL,
    `AverageFXBookBasis` decimal(14,9) default NULL,
    `AverageLoanFeeAccrualRate` decimal(12,9) default NULL,
    `TaxLotCostingBasis` varchar(8) default NULL,
    `TaxLotSaleCount` int(11) default NULL,
    `YieldToMaturity` decimal(7,4) default NULL,
    `SPQualityRating` varchar(4) default NULL,
    `SPRatingDate` date default NULL,
    `MoodyQualityRating` varchar(4) default NULL,
    `MoodyRatingDate` date default NULL,
    `TermToMaturity` decimal(8,5) default NULL,
    `MaculayDuration` decimal(8,5) default NULL,
    `ModifiedDuration` decimal(8,5) default NULL,
    `EffectiveDuration` decimal(8,5) default NULL,
    `DurationToNextCall` decimal(8,5) default NULL,
    `EffectiveConvexity` decimal(8,5) default NULL,
    `Convexity` decimal(8,2) default NULL,
    `PriceNormalizationFactor` decimal(6,2) default NULL,
    `PendingReceivablesLocal` decimal(14,2) default NULL,
    `PendingPayablesLocal` decimal(14,2) default NULL,
    `PendingSettlementFlag` tinyint(1) default NULL,
    `IntransitPurInterestLocal` decimal(14,2) default NULL,
    `IntransitSoldInterestLocal` decimal(14,2) default NULL,
    `UnrealizedGainLossLocal` decimal(14,2) default NULL,
    `UnrealizedGainLossBase` decimal(14,2) default NULL,
    `UnrealizedSTGainLossLocal` decimal(14,2) default NULL,
    `UnrealizedSTGainLossBase` decimal(14,2) default NULL,
    `UnrealizedLTGainLossLocal` decimal(14,2) default NULL,
    `UnrealizedLTGainLossBase` decimal(14,2) default NULL,
    `CashBalanceIncomePortion` decimal(14,2) default NULL,
    `CashBalanceCorpusPortion` decimal(14,2) default NULL,
    `AccruedInterstLocal` decimal(14,2) default NULL,
    `AccruedInterestBase` decimal(14,2) default NULL,
    `CurrentFactor` decimal(11,9) default NULL,
    `CurrentIndex` decimal(11,9) default NULL,
    `BookValueLocalSetl` decimal(14,2) default NULL,
    `BookValueBaseSetl` decimal(14,2) default NULL,
    `MarketValueLocalSetl` decimal(14,2) default NULL,
    `MarketValueBaseSetl` decimal(14,2) default NULL,
    `OriginalFaceQuantitySetl` decimal(14,2) default NULL,
    `QuantityHeldSetl` decimal(14,2) default NULL,
    `QuantityEncumberedSetl` decimal(14,2) default NULL,
    `OriginalCostLocalSetl` decimal(14,2) default NULL,
    `OriginalCostBaseSetl` decimal(14,2) default NULL,
    `AverageFXOriginalCostSetl` decimal(15,9) default NULL,
    `AverageFXBookBasisSetl` decimal(15,9) default NULL,
    `YieldToMaturitySetl` decimal(15,9) default NULL,
    `LastUpdatedTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY (`EvaluationDate`,`PositionID`,`PortfolioId`),
    KEY `TradeBasisHoldings_FKIndex1` (`PositionID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1


    I am guessing because it is such a HUGE table and Select * gets it all, that there would be locks on the whole table causing LOCKS.
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    You are using InnoDB which is a good thing.

    Now:
    1. Can you show the actual query?
    And the output from EXPLAIN (the actual query).

    2. Can you say if you have high CPU or high IO when the query is executed.

    My guess is that you have high IO which causes the questions:
    3. How big is your table in MB
    4. How much RAM you got on your machine
    5. What have you configured innodb_buffer_pool_size to? (check with SHOW VARIABLES LIKE 'innodb%')
    /Stefan
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    You are using InnoDB which is a good thing.

    Now:
    1. Can you show the actual query?
    And the output from executing EXPLAIN (the actual query here).

    2. Can you say if you have high CPU or high IO when the query is executed.

    My guess is that you have high IO which causes the questions:
    3. How big is your table in MB
    4. How much RAM you got on your machine
    5. What have you configured innodb_buffer_pool_size to? (check with SHOW VARIABLES LIKE 'innodb%')
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo