Mysql poor performance

Question

I have this simple table

CREATE TABLE `user_did_something` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `something_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` int(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Indexes for table `user_did something`
ALTER TABLE `user_did_something`
  ADD PRIMARY KEY (`id`);

and when I try to insert

INSERT INTO user_did_something (something_id, user_id) VALUES (1,11)

runtime is ~ 70 ms.

Testing the same structure on another machine, runtime ~ 3 ms.

Additional Information:

• version 10.0.28-MariaDB-0ubuntu0.16.04

• the table has no rows

• only index for start column id.

I do not know how to debug this. Read some materials on the Internet, reload everything, all the same result.

I am looking for guidance on debugging such things without having to reinstall the mysql server on my machine.

Answer 1

I am looking for guidance on debugging such things without having to reinstall the mysql server on my machine.

. , - , . , , , .

DTrace! , ( ).

DTrace , , Solaris, Mac OS X * FreeBSD, , Linux. ( BPF Linux 4.9-rc1).

* Mac OS X DTrace, System Integrity Protection

mysqld , :

dtrace -p $(pgrep -x mysqld) -F -n 'pid$target:mysqld::entry{} pid$target:mysqld::return{}'

... InnoDB :

2  -> sync_arr_wake_threads_if_sema_free    
6  -> os_event_reset                        
2    -> os_mutex_enter                      
6  <- os_event_reset                        
2    <- os_mutex_enter                      
6  -> pfs_mutex_exit_func                   
6  <- pfs_mutex_exit_func                   
2    -> os_mutex_exit                       
6  -> os_event_reset                        
2    <- os_mutex_exit                       
6  <- os_event_reset                        

, MySQL!

dtrace -p $(pgrep -x mysqld) -F -n 'pid$target:mysqld:*dispatch_command*:entry{printf("Query: %s\n", copyinstr(arg2));}'

:

CPU FUNCTION                                 
0  -> dispatch_command(enum_server_command, THD*, char*, unsigned int) Query: SHOW VARIABLES LIKE 'pid_file'

, DTrace , , . : mysqld:*dispatch_command*:entry - , , pid$target:mysqld::entry|return , . mysqld:*dispatch_command*:exit . , , , .

, ; MySQL , DTrace .

, DTraceToolkit, , , , , , , .. , , DTrace.

---

, Ben Rockwood, MySQL DTrace. .

DTrace Brendan DTrace book. , , .

Answer 2

, MySQL. , , . :

, Ubuntu.

, :

SHOW VARIABLES LIKE have_query_cache;
SHOW VARIABLES LIKE query_cache_size;

, , , :

SHOW PROCESSLIST;

, x $statement_analysis:

SELECT * FROM sys.`x$statement_analysis`

. , Threads_connected:

SHOW GLOBAL STATUS