Как следить за пространством MySQL?
Я загрузил образ виртуальной машины веб-приложения, использующего MySQL.
Как я могу следить за потреблением места и знать, когда нужно добавить дополнительное пространство?
Ответы 6
Если доступен только MySQL, используйте команду SHOW TABLE STATUS и посмотрите на столбец Data_length для каждой таблицы, который указан в байтах.
Если у вас есть другие языки, доступные на машине, сценарий на любом из них, который запускается регулярно (cron), проверяет свободное место на диске или размер каталога базы данных и обновляет вас по электронной почте или иным образом. Существует слишком много вариантов, чтобы предложить конкретное решение - это зависит от вашей ситуации.
Для таблиц MyISAM я обычно проверяю размер каталога / var / lib / mysql / mydatabasename /. Таблицы InnoDB используют монолитные файлы, поэтому вы должны использовать SHOW TABLE STATUS.
У меня есть несколько отличных вопросов, которыми я хочу поделиться:
Запустите это, чтобы получить общее использование данных MySQL и индекса по подсистеме хранения.
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
Запустите это, чтобы получить общее количество данных MySQL и использование индекса по базе данных
SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);
Запустите это, чтобы получить общее использование данных MySQL и индекса по базам данных и механизмам хранения.
SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;
ПРЕДОСТЕРЕЖЕНИЕ
В каждом запросе вы увидите (SELECT 3 pw). Pw означает мощность 1024 для отображения результатов.
(SELECT 0 pw)отобразит отчет в байтах(SELECT 1 pw)отобразит отчет в килобайтах(SELECT 2 pw)отобразит отчет в мегабайтах(SELECT 3 pw)отобразит отчет в гигабайтах(SELECT 4 pw)отобразит отчет в терабайтах(SELECT 5 pw)отобразит отчет в PetaBytes (свяжитесь со мной, если вы запустите этот)
Вот запрос отчета с немного меньшим форматированием:
SELECT IFNULL(db,'Total') "Database",
datsum / power(1024,pw) "Data Size",
ndxsum / power(1024,pw) "Index Size",
totsum / power(1024,pw) "Total"
FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum
FROM (SELECT table_schema db,data_length dat,index_length ndx
FROM information_schema.tables WHERE engine IS NOT NULL
AND table_schema NOT IN ('information_schema','mysql')) AA
GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;
Поверьте, я сделал эти запросы более 4 лет назад и использую их до сих пор.
ОБНОВЛЕНИЕ 2013-06-24 15:53 EDT
Есть кое-что новенькое. Я изменил запросы, чтобы вам не приходилось устанавливать параметр pw для отображения различных единиц измерения. Каждая единица отображения рассчитывается для вас.
Отчет по механизмам хранения
SELECT
IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
SELECT ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
FROM
(SELECT *,
FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM
(SELECT
ENGINE,
SUM(data_length) DAT,
SUM(index_length) NDX,
SUM(data_length+index_length) TBL
FROM
(
SELECT engine,data_length,index_length FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL
) AAA GROUP BY ENGINE WITH ROLLUP
) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;
Отчет по базе данных
SELECT
IFNULL(DB,'Total') "Database",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
SELECT DB,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
FROM
(SELECT *,
FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM
(SELECT
DB,
SUM(data_length) DAT,
SUM(index_length) NDX,
SUM(data_length+index_length) TBL
FROM
(
SELECT table_schema DB,data_length,index_length FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL
) AAA GROUP BY DB WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;
Отчет по базе данных / ядру хранилища
SELECT
IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
SELECT DB,ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
FROM
(SELECT *,
FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM
(SELECT
DB,ENGINE,
SUM(data_length) DAT,
SUM(index_length) NDX,
SUM(data_length+index_length) TBL
FROM
(
SELECT table_schema DB,ENGINE,data_length,index_length FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL
) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;
есть ли какая-то особая причина для LPAD до 17?
@Bhathiya Я использую 17 из-за вывода функции FORMAT. Число один триллион состоит из 13 цифр. При запуске FORMAT(1000000000000,0) отображается 1,000,000,000,000. Обратите внимание, что добавлены 4 запятые. Таким образом, я использую 17 для размещения небольших столбцов дисплея рядом с столбцами большего размера, что придает отчету единообразный вид. Вы можете поэкспериментировать со значениями ширины, отличными от 17, поскольку это не влияет на вычисление дискового пространства.
Я пробовал такие запросы, и я пробую эти конкретные запросы, и я всегда сталкивался с проблемой, что они требуют много времени для выполнения. Есть ли способ оптимизировать эти запросы, чтобы они выполнялись быстрее?
@JDS У меня к вам два вопроса: 1) все ли ваши данные InnoDB? 2) Вы используете innodb_file_per_table?
1) Да. 2) Да. «Долго работать» - это от трех до пяти минут для средней (10 ГБ на диске) базы данных.
@JDS, вот в чем проблема. Если бы все таблицы были внутри ibdata1 и innodb_file_per_table был отключен, запрос был бы быстрым из-за опроса словаря данных и страниц данных / индекса внутри одного файла. В вашей ситуации каждый доступ к таблице со словарем данных ibdata1 приводит к открытию дескриптора файла для каждого соответствующего файла .ibd. У меня есть клиентская БД с 112000+ таблицами (900 ГБ), и это занимает 2 часа. У меня есть другой клиент с 41000+ таблиц (2,5 ТБ), и это занимает 15 секунд (не опечатка, я сказал 15 секунд). Разница была в innodb_file_per_table.
хорошо, спасибо. file_per_table - единственное разумное решение для нас, потому что мы управляем базами данных для наших клиентов. если бы innodb смог освободить место обратно в ОС, мы, вероятно, использовали бы монолитный файл данных. Благодарность
Поскольку у вас есть виртуальная машина, и вам все равно, как используется пространство,
Я думаю, что самый простой способ - проверить размер каталога данных MySQL.
По умолчанию это /var/lib/mysql.
Также было бы неплохо очистить двоичные журналы mysql (если возможно) перед проверкой размера каталога данных.
Вы можете сослаться на MONyog, который имеет функцию Disk Info, которая позволяет вам узнать анализ дискового пространства на уровне сервера, уровне базы данных и на уровне таблицы.
Привет, так как это ваш первый ответ, сначала проверьте, существует ли уже ваш ответ, как оно делает. В этом случае лучше ответить проголосовать за вместо того, чтобы говорить то же самое.
du -s /var/lib/mysql/* | sort -nr
Результат
34128 /var/lib/mysql/db_name1
33720 /var/lib/mysql/db_name2
29744 /var/lib/mysql/db_name3
26624 /var/lib/mysql/db_name4
16516 /var/lib/mysql/db_name5
Thsi будет отображаться в порядке убывания в килобайтах.
Вероятно, вам следует отредактировать свой вопрос и поставить 4 ведущих пробела перед каждой строкой, которая должна отображаться как код.