복붙노트

[SQL] 잠금 장치의 총 수는 로크 테이블 크기를 초과

SQL

잠금 장치의 총 수는 로크 테이블 크기를 초과

나는 MySQL의에서 보고서를 실행 해요. 쿼리 중 하나는 임시 테이블에 행의 많은 양을 삽입하는 것을 포함한다. 나는 그것을 실행하려고하면이 오류를 얻을 :

오류 코드 1206 : 잠금의 수는 잠금 테이블의 크기를 초과합니다.

문제의 쿼리는 다음과 같습니다

create temporary table SkusBought(
customerNum int(11),
sku int(11),
typedesc char(25),
key `customerNum` (customerNum)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into skusBought
select t1.* from
    (select customer, sku, typedesc from transactiondatatransit
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondatadelaware
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondataprestige
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku) t1
join
(select customernum from topThreetransit group by customernum) t2
on t1.customer = t2.customernum;

나는 구성 파일을 변경하면 버퍼 풀 크기의 의지 도움말을 증가 읽었습니다,하지만 아무것도하지 않습니다. 무슨 일이 임시 해결 방법이나 영구적 인 수정으로 하나,이 문제를 해결하는 방법이있을까요?

편집 : 쿼리의 변경된 부분. 그것은 영향을 미치지 만 나는 모든 찾기가-교체했고, 그것을 그렇게 망쳐 몰랐어요 안된다. 문제에 영향을주지 않습니다.

편집 2 : T1에 추가 typedesc. 나는하지만 여기에 쿼리를 변경했습니다.

해결법

  1. ==============================

    1.이 문제는 MySQL의 변수 innodb_buffer_pool_size에 대해 더 높은 값을 설정하여 해결할 수 있습니다. innodb_buffer_pool_size의 기본값은 8388608 될 것입니다.

    이 문제는 MySQL의 변수 innodb_buffer_pool_size에 대해 더 높은 값을 설정하여 해결할 수 있습니다. innodb_buffer_pool_size의 기본값은 8388608 될 것입니다.

    innodb_buffer_pool_size의 설정 값을 변경하려면 설정 아래를 참조하십시오.

    MySQL 서버를 다시 시작하려면 아래 두 옵션의 사람을 사용할 수 있습니다 :

    자물쇠의 갯수가 로크 테이블 크기를 초과하는 참고

  2. ==============================

    2.사용 테이블 잠금 - 나는 그것을 해결하는 또 다른 방법을 찾아 냈다. 물론, 그것은 당신의 응용 프로그램에 부적절 할 수 있습니다 - 당신은 같은 시간에 업데이트 테이블에 필요합니다.

    사용 테이블 잠금 - 나는 그것을 해결하는 또 다른 방법을 찾아 냈다. 물론, 그것은 당신의 응용 프로그램에 부적절 할 수 있습니다 - 당신은 같은 시간에 업데이트 테이블에 필요합니다.

    보다: 대신 이노의 MVCC 행 수준 잠금의 기본 동작의 전체 테이블을 잠금 LOCK 테이블을 사용하십시오. I 틀리지 않는 경우 "잠금 테이블"행을 식별하는 비트 성명 수정되고 함께 MVCC 구현을위한 이노 내부 구조 기억 로우 및 버전 식별자를 참조하여, 60 만 행의 테이블되고 아마에 할당 된 메모리를 초과합니다. 잠금 테이블 명령 대신 행 수준의 테이블 수준 잠금을 설정하여이 문제를 완화해야한다 :

    SET @@AUTOCOMMIT=0;
LOCK TABLES avgvol WRITE, volume READ;
INSERT INTO avgvol(date,vol)
SELECT date,avg(vol) FROM volume
GROUP BY date;
UNLOCK TABLES;

    제이 파이프, 지역 사회 관계 관리자, 북미, MySQL을 사

  3. ==============================

    3.(내가 볼 당신이 이미 읽은 것을) MySQL의 문서에서 :

    (내가 볼 당신이 이미 읽은 것을) MySQL의 문서에서 :

    innodb_buffer_pool_size 나던 도움을 증가하는 경우, 단지 각은 topThreetransit 테이블에 가입, 굵게 표시된 부분에 표시를 따라 3 건너 뛰기 노조로 INSERT를 분할 인서트 3을합니다.

  4. ==============================

    4.제대로 각이 다음을 수행하는 것이 작업을 수행하는 덜 집중적 인 방법을 상대적으로 고유 한 값을 포함하도록 테이블을 구성하는 경우 3 별도의 인서트에 각 삽입 장소에서 조인 필터와 문, 각 테이블에 1 -

    제대로 각이 다음을 수행하는 것이 작업을 수행하는 덜 집중적 인 방법을 상대적으로 고유 한 값을 포함하도록 테이블을 구성하는 경우 3 별도의 인서트에 각 삽입 장소에서 조인 필터와 문, 각 테이블에 1 -

    INSERT INTO SkusBought...

SELECT t1.customer, t1.SKU, t1.TypeDesc
FROM transactiondatatransit AS T1
LEFT OUTER JOIN topThreetransit AS T2
ON t1.customer = t2.customernum
WHERE T2.customernum IS NOT NULL

    다른 두 테이블이 반복 - 복사 / 붙여 넣기가 좋은 방법입니다, 단순히 테이블 이름에서 변경합니다. 당신은 당신이 다음 사항을 추가 할 수 있습니다 SkusBought 테이블에서 중복 항목을 방지하려는 경우 ** WHERE 절에 앞서 각 섹션에 코드를 가입 할 수 있습니다.

    LEFT OUTER JOIN SkusBought AS T3
ON  t1.customer = t3.customer
AND t1.sku = t3.sku

    - 그리고 다음의 마지막 줄 clause-

    AND t3.customer IS NULL

    귀하의 초기 코드는 서브 쿼리의 숫자를 사용하고, 그것은 먼저 다른 하위 실행과 함께 당신이 원하는 테이블에 삽입하기 전에 별도의 세 가지 소스에서 데이터를 채울 자신의 임시 테이블을 생성합니다 같이 UNION 문은 비용이 많이들 수 있습니다 필터 결과 쿼리.

  5. ==============================

    5.창에서 : 당신은 MySQL의 워크 벤치가있는 경우. 서버 상태로 이동합니다. 그것이 내 경우에는 서버 파일을 실행의 위치를 ​​찾을 수 있습니다 :

    창에서 : 당신은 MySQL의 워크 벤치가있는 경우. 서버 상태로 이동합니다. 그것이 내 경우에는 서버 파일을 실행의 위치를 ​​찾을 수 있습니다 :

    C:\ProgramData\MySQL\MySQL Server 5.7

    의 my.ini 파일을 열고 buffer_pool_size을 찾을 수 있습니다. 값을 높게 설정합니다. 기본값은 8M입니다. 이것은 내가이 문제를 해결하는 방법입니다

  6. ==============================

    6.나는 MySQL의 워크 벤치와 MySQL의 창을 실행하고 있습니다. 서버로 이동> 서버 상태 상단이 구성 파일은 말한다 : "경로"(C : \ 경우 ProgramData \ MySQL은 \ ... \의 my.ini)

    나는 MySQL의 워크 벤치와 MySQL의 창을 실행하고 있습니다. 서버로 이동> 서버 상태 상단이 구성 파일은 말한다 : "경로"(C : \ 경우 ProgramData \ MySQL은 \ ... \의 my.ini)

    그런 다음 파일에서 "의 my.ini"를 눌러 컨트롤 + F와 buffer_pool_size을 찾을 수 있습니다. 값을 높게 설정, 난 (기본값은 8메가바이트이다) 64MB의 추천 할 것입니다.

    인스턴스> 시작 / 종료> 중지 서버로 이동하여 sruver를 다시 시작합니다 (다음 나중에 다시 서버를 시작)

    내 경우에는 내가 내 테이블에서 항목을 삭제할 수 없습니다.

  7. ==============================

    7.먼저, SQL 명령 show 글로벌 변수를 사용할 수 있습니다 같은 'innodb_buffer %'; 버퍼 크기를 확인한다.

    먼저, SQL 명령 show 글로벌 변수를 사용할 수 있습니다 같은 'innodb_buffer %'; 버퍼 크기를 확인한다.

    솔루션이의 my.cnf 파일 및 추가 기능을 찾을 수있다,

    [mysqld를] innodb_buffer_pool_size = 1G #은 데이터와 시스템에 따라 달라집니다

    DO, 그렇지 않으면, [mysqld에]를 추가하는 것을 잊지 작동하지 않습니다.

    내 경우에는, 16.04 우분투, my.cnf 파일이있는 폴더 아래에 있습니다은 / etc / mysql을 /.

  8. ==============================

    8.어려운 방법 알아 것을 발견 - 그것은이 설정에 사용 된 그림은 바이트 것은 말할 가치가 있습니다!

    어려운 방법 알아 것을 발견 - 그것은이 설정에 사용 된 그림은 바이트 것은 말할 가치가 있습니다!

  9. ==============================

    9.이 답변은 아래 직접 영업 이익의 질문에 대답하지 않습니다. 하나, 이 페이지는 첫 번째 결과 때이기 때문에 여기이 답변을 추가 해요 당신이 구글 "잠금의 총 수는 잠금 테이블의 크기를 초과".

    이 답변은 아래 직접 영업 이익의 질문에 대답하지 않습니다. 하나, 이 페이지는 첫 번째 결과 때이기 때문에 여기이 답변을 추가 해요 당신이 구글 "잠금의 총 수는 잠금 테이블의 크기를 초과".

    실행중인 쿼리 행의 스팬 수백만, 당신은 구성의 한계를 변경하는 대신 while 루프를 시도 할 수있는 전체 테이블을 구문 분석됩니다.

    그동안 모양 조각으로 그것을 깰 것입니다. 다음은 DATETIME 인 인덱스 컬럼을 통해 반복 예입니다.

    # Drop
DROP TABLE IF EXISTS
new_table;

# Create (we will add keys later)
CREATE TABLE
new_table
(
    num INT(11),
    row_id VARCHAR(255),
    row_value VARCHAR(255),
    row_date DATETIME
);

# Change the delimimter
DELIMITER //

# Create procedure
CREATE PROCEDURE do_repeat(IN current_loop_date DATETIME)
BEGIN

    # Loops WEEK by WEEK until NOW(). Change WEEK to something shorter like DAY if you still get the lock errors like.
    WHILE current_loop_date <= NOW() DO

        # Do something
        INSERT INTO
            user_behavior_search_tagged_keyword_statistics_with_type
            (
                num,
                row_id,
                row_value,
                row_date
            )
        SELECT
            # Do something interesting here
            num,
            row_id,
            row_value,
            row_date
        FROM
            old_table
        WHERE
            row_date >= current_loop_date AND
            row_date < current_loop_date + INTERVAL 1 WEEK;

        # Increment
        SET current_loop_date = current_loop_date + INTERVAL 1 WEEK;

    END WHILE;

END//

# Run
CALL do_repeat('2017-01-01');

# Cleanup
DROP PROCEDURE IF EXISTS do_repeat//

# Change the delimimter back
DELIMITER ;

# Add keys
ALTER TABLE
    new_table
MODIFY COLUMN
    num int(11) NOT NULL,
ADD PRIMARY KEY
    (num),
ADD KEY
    row_id (row_id) USING BTREE,
ADD KEY
    row_date (row_date) USING BTREE;

    테이블이 날짜를 사용하지 않는 경우 당신은 또한 "NUM"컬럼을 통해 루프에 적용 할 수 있습니다.

    이 사람을 도움이되기를 바랍니다!

    10. from https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQLite는 TIMESTAMP 값을 비교  (0) 2020.04.26
[SQL] 어디 문에서 SQL을 사용하여 별칭  (0) 2020.04.26
[SQL] IN () 절에 SQL 매개 변수를 전달하면 .NET에서 데이터 집합을 입력하여  (0) 2020.04.26
[SQL] SQL 쿼리는 급여 테이블에서 N 번째 가장 높은 급여를 찾을 수  (0) 2020.04.26
[SQL] IF는 조건이 PLSQL 작동하지 EXISTS  (0) 2020.04.26
,

티스토리툴바