Bug #77345 The aggregate functions of MySQL is too slow, too much CPU for type convertion
Submitted: 14 Jun 2015 9:26
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: server, tuning

[14 Jun 2015 9:26] Fangxin Flou
Description:
We want to create report from MySQL database, found that it's very slow, by doing a perf top when running report query, we found that too much CPU are used for type convertion at server side. Any plan to improve it?

For MyISAM (234ms for query "select col2, sum(col3) from my_range group by col2")

  7.84%  libc-2.12.so        [.] memcpy
  7.18%  mysqld              [.] _mi_rec_unpack
  5.01%  mysqld              [.] decimal2bin
  4.77%  mysqld              [.] bin2decimal
  4.41%  mysqld              [.] evaluate_join_record(JOIN*, st_join_table*)
  3.48%  mysqld              [.] end_update(JOIN*, st_join_table*, bool)
  3.43%  mysqld              [.] Item_field::val_decimal(my_decimal*)
  3.20%  mysqld              [.] field_conv(Field*, Field*)
  3.13%  mysqld              [.] do_add
  3.08%  mysqld              [.] _mi_read_rnd_dynamic_record
  2.68%  mysqld              [.] my_hash_sort_simple
  2.66%  mysqld              [.] handler::ha_update_row(unsigned char const*, unsigned char*)
  2.49%  mysqld              [.] Field_varstring::store(char const*, unsigned int, charset_info_st const*)
  2.41%  mysqld              [.] my_strnncollsp_simple
  2.12%  mysqld              [.] well_formed_copy_nchars(charset_info_st const*, char*, unsigned int, charset_info_st const*, char
  1.94%  mysqld              [.] my_decimal2binary(unsigned int, my_decimal const*, unsigned char*, int, int)
  1.84%  mysqld              [.] hp_key_cmp
  1.83%  mysqld              [.] do_copy_null(Copy_field*)
  1.74%  mysqld              [.] heap_update
  1.65%  mysqld              [.] QEP_tmp_table::put_record()
  1.62%  mysqld              [.] _mi_read_cache
  1.56%  mysqld              [.] _mi_get_block_info
  1.55%  mysqld              [.] Item_sum_sum::update_field()
  1.51%  mysqld              [.] heap_rkey

For InnoDB (293ms for query "select col2, sum(col3) from my_range group by col2")

  8.89%  mysqld            [.] row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long, unsigned long)
  6.52%  mysqld            [.] row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, unsigned char con
  4.70%  libc-2.12.so      [.] memcpy
  4.30%  mysqld            [.] bin2decimal
  4.23%  mysqld            [.] decimal2bin
  4.17%  mysqld            [.] rec_get_offsets_func(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, mem_b
  4.06%  mysqld            [.] row_sel_store_mysql_field_func(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long
  3.82%  mysqld            [.] evaluate_join_record(JOIN*, st_join_table*)
  3.02%  mysqld            [.] hp_key_cmp
  2.72%  mysqld            [.] do_add
  2.63%  mysqld            [.] row_sel_store_mysql_rec(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned long, dict_i
  2.54%  mysqld            [.] field_conv(Field*, Field*)
  2.45%  mysqld            [.] end_update(JOIN*, st_join_table*, bool)
  2.33%  mysqld            [.] handler::ha_update_row(unsigned char const*, unsigned char*)
  2.25%  mysqld            [.] hp_search
  1.97%  mysqld            [.] my_hash_sort_simple
  1.92%  mysqld            [.] well_formed_copy_nchars(charset_info_st const*, char*, unsigned int, charset_info_st const*, char co
  1.86%  mysqld            [.] do_varstring(Copy_field*)
  1.83%  mysqld            [.] Field_varstring::store(char const*, unsigned int, charset_info_st const*)
  1.55%  mysqld            [.] my_strnncollsp_simple
  1.53%  mysqld            [.] my_decimal2binary(unsigned int, my_decimal const*, unsigned char*, int, int)
  1.37%  mysqld            [.] heap_update
  1.37%  mysqld            [.] handler::ha_statistic_increment(unsigned long long system_status_var::*) const
  1.37%  mysqld            [.] hp_rec_key_cmp
  1.36%  mysqld            [.] heap_rkey

How to repeat:
create a test table

CREATE TABLE `my_range` (
  `id` int(11) NOT NULL,
  `col2` varchar(8) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM 

fill in 1m rows, and run the following query

select col2, sum(col3) from my_range group by col2

Suggested fix:
Port Oracle's number type as the new decimal format.