Enabling and confirming CURA with TiDB #9
Comments
|
Hi, can you show the explain results for the queries? |
|
Hi, This is an example for query 5: -- using 1365545250 as a seed to the RNG
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= '1994-01-01'
and o_orderdate < date_add('1994-01-01', interval '1' year)
group by
n_name
order by
revenue desc;
Plan: |
|
I suggest you try a simple query like |
|
I'm getting: |
|
Hi Yujin, welcome back. You can refer to https://github.com/zanmato1984/tidb-hackathon-2020/blob/master/sql/gpu_settings.sql for the necessary settings for tidb running through cura. Please also be aware that our integration of cura into tidb is more like a demo rather than a product. Actually we only adapted operators/functions needed by q5, q9, q17, q18. If you run other queries, you'll very likely to see failures. |
|
By the way, if the table contains column with string type(char, varchar), you need to build a customized tikv to run cura query.(https://github.com/windtalker/tikv/tree/tikv_cura) |
|
For some reason I am getting a "Type not supported" error in CURA. I've configured my setup like this: Where the # TiDB server host.
host = "0.0.0.0"
# tidb server advertise IP.
advertise-address = ""
# TiDB server port.
port = 4000
# Registered store name, [tikv, mocktikv, unistore]
store = "tikv"
# TiDB storage path.
# path = "/tmp/tidb"
path = "127.0.0.1:2379"
# (and so on)I think I'm using the right versions: In the log files, the TiKV branch is set to I'm also detecting some interaction between TiDB and TiKV in my PD logs as well. So what could I have been doing wrong? |
|
Could you give a little more details about how you got "Type not supported" from cura? Maybe the query? The way that error was reported? |
|
Hi, I've pieced some things together like so: set tidb_mem_quota_query = 343597383681;
set tidb_cura_chunk_size = 4 * 1024 * 1024;
set tidb_enable_cura_exec = 1;
set tidb_cura_support = 127;
set tidb_cura_concurrent_input_source = 0;
set tidb_cura_mem_res_type = arena;
set tidb_cura_mem_res_size = 7000 * 1024 * 1024;
set tidb_cura_exclusive_default_mem_res = 1;
set tidb_cura_stream_concurrency = 3;
set tidb_cura_enable_bucket_agg=0;
set tidb_cura_stream_concurrency=3;
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%dim%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;And then named this file time tiup bench tpch cleanup
time tiup bench tpch prepare --queries q5,q9,q17,q18
## Do an example query
time mysql -v -h 127.0.0.1 -P 4000 -u root test < path-0001.sqlThe command Here's the output: Line 17 in that file is the first |
|
I don't know if this information might be useful but the |
|
|
|
Sorry we forgot mentioning that cura doesn't support I think if you just want to see whatever query running on cura, you can try something like: Or if you intentionally want to try tpch queries, you may have to recreate tpch tables manually with the Last, tikv doesn't require cura libcura.so - cura takes care of the the rest computation after the portions pushed-down to tikv. This may also explain why you didn't see cura involved for query |
|
Hi, I hit upon a crash due to a CURA module. See the log here: https://pastebin.com/Gm5yVt5D. Query (based on q5): set tidb_mem_quota_query = 343597383681;
set tidb_cura_chunk_size = 4 * 1024 * 1024;
set tidb_enable_cura_exec = 1;
set tidb_cura_support = 127;
set tidb_cura_concurrent_input_source = 0;
set tidb_cura_mem_res_type = arena;
set tidb_cura_mem_res_size = 7000 * 1024 * 1024;
set tidb_cura_exclusive_default_mem_res = 1;
set tidb_cura_enable_bucket_agg=0;
set tidb_cura_stream_concurrency=2;
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= '1994-01-01'
and o_orderdate < date_add('1994-01-01', interval '1' year)
group by
n_name
order by
revenue desc; |
|
If anything, I used this little script to change the column data types: use test;
set GLOBAL tidb_enable_change_column_type = ON;
alter table part modify p_retailprice Double;
alter table supplier modify s_acctbal Double;
alter table partsupp modify ps_supplycost Double;
alter table customer modify c_acctbal Double;
alter table orders modify o_totalprice Double;
alter table lineitem modify l_quantity Double;
alter table lineitem modify l_extendedprice Double;
alter table lineitem modify l_discount Double;
alter table lineitem modify l_tax Double; |
|
|
Here's the cura log: Right here, as the server goes down, it abruptly ends. The server says now the error is a "bus error." It's similar to the invalid select count(*) from lineitem l join orders o on l_orderkey = o_orderkey;... works (I prepared this database using Also, I do detect some GPU usage.
Here, we see that two GPU processes have spawned. |
|
Also, sorry for the very late reply. I have had some hard business to sort out... |
|
Hi Yujin, thanks for sticking with us.
|
|
Sure, I’ll try that when I come back.
… On Jul 30, 2021, at 2:55 AM, ruoxi ***@***.***> wrote:
Hi Yujin, thanks for sticking with us.
For some reason, the tidb branch tidb_cura is not the one we ran our demo. We may have missed some fixes. I uploaded the binary used in our demo show, built on x86-64, Ubuntu 18.04, go 1.15.2. You can try it in your local.
Make sure your tables are analyzed, by running analyze table <TABLE_NAME>.
Could you give us the explain result of q5 and q17?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub <#9 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOLJVS25WQEHVYIPMK62ZNDT2JZK3ANCNFSM5AKYPYXQ>.
|
|
I still have some problems. For some reason, I'm getting this type conversion problem: This is caused whenever I issue a command like alter table part modify p_retailprice Double;The variable |
|
I think in tidb 4.0, arbitrary column type modification is an experimental feature, meaning it may not be fully implemented or is problematic. So I suggest pre-creating tables with decimal types replaced with double, then using tiup to import data (yes tiup will skip table creating if it sees tables already exist). You can refer to this script, but remember do replace decimal with double: https://github.com/pingcap/tidb-bench/blob/master/tpch/dss.sql |
|
Yeah, I did that. I used However the problem was that, somehow, For now, the only problem is the frequent crashing. In fact it only went through the whole benchmark only once which is a curiosity. |
I might try that but since the type conversion is already solved I'll set that solution aside. Thank you for you suggestion, however. |
Thanks. |
|
Meanwhile we'll try to reproduce your issue in our local. Please give us some time. Thanks. |
|
Hi, The scripts that we used are hosted in this repository: https://github.com/csm-yujinkim/tidb-experiments To use this, first: |
|
I'm using the |
|
Hi, I've uploaded some logs onto the repository I mentioned. Looks like there's a problem (bug?) with the For example, see https://github.com/csm-yujinkim/tidb-experiments/issues/11. The titles for the issues are UNIX timestamps. |
|
Hi Yujin, We have some findings:
Would you try again after you fix bullet 2 above? Thanks. |
|
Hi, Thanks for the correspondence.
This is a list of commit hashes that I'm using (abridged):
I'm still currently getting an error of the same sort. See: https://github.com/csm-yujinkim/tidb-experiments/issues/13 |
|
We are looking into it. Could you upload the corresponding tidb.log and cura.log for us? Thanks. |
|
EDIT: never mind, my uploaded tidb were lacking some changes. |
|
Yeah, the Here's some usage descriptions about the various scripts:
|
|
I've pushed some of my latest changes into that repository. Hopefully things aren't too different. |
|
We may have some clues, but need your input to confirm. Could you give us the explain result of the all four queries? You can simply add |
|
Hi, Query 5: Query 9: Query 17: Query 18: |
|
Hi Yujin, we roughly located the problem:
So before proceding to fix the bugs, I'm a little wondering what is your expectation about this experiment? If you expect a perf comparison between GPU and CPU as our article did, you may want to directly try larger data set, i.e. tpch 20G or 50G. This way the "cura sub-plan" issue may be worked around. If you expect this cura-empowered tidb to be in some production use, we couldn't promise that we can give our effort that far - we may fix the "cura sub-plan" issue, but there are way too much issues to address to make cura-empowered tidb production level. What do you think? |
|
Hi, We're researching the use of our research in the terabyte scale (at least 1TiB or so), which means that certainly we would use more than 50G. On the other hand, I had put "50" to the scale factor (?) figure in diff --git a/tpch/Makefile b/tpch/Makefile
index e5a2621..7d86c48 100644
--- a/tpch/Makefile
+++ b/tpch/Makefile
@@ -1,5 +1,5 @@
tbl: dbgen
- cd dbgen && ./dbgen -s 1
+ cd dbgen && ./dbgen -s 50
dbgen:
cd dbgen; make;
load:And then as I examine the And as such my queries (Q9) fail. How may I solve this problem? |
|
We hit that issue too. We used Note that But it really cost a lot of time. Give it a day or two. And may I ask whether you are going to use tidb or some other engine yet to integrate with cura? |
|
I see what you mean. I'll try that. However, doesn't that mean that the data type is going to be We're using TiDB as a reproducible case study of using the GPU to do database operations. Ultimately, the goal is to find different yet memory-intensive (as opposed to compute-intensive) workloads for the GPU, and CURA is a good example for us. |
Right, sorry I forgot about that. We modified the schema file https://github.com/pingcap/tidb-bench/blob/master/tpch/dss.sql by replacing
Got you. In the meantime we'll try fixing the bugs mentioned before so that you can run on small data set. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi again,
I'm trying to make sure that TiDB can work with CURA. So far, I compiled the custom TiDB executable in the
tidb_curabranch:And then, I made it go through a script like this:
However, I'm not detecting any GPU activities. Is there a way I can get the GPU--CURA engine to work?
The text was updated successfully, but these errors were encountered: