_ Hello,
my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours to complete. However, my inner sense is telling me that 200k inserts can't take that long (the DB is empty btw), so I am guessing that DB's feedback (line prints to terminal's stdin file descriptor) gets printed onscreen too slow; first on the local terminal on the linux machine, and then even slower on my screen I am SSH-ing from.
I would surely like to turn off all feedback from mysqld so that I would know when the inserts finished only when the mysql's client prompt got back. Probably much sooner than 3 hours. How could I do that ?
/etc/my.cnf follows:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
follows the stored procedures:
select 'Initializing database..';
call TigInitdb();
-- Possible encodings are:
-- - 'MD5-USERID-PASSWORD'
-- - 'MD5-PASSWORD'
-- - 'PLAIN'
-- More can be added if needed.
call TigPutDBProperty('password-encoding', 'PLAIN');
call TigPutDBProperty('schema-version', '4.0');
select 'Adding new users with PlainPw: ', '+391xxxxxx', 'pwd_xxxxxx';
drop function if exists TigUserGen;
delimiter //
create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
declare res_uid bigint unsigned;
insert into tig_users (user_id, sha1_user_id, user_pw)
values (_user_id, sha1(lower(_user_id)), _user_pw);
select LAST_INSERT_ID() into res_uid;
insert into tig_nodes (parent_nid, uid, node)
values (NULL, res_uid, 'root');
if _user_pw is NULL then
update tig_users set account_status = -1 where uid = res_uid;
end if;
select res_uid as uid;
end //
create procedure TigUserGen(usr_count INT)
begin
DECLARE cnt,len INT;
DECLARE usr CHAR(6);
SET cnt = 0;
SET len = 0;
SET usr = '';
WHILE cnt < usr_count DO
SET cnt = cnt + 1;
SET usr = CAST(cnt AS CHAR(6));
SET len = CHAR_LENGTH(usr);
CASE len
WHEN 1 THEN
call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 2 THEN
call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 3 THEN
call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 4 THEN
call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
WHEN 5 THEN
call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
ELSE
call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
END CASE;
END WHILE;
end //
delimiter ;
call TigUserGen(200000);
Thanks
Best Answer
Try commenting out the echoing of the uid
Try throttling the output of the ids in groups of 1250