Tag Archives: MySQL

mysql create 1 million test records

To create a lot of records for testing is possible using PROCEDURE like this:

DELIMITER //
drop procedure if exists gen //
CREATE TABLE mytable ( code int, a int, b int ) //
CREATE PROCEDURE gen() BEGIN DECLARE i int DEFAULT 0; WHILE i <= 1000000 DO INSERT INTO mytable (code, a, b) VALUES (i, 1, 1); SET i = i + 1; END WHILE; END //
CALL gen() //
DELIMITER ;

slow LATERAL DERIVED SQL using GROUP BY subqueries

If you have some new MariaDB or MYSQL version and slow GROUP BY issue, you can try explain your slow query and find many 'LATERAL DERIVED' select type, so you can try change optimizer:

MariaDB [db]> set optimizer_switch='split_materialized=off';
Query OK, 0 rows affected (0.000 sec)

To set permanently vi /etc/my.cnf.d/server.cnf

[mysqld]
optimizer_switch=split_materialized=off

compare mysql configuration

wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-release-latest.noarch.rpm
yum install percona-toolkit

Create same MySQL user on different databases and run:

pt-config-diff h=192.168.0.99 h=192.168.0.100 -p Password -u User

# A software update is available:
74 config differences
Variable                  m1.srv.net               old100.srv.net
========================= ========================= =========================
back_log                  80                        50
binlog_format             MIXED                     STATEMENT
general_log_file          m1.log                    /var/lib/mysql/old100.log
group_concat_max_len      1048576                   1024
have_openssl              YES                       DISABLED
have_symlink              YES                       DISABLED
hostname                  m1.srv.net                old100.srv.net
innodb_autoextend_incr... 64                        8
innodb_buffer_pool_ins... 8                         1
innodb_buffer_pool_size   5368709120                1073741824
innodb_concurrency_tic... 5000                      500
innodb_data_file_path     ibdata1:12M:autoextend    ibdata1:10M:autoextend
innodb_file_format                                  Antelope
innodb_flush_method       fsync                     
innodb_large_prefix                                 OFF
innodb_log_buffer_size    16777216                  8388608
innodb_log_file_size      50331648                  5242880
innodb_max_dirty_pages... 75.000000                 75
innodb_old_blocks_time    1000                      0
innodb_open_files         2000                      300
innodb_purge_batch_size   300                       20
innodb_purge_threads      4                         0
innodb_spin_wait_delay    4                         6
innodb_stats_on_metadata  OFF                       ON
innodb_strict_mode        ON                        OFF
innodb_version            10.4.13                   5.5.54
join_buffer_size          262144                    131072
key_buffer_size           134217728                 8388608
lc_messages_dir                                     /usr/share/mysql/
lock_wait_timeout         86400                     31536000
log_error                                           /var/log/mysqld.log
log_warnings              2                         1
max_allowed_packet        16777216                  1048576
max_connect_errors        100                       10
max_long_data_size        16777216                  1048576
max_relay_log_size        1073741824                0
max_seeks_for_key         4294967295                18446744073709551615
max_write_lock_count      4294967295                18446744073709551615
myisam_recover_options    BACKUP,QUICK              OFF
myisam_sort_buffer_size   134216704                 8388608
old_alter_table           DEFAULT                   OFF
open_files_limit          16364                     5000
optimizer_switch          index_merge=on,index_m... index_merge=on,index_m...
performance_schema_eve... -1                        10000
performance_schema_eve... -1                        10
performance_schema_max... 90                        80
performance_schema_max... -1                        1000
performance_schema_max... -1                        10000
performance_schema_max... -1                        1000000
performance_schema_max... 40                        30
performance_schema_max... -1                        1000000
performance_schema_max... -1                        100000
performance_schema_max... -1                        50000
performance_schema_max... -1                        1000
pid_file                  /var/lib/mysql/m1.pid   /var/run/mysqld/mysqld...
query_alloc_block_size    16384                     8192
query_cache_size          1048576                   0
query_cache_type          OFF                       ON
query_prealloc_size       24576                     8192
secure_auth               ON                        OFF
secure_file_priv                                    /var/lib/mysql-files/
server_id                 1                         0
slave_net_timeout         60                        3600
slow_query_log_file       m1-slow.log               /var/lib/mysql/old100-slo...
sql_mode                  STRICT_TRANS_TABLES,ER... 
sync_master_info          10000                     0
sync_relay_log            10000                     0
sync_relay_log_info       10000                     0
system_time_zone          UTC                       EEST
table_open_cache          2000                      400
thread_cache_size         151                       0
thread_stack              299008                    262144
version                   10.4.13-MariaDB           5.5.54
version_comment           MariaDB Server            MySQL Community Server...

ERROR 2013 (HY000): Lost connection to MySQL server during query


InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: the size of single-table tablespace file ./my_database/wp_badTable.ibd

mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

mysql> drop database my_database;
ERROR 2013 (HY000): Lost connection to MySQL server during query

service mysqld stop
Shutting down MySQL... SUCCESS!

# Backup corrupted database files:
mv -v /var/lib/mysql/my_database/wp_wfHoover.* /root/
`/var/lib/mysql/my_database/wp_badTable.frm' -> `/root/wp_badTable.frm'
`/var/lib/mysql/my_database/wp_badTable.ibd' -> `/root/wp_badTable.ibd'

service mysqld start
Starting MySQL.. SUCCESS!

mysql> drop database my_database;
Query OK, 30 rows affected (0.40 sec)

service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!

200430 14:12:18 [Note] /usr/sbin/mysqld: Normal shutdown
200430 14:12:21 [Note] /usr/sbin/mysqld: ready for connections.

Now you can recreate database and restore from backups.

sysbench mariadb

sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=100000 \
--oltp-tables-count=12 \
--num-threads=4 \
--mysql-host=192.168.200.110 \
--mysql-db=test_database \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run