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.
© 2022, Oracle Corporation and/or its affiliates