Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

_ 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

share|improve this question
    
Please post your my.cnf into the question –  RolandoMySQLDBA Apr 3 '12 at 20:37
    
ok, I have now done so –  kellogs Apr 3 '12 at 21:14
    
Questions: 1) The stored procedure (SP) that does the 200K inserts, is it doing so one row at a time ? 2) Are you echoing from the SP after each insert ? 3) Can you post the SP in the question (of course hiding details of what is being inserted)? –  RolandoMySQLDBA Apr 3 '12 at 21:22
    
ok, I have now done so (3). Regarding 1 and 2, I wouldn't know what to answer... –  kellogs Apr 3 '12 at 22:19
add comment

1 Answer

up vote 1 down vote accepted

Try commenting out the echoing of the uid

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 //

Try throttling the output of the ids in groups of 1250

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len,echo_count,echo_limit INT;  
    DECLARE usr CHAR(6);  
    SET cnt = 0;
    SET len = 0;
    SET usr = '';  

    SET echo_limit = 1250;    
    SET echo_count = 0;    
    WHILE cnt < usr_count DO  
        SET cnt = cnt + 1;
        SET echo_count = echo_count + 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;
        IF echo_count >= echo_limit THEN
            SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
            SET echo_count = 0;
        END IF;
    END WHILE;  
    IF echo_count >= 0 THEN
        SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
    END IF;
end //

delimiter ;

call TigUserGen(200000);
share|improve this answer
    
thanks. Will try tomorrow as my mysql is now in a deplorable state. Two versions on the same machine, unusable :|. Btw, the formatting of my code is now much nicer. How do you do it ? It never comes out that nicely when I select it and press the curly brackets icon. –  kellogs Apr 3 '12 at 23:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.