총 잠금 수가 잠금 테이블 크기를 초과합니다.
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;
컨피규레이션파일을 변경하여 버퍼 풀 크기를 늘리는 것은 도움이 된다고 읽었습니다만, 아무것도 도움이 되지 않습니다.일시적인 회피책 또는 영구적인 해결책 중 하나를 사용하여 이 문제를 해결하려면 어떻게 해야 합니다.
EDIT: 쿼리의 일부가 변경되었습니다.영향을 주진 않겠지만 다 찾아보고 교체했는데 그게 다 망친 줄 몰랐어요질문에는 영향을 주지 않습니다.
EDIT 2: t1에 typedesc가 추가되었습니다.조회에서는 변경했는데 여기는 아닙니다.
변수 MySQL 값을 할 수 .innodb_buffer_pool_size의 .innodb_buffer_pool_size 되다8,388,608.
을 변경합니다.innodb_buffer_pool_size아래 세트를 참조해 주세요.
- 을 찾습니다.
my.cnf서버로부터 액세스 합니다. Linux에서 ./etc/my.cnf - " " " 을 합니다.
innodb_buffer_pool_size=64MB - MySQL 서버 재시작
MySQL 서버를 재시작하려면 다음 두 가지 옵션 중 하나를 사용할 수 있습니다.
- 서비스 mysqld 재시작
- /etc/init.d/mysqld 재시작
참조: 총 잠금 수가 잠금 테이블 크기를 초과합니다.
Table Lock을 사용하여 해결할 다른 방법을 찾았습니다.물론 동시에 테이블을 업데이트해야 하는 경우 애플리케이션에 적합하지 않을 수 있습니다.
「 Using 」(사용해 주세요) : 「Try Using 」(사용해 주세요)LOCK TABLESInnoDB MVCC입니다.내가 틀리지 않았다면, "잠금 테이블"은 MVCC 구현을 위한 행과 버전 식별자를 저장하는 InnoDB 내부 구조를 의미하며, 행이 변경되고 있음을 나타내는 비트가 스테이트먼트에서 수정되고 있으며, 6000만 행의 테이블이 할당되어 있는 메모리를 초과할 수 있습니다.LOCK TABLES명령어는 행 수준이 아닌 테이블 수준의 잠금을 설정하여 이 문제를 완화해야 합니다.
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 Inc., 북미 지역 커뮤니티 관계 관리자 Jay Pips씨
MySQL 문서(이미 읽은 내용)에서 다음을 수행합니다.
1206 (ER_)잠금_탭LE_FULL)
총 잠금 수가 잠금 테이블 크기를 초과합니다.이 오류를 방지하려면 innodb_buffer_pool_size 값을 늘립니다.개개의 애플리케이션내에서, 큰 조작을 작은 조각으로 분할하는 것을 회피할 수 있습니다.예를 들어, 큰 INSERT 에러가 발생했을 경우는, 몇개의 작은 INSERT 조작을 실행합니다.
innodb_buffer_pool_size를 늘려도 도움이 되지 않는 경우 굵은 글씨로 표시된 부분에 따라 INSERT를 3으로 분할합니다.UNION을 건너뛰고 3개의 INSERT를 만듭니다.각각 JOIN이 맨 위에 있습니다.3번 통과 테이블.
명령어 sql 을 할 수 .show global variables like 'innodb_buffer%';버퍼 크기를 확인합니다.
의 ★★★★★★★★★★★★★★★★★★★★★★★★★★★my.cnf andadd, file 및 add, file 및 add.
[mysqld]
innodb_buffer_pool_size=1G # depends on your data and machine잊지 말고 추가해 주세요[mysqld]그렇지 않으면 작동하지 않습니다.
제 경우 우분투 16.04는my.cnf./etc/mysql/.
MySQL windows MySQL my my my my 。[ ]> [ Server ] Server ]> [ Server status ])라고됩니다.길C:\ProgramData\MySQL\...\my.ini)
다음 "에서 control "my.ini" control+를 찾습니다.에프 에프 에프 에프 에프 에프 에프 에프 에프 에프 에프 에프 에프.buffer_pool_sizemb6464 6464 64 、 64 MB （ 8 MB ） 。
[ Instance ]> [ Startup / Shutdown ]> [ Stop server ]순서로 이동하여 서버를 재시작합니다(나중에 서버를 다시 부팅합니다).
제 경우 테이블에서 엔트리를 삭제할 수 없었습니다.
오류 코드 1206 수정: 잠금 수가 잠금 테이블 크기를 초과합니다.
제 경우 WampServer 2.5가 설치된 Windows에서 실행되는 MySQL Workbench(5.6.17)를 사용하고 있습니다.
Windows/WampServer의 경우 my.ini 파일(my.cnf 파일이 아님)을 편집해야 합니다.
이 파일을 찾으려면 메뉴 서버/서버 상태(MySQL Workbench)로 이동하여 서버 디렉터리/기본 디렉터리 아래를 확인하십시오.
my.ini 파일에는 다양한 설정에 대한 섹션이 정의되어 있습니다.섹션 [mysqld](존재하지 않는 경우 생성)를 찾아 innodb_disc_pool_size=4 명령을 추가합니다.G
[mysqld]
4innodb_module_pool_size=4Gg
buffer_pool 파일의 크기는 사용하시는 머신에 따라 다르며, 대부분의 경우 2G 또는 4G로 문제가 해결됩니다.
서버를 재기동해, 새로운 설정을 실시할 수 있도록 해 주세요.이것에 의해, 문제가 해결되었습니다.
도움이 됐으면 좋겠다!
sql 스크립트 실행 중 MYSQL에서 발생한 것과 동일한 문제. 아래 이미지를 봐주세요.에러 코드 1206: 잠금 수가 잠금 테이블 크기를 초과합니다. 그림
은 Mysql 에, 「Mysql」에서 했습니다.my.ini시스템에서 작동하여 문제가 해결되었습니다.
부분은 될 것 요.my.ini다음 경로에서 사용할 수 있습니다.- C:\ProgramData\MySQL\MySQL Server 5.7\my.ini.my.iniconfig "config" 필드:-
key_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=128M
innodb_log_buffer_size=10M
innodb_buffer_pool_size=256M
query_cache_type=2
max_allowed_packet=16M
위의 모든 변경 후 MYSQL 서비스를 재시작하십시오.이미지를 참조해 주세요.- Microsoft MYSQL Service Picture (Microsoft MYSQL 서비스 그림)
각 테이블이 비교적 고유한 값을 가지도록 적절하게 구성되어 있는 경우, 각 삽입에 대해 join-filter를 배치한 상태에서 3개의 insert-into 문(테이블마다 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
다른 2개의 테이블에 대해서도 이 절차를 반복합니다.복사/붙여넣기는 적절한 방법입니다.FROM 테이블명을 변경하기만 하면 됩니다.** SkusBought 테이블의 엔트리가 중복되지 않도록 하려면 WHERE 절 앞에 각 섹션에 다음 Join 코드를 추가할 수 있습니다.
LEFT OUTER JOIN SkusBought AS T3
ON t1.customer = t3.customer
AND t1.sku = t3.sku
-그리고 WHERE 절의 마지막 줄-
AND t3.customer IS NULL
초기 코드는 다수의 서브쿼리를 사용하고 있으며 UNION 스테이트먼트는 결과를 필터링하기 위해 다른 서브쿼리를 실행하고 테이블에 삽입하기 전에 먼저 3개의 개별 소스로부터의 데이터를 입력하기 위한 자체 임시 테이블을 생성하기 때문에 비용이 많이 들 수 있습니다.
mysql 워크벤치가 있는 경우.서버 상태로 이동합니다.내 경우 실행 중인 서버 파일의 위치를 찾습니다.
C:\ProgramData\MySQL\MySQL Server 5.7
my.ini 파일을 열고 buffer_pool_size를 찾습니다.값을 높게 설정합니다. 기본값은 8M입니다.이것이 내가 이 문제를 해결한 방법이다.
이 설정에 사용되는 수치는 바이트 단위라고 말할 필요가 있습니다.그것은 어려운 방법으로 알 수 있습니다.
아래 답변은 OP의 질문에 직접 답변하는 것이 아닙니다.그러나 이 페이지는 구글에서 "잠금 총 수가 잠금 테이블 크기를 초과했습니다"를 검색했을 때 나타나는 첫 번째 결과이기 때문에 여기에 답변을 추가합니다.
실행 중인 쿼리가 수백만 행에 걸친 테이블 전체를 해석하는 경우 설정에서 제한을 변경하는 대신 while loop을 시도할 수 있습니다.
잠시 동안 보면 산산조각 날 거야.다음으로 인덱스된 컬럼(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" 열 위로 루프하도록 조정할 수도 있습니다.
이게 누군가에게 도움이 되길 바라!
안녕하세요.
MySQL 테이블에서 수백만 행을 제거하려고 할 때 동일한 오류가 발생했습니다.
저의 해상도는 MySQL 구성 파일 변경과는 무관하지만 트랜잭션당 최대 id를 지정함으로써 대상 행 수를 줄이기 위한 것이었습니다.1개의 트랜잭션으로 모든 행을 대상으로 하는 것이 아니라 트랜잭션당 부분을 대상으로 하는 것이 좋습니다.작업을 완료하려면 더 많은 트랜잭션이 필요할 수 있지만 최소한 MySQL 구성을 만지작거리지 말고 다른 곳에서 작업을 수행할 수 있습니다.
예제:
delete from myTable where groupKey in ('any1', 'any2') and id < 400000;
가 아니라
delete from myTable where groupKey in ('any1', 'any2');
쿼리는 groupBy 및 orderBy 절을 사용하여 계속 최적화될 수 있습니다.
제가 '만행'을 사용해서 때도 .데이터베이스에 몇 백만 개의 행을 삽입할 때python은 이 더하여 더 작은 덩어리로 줄이는 memory usageexecutemany하나의 커밋을 실행하는 대신 각 척을 삽입한 후 동시에 커밋하는 기능을 수행합니다.
def insert(query, items, conn):
GROUPS = 10
total = items.shape[0]
group = total // GROUPS
items = list(items.itertuples(name=None, index=None))
for i in range(GROUPS):
cursor.executemany(query, items[group * i : group * (i + 1)])
conn.commit()
print('#', end='')
print()
또한 위의 구현에는 깔끔한 진행 표시줄이 있습니다.
언급URL : https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size
'programing' 카테고리의 다른 글
| SQL 데이터베이스 테이블의 n번째 행을 선택하려면 어떻게 해야 합니다. (0) | 2022.10.01 |
|---|---|
| SecurityError: 오리진이 있는 프레임이 교차 오리진 프레임에 액세스하는 것을 차단했습니다. (0) | 2022.10.01 |
| 와 Instant를 사용한 JPA 쿼리가 작동하지 않음 (0) | 2022.09.22 |
| 코드 변경 후 magento 셋업 업그레이드를 실행할 때 Mysql 서버가 사라짐 (0) | 2022.09.22 |
| strict type은 PHP에서 무엇을 합니까? (0) | 2022.09.22 |