MySQL Forums
Forum List  »  Performance

Re: use index on a view
Posted by: lau ch
Date: February 10, 2009 02:03AM

Rick James
Thanks a lot. In fact, I am a new beginner to MySQL.

>Hmmmm... This will take some thinking. But first some notes...
I have tried the "explain select * from vw_all_tables where ind_column = 123456" for all blank underlying tables, the key index column also return NULL. This circumstance is bad. When query from a view, I expect mysql will base on the condition to find out all desired records for each table first and then merge them into a big recordset and then return. (which the way the MSSQL behaves, as it can be check from MSSQL’s execution plan)

>key_buffer_size wraps at 4GB, so you really have only 1GB. But if you are only using InnoDB, recommend you set it to something smaller, like 50M.
Thanks, I will try it later. But, (In fact, I don’t the usage of key_buffer_size), does this wrapping appears in the mysql log file as a warning message when init mysql?

>innodb_buffer_pool_size may as well be even bigger, say 25G. But then, you say it seems to be running out of ram with 15+5 in caches.
I am sorry that I don’t know the usage of innodb_buffer_pool_size.

>SHOW VARIABLES LIKE '%size';
See attached below.

>You have 20 tables, each with 60 indexes? What if a user filters on three of the columns? You won't have a decent index to work with.
No. I have 20 tables each with 5 indexes only. See “tblorder01” in the attached below.

>What is the max size for MyISAM? A way to find out:
281474976710655

>How many bytes do you expect to get back from select * from vw_all_tables use index (col_name) where col_name > 1000;
Must be less than 40M bytes (or 100K rows)

>"OS to down" -- Any clues in mysqld.err? In /var/...? Elsewhere? Can you tell if you ran out of ram (seems very unlikely)?
Should be “OS to almost down” which cause all process to hanging and cannot accept new putty session. I usually simply power off the machine.

>What determines what goes into tbl01 vs tbl20? Each table has same columns and same indexes? Sounds like Partitioning.
Why 20 tables?
Our record is stored in daily basis. That is, each day has a separated table and each day has some 20M records. When put into production, we might 26 tables per month and keep 3~6 months for historical data storage.
In addition, all table schema are same.

Are all 60 columns INTs?
No, See “tblorder01” in the attached below.

>How much disk space is used by the db? I would guess 400GB.
~133GB, include the index. (you are wrong!, :>. I now presume that if each table has 60 indexes, it might use 400GB)

>I assume the index "col_name" is an index on just col_name (plus, implicitly, the PRIMARY KEY).
No. (I use stock_code & rec_datetime to perform the test. See the attached below)

Attached:

CREATE TABLE `tblorder01` (
`rec_datetime` datetime DEFAULT NULL,
`trans_type` char(2) DEFAULT NULL,
`session_type` char(1) DEFAULT NULL,
`auto_ctrl_flag` char(1) DEFAULT NULL,
`trading_status` char(2) DEFAULT NULL,
`broker_num` smallint(6) DEFAULT NULL,
`order_seq_num` bigint(20) NOT NULL DEFAULT '0',
`order_price` int(11) DEFAULT NULL,
`order_side_code` char(1) DEFAULT NULL,
`order_type` char(1) DEFAULT NULL,
`out_qty` int(11) DEFAULT NULL,
`filled_qty` int(11) DEFAULT NULL,
`total_qty` int(11) DEFAULT NULL,
`reduce_qty` int(11) DEFAULT NULL,
`reject_qty` int(11) DEFAULT NULL,
`device_type` char(2) DEFAULT NULL,
`device_num` bigint(20) DEFAULT NULL,
`firm_id` int(11) DEFAULT NULL,
`pc_user_id` smallint(6) DEFAULT NULL,
`tc_origin` char(1) DEFAULT NULL,
`tc_hedge` char(1) DEFAULT NULL,
`tc_shortsell` char(1) DEFAULT NULL,
`all_or_nothing_flag` char(1) DEFAULT NULL,
`broker_comment` char(10) DEFAULT NULL,
`bs_order_ref` int(11) DEFAULT NULL,
`bs_user_id` int(11) DEFAULT NULL,
`source_id` char(10) DEFAULT NULL,
`message_ref` int(11) DEFAULT NULL,
`ors_trans_source` char(2) DEFAULT NULL,
`order_book_reg_time` datetime DEFAULT NULL,
`order_book_upd_time` datetime DEFAULT NULL,
`cancel_type` smallint(6) DEFAULT NULL,
`exch_rate` int(11) DEFAULT NULL,
`order_status` char(1) DEFAULT NULL,
`order_op` char(1) DEFAULT NULL,
`broad_lot_flag` char(1) DEFAULT NULL,
`rej_msg_num` smallint(6) DEFAULT NULL,
`replace_flag` char(1) DEFAULT NULL,
`bskt_idx_code` char(8) DEFAULT NULL,
`bskt_time` datetime DEFAULT NULL,
`stock_code` int(11) DEFAULT NULL,
`turnover` bigint(20) DEFAULT NULL,
`shares_traded` bigint(20) DEFAULT NULL,
`trade_count` int(11) DEFAULT NULL,
`highest_trade_price` int(11) DEFAULT NULL,
`lowest_trade_price` int(11) DEFAULT NULL,
`last_trade_price` int(11) DEFAULT NULL,
`nominal_price` int(11) DEFAULT NULL,
`nominal_price_type` char(1) DEFAULT NULL,
`iep` int(11) DEFAULT NULL,
`iev` bigint(20) DEFAULT NULL,
`tot_bid_out` int(11) DEFAULT NULL,
`tot_ask_out` int(11) DEFAULT NULL,
`best_bid_price` int(11) DEFAULT NULL,
`best_ask_price` int(11) DEFAULT NULL,
PRIMARY KEY (`order_seq_num`),
KEY `rec_datetime` (`rec_datetime`),
KEY `broker_num` (`broker_num`),
KEY `firm_id` (`firm_id`),
KEY `stock_code` (`stock_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblorder02, tblorder03, ..., tblorder20 are the same.

create view vw_all_tables as select * from tblorder01 union all tblorder02 union all ...


The query I test:
SELECT order_status, rec_datetime, RIGHT(cast(stock_code AS char), 5) AS stock_code, order_side_code,
CASE order_status WHEN 'R' THEN reduce_qty WHEN 'C' THEN reduce_qty ELSE total_qty END AS total_qty, (order_price / 1000.0) AS order_price,
CASE order_status WHEN 'R' THEN (reduce_qty * (order_price / 1000.0)) WHEN 'C' THEN (reduce_qty * (order_price / 1000.0))
ELSE (total_qty * (order_price / 1000.0)) END AS value, (iep / 1000.0) AS iep, iev, RIGHT(cast(firm_id AS char), 5) AS firm_id,
RIGHT(cast(broker_num AS char), 4) AS broker_num, order_type, tc_shortsell, tc_origin, tc_hedge, device_type, ors_trans_source,
broad_lot_flag, all_or_nothing_flag, order_seq_num, broker_comment, bskt_idx_code
FROM vw_all_tables WHERE rec_datetime between cast(concat(left(now(), 11), '10:01:00') as datetime) and cast(concat(left(now(), 11), '10:15:00') as datetime) and stock_code = 5 AND order_type IN ('I', 'R', 'C') ORDER BY rec_datetime;

"SHOW VARIABLES LIKE '%size'" return
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 16777216 |
| delayed_queue_size | 1000 |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 16106127360 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_file_size | 536870912 |
| join_buffer_size | 131072 |
| key_buffer_size | 5368709120 |
| key_cache_block_size | 1024 |
| large_page_size | 0 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_heap_table_size | 25769803776 |
| max_join_size | 18446744073709551615 |
| max_relay_log_size | 0 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_sort_buffer_size | 8388608 |
| preload_buffer_size | 32768 |
| profiling_history_size | 15 |
| query_alloc_block_size | 8192 |
| query_cache_size | 0 |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_max_join_size | 18446744073709551615 |
| thread_cache_size | 0 |
| tmp_table_size | 25769803776 |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
+---------------------------------+----------------------+
33 rows in set (0.00 sec)


Content of "my.conf"

[mysqld]
#log-bin=/u01/mysql-binlog
server-id=1
#datadir=/var/lib/mysql
datadir=/u01
socket=/var/lib/mysql/mysql.sock
#socket=/u01/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
##innodb_buffer_pool_size=8G
#log-slow-queries=/var/log/mysqld.slow.log
#long_query_time=1
#log-slow-admin-statements
#innodb_file_io_threads=4
innodb_file_per_table=1
#innodb_file_format=barracuda
#innodb_strict_mode=1
innodb_thread_concurrency=8
#innodb_log_buffer_size=4M
innodb_log_file_size=256M
#innodb_additional_mem_pool_size=20M
innodb_data_home_dir =
innodb_data_file_path = /u01/ibdata1:10G:autoextend

max_allowed_packet=16M
bulk_insert_buffer_size=16M

tmp_table_size=24G
max_heap_table_size=24G

key_buffer_size=5G
innodb_buffer_pool_size=15G
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Options: ReplyQuote


Subject
Views
Written By
Posted
6344
February 04, 2009 10:22PM
3107
February 05, 2009 10:26PM
2948
February 09, 2009 02:50AM
2706
February 09, 2009 11:44PM
Re: use index on a view
2927
February 10, 2009 02:03AM
2593
February 11, 2009 01:21AM
2783
February 11, 2009 08:37PM
6108
February 11, 2009 09:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.