Server IP : 172.67.216.182 / Your IP : 172.69.176.32 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/mysql-test/t/ |
Upload File : |
# # Test of the cost constants when restarting the server # # Can not run with embedded server since it requires server restarts -- source include/not_embedded.inc # To get stable cost estimates, the test should only be run with # 16K InnoDB page size. --source include/have_innodb_16k.inc # # Test that changes to cost constants are used after restarting server # # Verify that the content of the two cost constants tables are as expected SELECT cost_name,cost_value FROM mysql.server_cost; SELECT engine_name,cost_name,cost_value FROM mysql.engine_cost; # # Create a test database that will be used for running queries # CREATE TABLE t0 ( i1 INTEGER ); INSERT INTO t0 VALUE (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); # Create a table with 100 records each having size approximately 1000 bytes CREATE TABLE t1_innodb ( pk INTEGER PRIMARY KEY, i1 INTEGER NOT NULL, c1 CHAR(250), c2 CHAR(250), c3 CHAR(250), c4 CHAR(250), INDEX i1_key (i1) ) ENGINE=InnoDB; INSERT INTO t1_innodb SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl' FROM t0 AS a0, t0 AS a1; # Create the same table in MyISAM CREATE TABLE t1_myisam ( pk INTEGER PRIMARY KEY, i1 INTEGER NOT NULL, c1 CHAR(250), c2 CHAR(250), c3 CHAR(250), c4 CHAR(250), INDEX i1_key (i1) ) ENGINE=MyISAM; INSERT INTO t1_myisam SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl' FROM t0 AS a0, t0 AS a1; ANALYZE TABLE t1_innodb, t1_myisam; # Run two queries to see cost estimates when run with default cost constants let query_innodb= SELECT * FROM t1_innodb; let query_myisam= SELECT * FROM t1_myisam; --echo "Explain with cost estimate against InnoDB" eval EXPLAIN FORMAT=JSON $query_innodb; --echo "Explain with cost estimate against MyISAM" eval EXPLAIN FORMAT=JSON $query_myisam; # # Update one cost constant in each of the two cost constant tables. # The new value is double of the default value. # UPDATE mysql.server_cost SET cost_value=0.4 WHERE cost_name="row_evaluate_cost"; UPDATE mysql.engine_cost SET cost_value=2.0 WHERE cost_name="memory_block_read_cost"; --echo "Restarting MySQL server" --source include/restart_mysqld.inc --echo "MySQL restarted" SELECT cost_name,cost_value FROM mysql.server_cost; SELECT engine_name, cost_name,cost_value FROM mysql.engine_cost; # # Run the two queries an validate that the cost estimate has doubled # --echo "Explain with cost estimate against InnoDB" eval EXPLAIN FORMAT=JSON $query_innodb; --echo "Explain with cost estimate against MyISAM" eval EXPLAIN FORMAT=JSON $query_myisam; # Reset the cost constants UPDATE mysql.server_cost SET cost_value=DEFAULT; UPDATE mysql.engine_cost SET cost_value=DEFAULT; # # Test that adding an engine specific cost constant does not influence # other engines. # INSERT INTO mysql.engine_cost VALUES ("InnoDB", 0, "memory_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT); --echo "Restarting MySQL server" --source include/restart_mysqld.inc --echo "MySQL restarted" # # Run the two queries and validate that the query against MyISAM has # the original cost estimates while the read cost for InnoDb should # be four times the original # --echo "Explain with cost estimate against InnoDB" eval EXPLAIN FORMAT=JSON $query_innodb; --echo "Explain with cost estimate against MyISAM" eval EXPLAIN FORMAT=JSON $query_myisam; # Delete the added entry for InnoDB DELETE FROM mysql.engine_cost WHERE engine_name NOT LIKE "default"; --echo "Restarting MySQL server" --source include/restart_mysqld.inc DROP TABLE t0, t1_innodb, t1_myisam;