Время от времени я вижу жалобы на то, что бывают разного рода глюки с выдачей динамических ip. Я тут хорошо покрутил все и кажется понял в чем проблема. На самом деле основные проблемы 2:
1) deadlock-и в mysql - это когда некоторые запросы блокируют друг-друга и из-за этого не дают выполняться другим
2) бывает, что ip на nas-е не соответствует ip, которое светится в биллинге
Насчет deadlock-ов. Причин тут несколько:
1) сука тупой mysql. Вот реально гадина, я тебя всегда защищал, но ты впадаешь в лок на запросах, которые ну никак логически не должны приводить к такому
2) большая нагрузка
Начнем с нагрузки. Старайтесь выносить такие модули ядра как сбор трафика и заглушка на другой сервер. Особенно заглушка ибо на нее бывает льется столько трафика, что отжирает 100% проца. Если нет возможности вынести - хотя бы ограничьте количество сессий с одного ip на заглушку.
Что касается mysql. Мне пришлось кординально переделать процедуру get_ip. Я сделал такие 2 серьезных изменения:
1) сделал код примитивным и очень избыточным, что бы все sql были максимально нежными. Процедура стала выглядеть тупо, не показывайте ее профи))
2) основной затык был в выборе незанятого динамического ip. Дело в том, что mysql при большом количестве запросов выдавало одну и туже свободную запись, в этом проблемы нет, это разруливалось, но из-за такой коллизии сильно росла нагрузка. Я бы мог сделать выборку и из нее выбрать случайную строку, но mysql в этом случае создает временную таблицу, а это тоже плохо. Поэтому я поступил хитро: в таблице ip (ip_pool) я физически перегруппировую записи по типу, тегам и т.д. Для этого я создал процедуру normalize_ippool:
DROP FUNCTION IF EXISTS `normalize_ippool`;
DELIMITER $$
CREATE FUNCTION `normalize_ippool` ( )
RETURNS TINYINT NO SQL
BEGIN
DECLARE mid INTEGER UNSIGNED;
CREATE TEMPORARY TABLE temp_ip_pool(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ip_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
SELECT MAX(id) into mid FROM ip_pool;
UPDATE ip_pool SET id = id + mid;
INSERT temp_ip_pool (SELECT NULL, id FROM ip_pool ORDER BY realip, type, tags);
UPDATE ip_pool i JOIN temp_ip_pool t ON i.id = t.ip_id SET i.id = t.id;
DROP TEMPORARY TABLE IF EXISTS temp_ip_pool;
RETURN 1;
END$$
DELIMITER ;
Вызов этой функции я добавлю в админку при изменении любого ip/пула.
Благодаря этому в get_ip я сразу беру рандомную строку без предварительной выборки, зная только начальный и конечный id нужной группы.
Конечно, вам эта инфа особо не интересна, это я так для себя чтоб сохранилось в истории, ну и может теоретически кому-то понадобится.
Насчет дубликатов ip. Имеется ввиду такая ситуация: есть nas и есть сервер с биллингом. Сервер перезагружается, загружается больше 6 минут, запускает модуль ядра auth, который сразу освобождает все ip по таймауту. Он-то думает, что раз 6 минут не было аккаунтинга по данным ip - значит все сессии на насе кильнулись. Он же не знает, что просто небыло коннекта с nas. А на nas сесси-то висят, а биллинг считает ip свободными и выдает другим абонам. Когда я давно придумал схему с освобождением ip, я подумал, что сервер стартанет за пару минут. Кроме того, я не предусмотрел, что может быть разрыв канала больше чем 6 минут. Поэтому сейчас я установил таймаут в час (3600 секунд) в get_ip и set_auth:
DROP PROCEDURE IF EXISTS `set_auth`;
DELIMITER $$
CREATE PROCEDURE `set_auth` (IN usr_ip VARCHAR(15), IN auth_properties VARCHAR(255))
BEGIN
DECLARE usr_id INT;
SELECT uid INTO usr_id FROM ip_pool WHERE INET_ATON(usr_ip) = ip LIMIT 1;
IF( usr_id > 0 ) THEN
INSERT INTO auth_now SET
ip = usr_ip,
properties = auth_properties,
start = UNIX_TIMESTAMP(),
last = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
properties = IF(auth_properties!='',auth_properties,properties),
last = UNIX_TIMESTAMP();
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
WHERE ip = INET_ATON(usr_ip) AND type = 'dynamic' LIMIT 1;
END IF;
END$$
DELIMITER ;
DROP FUNCTION IF EXISTS `get_ip`;
DELIMITER $$
CREATE FUNCTION `get_ip` ( user_id INTEGER UNSIGNED )
RETURNS VARCHAR(15) NO SQL
BEGIN
DECLARE user_ip VARCHAR(15);
DECLARE real_ip VARCHAR(15) DEFAULT 0;
DECLARE row_cnt INTEGER;
DECLARE ip_id INTEGER;
DECLARE tries INTEGER DEFAULT 30;
DECLARE id_min INTEGER;
DECLARE id_max INTEGER;
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id AND type='static' LIMIT 1;
IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;
SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
LIMIT 1;
IF( ip_id IS NOT NULL) THEN
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
WHERE id = ip_id AND uid = user_id;
SELECT ROW_COUNT() INTO row_cnt;
IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
END IF;
SELECT MAX(id), MIN(id) INTO id_max, id_min
FROM ip_pool
WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0);
sel_ip: WHILE tries > 0 DO
SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
FROM ip_pool
WHERE uid = 0
AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
AND id <= id_max
LIMIT 1;
IF( user_ip IS NOT NULL) THEN
UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
WHERE id = ip_id AND uid = 0;
SELECT ROW_COUNT() INTO row_cnt;
IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
SET tries = tries - 5;
END IF;
SET tries = tries - 1;
END WHILE;
END$$
DELIMITER ;
