Архивирование списаний
должно для твоих выборокcooler85 писал(а):вот как я думаю на счет индексов... по моему ADD INDEX(account_id,discount_date) должно хватить...
а если использовать интерфейс администратора стандартный то надо еще вешать индексы на date_hour date mount date_day по ним биллинг рисует отчеты по дням часам месяцам
вот пробывал я делать архивные таблицы ещё в августе... но основные таблицы "не похудели", то есть реально в них данных нет столько, а весят они дофига. прочитал, что после DELETE FROM надо
как быть?
но это не помогло... файлы .MYD весят оч много.In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier to use, but myisamchk is faster. See Section 12.5.2.5, “OPTIMIZE TABLE Syntax”, and Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
как быть?
ок, допустим тип таблиц у меня innodb
сделана архивация, данные перекочевали в архивные таблицы
когда и как их можно оттуда удалять безвозвратно? (сейчас файлы innodb занимаю больше сотни гигов)
даже если я дропну архивные таблицы размер этих файлов не уменьшится, то есть остается один вариант полный дроп базы, останов mysql, удаление файлов innodb и заливка базы из дампа с нуля ?
ps
ктсати разработчики не рекомендуют использовать тип таблиц myisam, тк нет поддержки транзакций (все-таки биллинг а не база какого-нибудь форума)
сделана архивация, данные перекочевали в архивные таблицы
когда и как их можно оттуда удалять безвозвратно? (сейчас файлы innodb занимаю больше сотни гигов)
даже если я дропну архивные таблицы размер этих файлов не уменьшится, то есть остается один вариант полный дроп базы, останов mysql, удаление файлов innodb и заливка базы из дампа с нуля ?
ps
ктсати разработчики не рекомендуют использовать тип таблиц myisam, тк нет поддержки транзакций (все-таки биллинг а не база какого-нибудь форума)
Какие нафиг транзакции в этих таблицах в которых данные только складываются и больше никогда не меняются?jack7 писал(а):ок, допустим тип таблиц у меня innodb
сделана архивация, данные перекочевали в архивные таблицы
когда и как их можно оттуда удалять безвозвратно? (сейчас файлы innodb занимаю больше сотни гигов)
даже если я дропну архивные таблицы размер этих файлов не уменьшится, то есть остается один вариант полный дроп базы, останов mysql, удаление файлов innodb и заливка базы из дампа с нуля ?
ps
ктсати разработчики не рекомендуют использовать тип таблиц myisam, тк нет поддержки транзакций (все-таки биллинг а не база какого-нибудь форума)
Ситуация такая: часть таблиц к которым часто обращается биллинг личше перевести в муисам, как показывает практика он там меньше зацикливаются, причем это не дидлок на уровне базы, это именно зацикливание на уровне логики биллинга, а часть таблиц к которые самые большие типа паймент, или транзакшин, можно смело перевоить в муисам.. от этого их не поплохеет.
по поводу уменьшения размера таблиц: на самом деле глубоко пофиг, сколько они занимают места. по сути это файловая система мускула на файловой системе операционки, на скорость это никак не влияет, только на размер.
что казается размера:
Советую вам перевести движок иннодб в режим одна таблица один файл, перестанет быть доступной возможность ставить связанные межтабличные индексы, но они все равно не используются в виду того что база изначально под них не запроектирована
в этом случае размер каждой таблицы можно уменьшить командой ALTER TABLE ...
По поводу транзакционных таблиц:
в таблице транзакшин_иптрафик, можете убить автоинкремент с поля id и соответственно примари индекс c этого поля, так как всегда биллинг вставляет значение соответствующего id из транзакшин_алл, и никогда не пользуется автоинкрементом, это позволит уменьшить размер таблицы процентов на 30
при манипулировани данными в таблицах транзакши_алл и транзакшин_иптрафик следует придерживатся толко одного правила:
если удаляем запись с ит их транзакшин_алл то нужно этот же ид попытатся удалить и из таблицы транзакшин _иптрафик, почему попытатся потому что таблица транзакшин_иптрафик является дочерней таблицей, и содержит просто более полную информацию о строках с типом 3 (помоему, это передача ип трафика) из таблица транзакшин_алл, иначе бы пришлось в таблицу иптрафик_ал вставлять дофига столбцов, и получилось бы совсем нерациональное использование размера ряда.
По поводу оптимизации этих двух таблиц как счас у меня:
Код: Выделить всё
CREATE TABLE IF NOT EXISTS `discount_transactions_all` (
`id` int(11) unsigned NOT NULL auto_increment,
`account_id` smallint(5) unsigned NOT NULL default '0',
`incoming_rest` float NOT NULL default '0',
`outgoing_rest` float NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`service_type` tinyint(3) unsigned NOT NULL default '0',
`discount_period_id` mediumint(8) unsigned NOT NULL default '0',
`slink_id` mediumint(8) unsigned NOT NULL default '0',
`discount_date` int(10) unsigned NOT NULL default '0',
`charge_type` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `discount_date` (`discount_date`),
KEY `account_id` (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=852031149 ;Код: Выделить всё
CREATE TABLE IF NOT EXISTS `discount_transactions_iptraffic_all` (
`id` int(11) NOT NULL,
`account_id` smallint(5) unsigned NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`discount_period_id` mediumint(8) unsigned NOT NULL default '0',
`slink_id` mediumint(8) unsigned NOT NULL default '0',
`discount_date` int(10) unsigned NOT NULL default '0',
`discount_date_hour` int(10) unsigned NOT NULL default '0',
`discount_date_day` int(10) unsigned NOT NULL default '0',
`discount_date_month` int(10) unsigned NOT NULL default '0',
`t_class` smallint(5) unsigned NOT NULL default '0',
`base_cost` float NOT NULL default '0',
`ipid` int(11) NOT NULL default '0',
`bytes` bigint(20) unsigned NOT NULL default '0',
KEY `acc_dis` (`account_id`,`discount_period_id`),
KEY `discount_date` (`discount_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `discount_transactions_all_200809` (
Код: Выделить всё
`id` int(11) NOT NULL,
`account_id` smallint(5) unsigned NOT NULL default '0',
`incoming_rest` float NOT NULL default '0',
`outgoing_rest` float NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`service_type` smallint(5) NOT NULL default '0',
`discount_period_id` int(11) NOT NULL default '0',
`slink_id` int(11) NOT NULL default '0',
`discount_date` int(11) NOT NULL default '0',
`charge_type` int(11) NOT NULL default '0',
KEY `mix` (`account_id`,`discount_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;Код: Выделить всё
CREATE TABLE IF NOT EXISTS `discount_transactions_iptraffic_all_200809` (
`id` int(11) NOT NULL,
`account_id` smallint(5) unsigned NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`discount_period_id` int(11) NOT NULL default '0',
`slink_id` int(11) NOT NULL default '0',
`discount_date` int(11) NOT NULL default '0',
`discount_date_hour` int(11) NOT NULL default '0',
`discount_date_day` int(11) NOT NULL default '0',
`discount_date_month` int(11) NOT NULL default '0',
`t_class` smallint(5) unsigned NOT NULL default '0',
`base_cost` float NOT NULL default '0',
`ipid` int(11) NOT NULL default '0',
`bytes` bigint(20) unsigned NOT NULL default '0',
KEY `mix` (`account_id`,`discount_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;Да тут как бы особых скриптов и не надо. Возможно, эти ссылки вам помогут:
http://www.maatkit.org
http://www.maatkit.org/doc/mk-archiver.html
http://www.xaprb.com/blog/2006/05/02/ho ... bs-in-sql/
http://www.maatkit.org
http://www.maatkit.org/doc/mk-archiver.html
http://www.xaprb.com/blog/2006/05/02/ho ... bs-in-sql/
в этой же теме на 1 страницеzooxel писал(а):Господа, многие были бы Вам признательны, если бы Вы опубликовали здесь полную версию скрипта с небольшими комментариями.
Код: Выделить всё
#!/usr/local/bin/perl
use IO::Handle;
use DBI;
$dbh = DBI->connect("DBI:mysql:UTM5:localhost", "user", "yourpass", {RaiseError=>1})
or die "\nConnection failed...\nError: $DBI::errstr\n";
#get end_date of period to archive
$sth = $dbh->prepare("select UNIX_TIMESTAMP(concat(YEAR(now()),'-',MONTH(now()),'-1 0:0:0'));");
$sth -> execute();
$enddate=$sth->fetchrow_array;
############################################
#Prepare to archive discount_transactions_all
print "Prepare to archive discount_transactions_all \n";
#get last archive id
$sth = $dbh->prepare("select max(archive_id) from archives where table_type=1");
$sth -> execute();
$archiveid_type1=$sth->fetchrow_array; $archiveid_type1++;
print "Last archive id=".$archiveid_type1."\n";
#get start date to archive
$sth = $dbh->prepare("select min(discount_date) from discount_transactions_all");
$sth -> execute();
$start_date=$sth->fetchrow_array;
print "Start date of archive period : ".$start_date."\n";
print "End date of archive period : ".$enddate."\n";
$arch_table_name="discount_transactions_all_".$enddate;
print "Name of new archive table : ".$arch_table_name."\n";
#Create new archive table
$sth = $dbh->prepare("CREATE TABLE ".$arch_table_name." (
id int(11) NOT NULL auto_increment,
account_id int(11) default NULL,
incoming_rest double default NULL,
outgoing_rest double default NULL,
discount double default NULL,
discount_without_tax double default NULL,
discount_with_tax double default NULL,
service_id int(11) default NULL,
service_type int(11) default NULL,
discount_period_id int(11) default NULL,
slink_id int(11) default NULL,
discount_date int(11) default NULL,
`comment` varchar(255) collate utf8_unicode_ci default NULL,
dealer_transaction_id int(11) default NULL,
dealer_payment_transaction_id int(11) default NULL,
is_canceled int(11) default '0',
cancel_id int(11) default '0',
charge_type int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY first_disc (discount_date),
KEY first_disc1 (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;");
$sth -> execute();
#prepare data to move in new table
print "Copy transactions to new archive table....\n";
$sth = $dbh->prepare("INSERT INTO ".$arch_table_name." SELECT * FROM discount_transactions_all where discount_date<".$enddate);
$sth -> execute();
print "Delete transactions from discount_transactions_all....\n";
$sth = $dbh->prepare("DELETE FROM discount_transactions_all USING discount_transactions_all
INNER JOIN ".$arch_table_name." ON discount_transactions_all.id =".$arch_table_name.".id;");
$sth -> execute();
$sth = $dbh->prepare("SELECT min(discount_date),max(discount_date) from ".$arch_table_name);
$sth -> execute();
($start_date,$end_date)=$sth->fetchrow_array;
$sth = $dbh->prepare("INSERT INTO archives (archive_id,table_type,table_name,
start_date,end_date) VALUES (".$archiveid_type1.",1,'".$arch_table_name."',".$start_date.",".$end_date.");");
$sth -> execute();
############################################
#Prepare to archive discount_transactions_iptraffic_all
print "\n\nPrepare to archive discount_transactions_iptraffic_all \n";
#get last archive id
$sth = $dbh->prepare("select max(archive_id) from archives where table_type=2");
$sth -> execute();
$archiveid_type2=$sth->fetchrow_array; $archiveid_type2++;
print "Last archive id=".$archiveid_type2."\n";
#get start date to archive
$sth = $dbh->prepare("select min(discount_date) from discount_transactions_iptraffic_all");
$sth -> execute();
$start_date=$sth->fetchrow_array;
print "Start date of archive period : ".$start_date."\n";
print "End date of archive period : ".$enddate."\n";
$arch_table_name="discount_transactions_iptraffic_all_".$enddate;
print "Name of new archive table : ".$arch_table_name."\n";
#Create new archive table
$sth = $dbh->prepare("CREATE TABLE ".$arch_table_name." (
id int(11) NOT NULL auto_increment,
account_id int(11) default NULL,
discount double default NULL,
discount_with_tax double default NULL,
service_id int(11) default NULL,
discount_period_id int(11) default NULL,
slink_id int(11) default NULL,
discount_date int(11) default NULL,
discount_date_hour int(11) default NULL,
discount_date_day int(11) default NULL,
discount_date_month int(11) default NULL,
t_class int(11) default '0',
base_cost double default '0',
ipid int(11) default '0',
bytes bigint(20) default NULL,
is_canceled int(11) default '0',
cancel_id int(11) default '0',
PRIMARY KEY (id),
KEY first_dtr (discount_date,account_id,slink_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
$sth -> execute();
#prepare data to move in new table
print "Copy transactions to new archive table....\n";
$sth = $dbh->prepare("INSERT INTO ".$arch_table_name." SELECT * FROM discount_transactions_iptraffic_all where discount_date<".$enddate);
$sth -> execute();
print "Delete transactions from discount_transactions_all....\n";
$sth = $dbh->prepare("DELETE FROM discount_transactions_iptraffic_all USING discount_transactions_iptraffic_all
INNER JOIN ".$arch_table_name." ON discount_transactions_iptraffic_all.id =".$arch_table_name.".id;");
$sth -> execute();
$sth = $dbh->prepare("SELECT min(discount_date),max(discount_date) from ".$arch_table_name);
$sth -> execute();
($start_date,$end_date)=$sth->fetchrow_array;
#print $start_date,$end_date,"\n";
$sth = $dbh->prepare("INSERT INTO archives (archive_id,table_type,table_name,
start_date,end_date) VALUES (".$archiveid_type2.",2,'".$arch_table_name."',".$start_date.",".$end_date.");");
$sth -> execute();
$sth->finish;
$dbh->disconnect;
на сервере с 8-ю виртуальными процами и 8 гигами оперативы база в 45 гигов архивируется уже 35 часов (идет последняя стадия - удаление старых записей из discount_transactions_iptraffic_all)
вот у меня тож оч долго было.....я уж было стал думать, что повисло, снял задачу, потом пришлось разбираться какая стадия шла и чуть-чуть дописывать скрипт, чтоб с презнего места началось думаю тут проблема в настройке сервера мускула. он у меня ваще никак не настроен, поэтому медленно. но скрипт рабочий 100% большое спасибо создателю
думаю тут проблема в настройке сервера мускула. он у меня ваще никак не настроен, поэтому медленно. но скрипт рабочий 100% большое спасибо создателю 
Скорее всего тормозит из-за INNER JOIN. Мне кажется, здесь можно обойтись без связи таблиц и ограничиться обычным запросом на удаление:
и
Код: Выделить всё
DELETE FROM discount_transactions_all where discount_date<".$enddateКод: Выделить всё
DELETE FROM discount_transactions_iptraffic_all where discount_date<".$enddateЧего вы херней страдаетеAta-man писал(а):Скорее всего тормозит из-за INNER JOIN. Мне кажется, здесь можно обойтись без связи таблиц и ограничиться обычным запросом на удаление:иКод: Выделить всё
DELETE FROM discount_transactions_all where discount_date<".$enddateКод: Выделить всё
DELETE FROM discount_transactions_iptraffic_all where discount_date<".$enddate
бегин
ренаме discount_transactions_all в discount_transactions_all_хххх
смотрим автоинкремент ид у discount_transactions_all_хххх
креате табле discount_transactions_all с автоинкремент из discount_transactions_all_хххх
далее спокойно переносим маленький кусочек данных из discount_transactions_all_хххх в discount_transactions_all
тоже самое с discount_transactions_iptraffic_all
коммит
ЗЫ естесно данная операция должна быть строго ежемесячной
Magnum72 писал(а): бегин
ренаме discount_transactions_all в discount_transactions_all_хххх
смотрим автоинкремент ид у discount_transactions_all_хххх
креате табле discount_transactions_all с автоинкремент из discount_transactions_all_хххх
далее спокойно переносим маленький кусочек данных из discount_transactions_all_хххх в discount_transactions_all
тоже самое с discount_transactions_iptraffic_all
коммит
точно, спасибо еще раз
1. а что в это время делает биллинг, пытающийся записать данные в discount_transactions_* ?Magnum72 писал(а): бегин
ренаме discount_transactions_all в discount_transactions_all_хххх
смотрим автоинкремент ид у discount_transactions_all_хххх
креате табле discount_transactions_all с автоинкремент из discount_transactions_all_хххх
далее спокойно переносим маленький кусочек данных из discount_transactions_all_хххх в discount_transactions_all
тоже самое с discount_transactions_iptraffic_all
коммит
ЗЫ естесно данная операция должна быть строго ежемесячной
2. сколько, на вашем примере, занимает эта операция?