Hi,
No, the database server is different. Originally, MariaDB was co-collocated with the application service. As part of of the switch to InnoDB, we've moved MariaDB to it's own server, but the settings are the same with the exception of the the innodb-buffer-pool-size. When we were using MyISAM, this wasn't set. Now we're using InnoDB, we've set this to 24G (server has 32GB RAM).
Looking at a pstack taken will the query is running, it looks like the server is spending most of it's time reading
Thread 8 (Thread 0x7f74200d9700 (LWP 7957)): #0 0x00007f74244c0013 in pread64 () from /lib64/libpthread.so.0 #1 0x00005582e07e86e1 in os_file_pread () #2 0x00005582e07ec216 in os_file_read_func () #3 0x00005582e07eeb02 in os_aio_func () #4 0x00005582e079f959 in _fil_io () #5 0x00005582e076c6a3 in buf_read_page () #6 0x00005582e0755837 in buf_page_get_gen () #7 0x00005582e0742ca5 in btr_cur_search_to_nth_level () #8 0x00005582e06f3adb in row_search_for_mysql () #9 0x00005582e06c4c50 in ha_innobase::index_next(unsigned char*) () #10 0x00005582e091a6a7 in ha_partition::handle_ordered_next(unsigned char*, bool) () #11 0x00005582e0465417 in join_read_next(READ_RECORD*) () #12 0x00005582e045a0a3 in sub_select(JOIN*, st_join_table*, bool) () #13 0x00005582e046887d in do_select(JOIN*, List<Item>*, TABLE*, Procedure*) () #14 0x00005582e047aa9b in JOIN::exec() () #15 0x00005582e0475c19 in mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) () #16 0x00005582e04766eb in handle_select(THD*, LEX*, select_result*, unsigned long) ()
Looking at the engine status, I'm starting to think that the O/S is doing a better job of pre-fetching (with MyISAM, the majority of RAM was being used by the O/S filesystem cache) then InnoDB does
FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 592947343 OS file reads, 80039464 OS file writes, 35075860 OS fsyncs 1 pending preads, 0 pending pwrites 1355.13 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s .... Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
/Conor