Clickhouse 数据测试新用户创建

最新推荐文章于 2025-06-30 11:21:35 发布

scaling_heights

最新推荐文章于 2025-06-30 11:21:35 发布

阅读量1.2w

点赞数

CC 4.0 BY-SA版权

分类专栏： clickhouse

本文链接：https://blog.youkuaiyun.com/jz1993/article/details/82914187

clickhouse 专栏收录该内容

3 篇文章

订阅专栏

一、数据准备：

https://yadi.sk/d/pOZxpa42sDdgm 数据下载并上传

gggggg

参考：https://blog.youkuaiyun.com/m0_37739193/article/details/79612186

数据导入被killed 头疼

[root@pcdnasdevapp03 ~]# cd /usr/local/clickhouse/
[root@pcdnasdevapp03 clickhouse]# ll
total 3509708
-rw-r--r--  1 root root       6380 Sep 28 10:31 clickhouse-client-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root   17166720 Sep 28 10:44 clickhouse-common-static-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root    4355556 Sep 28 10:30 clickhouse-debuginfo-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root      11708 Sep 28 10:28 clickhouse-server-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root       9412 Sep 28 10:26 clickhouse-server-common-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root     642608 Sep 28 11:18 clickhouse-test-18.12.17-1.el7.x86_64.rpm
drwxrwxr-x 68 root root       4096 Dec 25  2012 glibc-2.17
drwxr-xr-x 61 root root       4096 Sep 28 09:36 glibc-2.17-build
-rw-r--r--  1 root root   23189442 Sep 27 20:28 glibc-2.17.tar.gz
-rw-r--r--  1 root root    6312878 Sep 28 14:12 libicu50-5.1.1-alt6.x86_64.rpm
-rw-r--r--  1 root root    7643068 Sep 28 15:19 libicu-last-50.1.2-11.el6.remi.x86_64.rpm
drwxr-xr-x  6 1000 1000       4096 Sep 29 16:58 mwget_0.1.0.orig
-rw-r--r--  1 root root     397190 Sep 29 16:40 mwget_0.1.0.orig.tar.bz2
-rw-r--r--  1 root root 3532492212 Sep 30 03:04 ontime.csv.xz
drwxr-xr-x  2 root root       4096 Sep 30 15:21 test
drwxrwxr-x 20 1000 1000       4096 Sep 28 14:31 unixODBC-2.3.7pre
-rw-r--r--  1 root root    1669682 Sep 28 14:28 unixODBC-2.3.7pre.tar.gz
[root@pcdnasdevapp03 clickhouse]# xz -v -c -d < ontime.csv.xz | clickhouse-client –query=”INSERT INTO test.ontime FORMAT CSV”
    --- %             203.0 MiB / 5,515.5 MiB = 0.037    24 MiB/s         3:50
Killed
[root@pcdnasdevapp03 clickhouse]# egrep -i 'killed process' /var/log/messages
Sep 30 09:17:35 pcdnasdevapp03 kernel: Killed process 27022, UID 0, (clickhouse-clie) total-vm:29567580kB, anon-rss:3663452kB, file-rss:8kB
Sep 30 10:47:52 pcdnasdevapp03 kernel: Killed process 32466, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3653112kB, file-rss:64kB
Sep 30 11:07:41 pcdnasdevapp03 kernel: Killed process 1238, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3669664kB, file-rss:56kB
Sep 30 11:12:38 pcdnasdevapp03 kernel: Killed process 1524, UID 0, (clickhouse-clie) total-vm:25374300kB, anon-rss:3662096kB, file-rss:4kB
Sep 30 11:17:52 pcdnasdevapp03 kernel: Killed process 1863, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3663720kB, file-rss:32kB
Sep 30 11:27:11 pcdnasdevapp03 kernel: Killed process 2475, UID 0, (clickhouse-clie) total-vm:33767004kB, anon-rss:3681400kB, file-rss:8kB
Sep 30 14:15:05 pcdnasdevapp03 kernel: Killed process 12050, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3665208kB, file-rss:32kB
Sep 30 14:28:29 pcdnasdevapp03 kernel: Killed process 12892, UID 0, (clickhouse-clie) total-vm:29567580kB, anon-rss:3686464kB, file-rss:24kB
Sep 30 14:42:32 pcdnasdevapp03 kernel: Killed process 13743, UID 0, (clickhouse-clie) total-vm:33767004kB, anon-rss:3667920kB, file-rss:8kB
Sep 30 14:48:36 pcdnasdevapp03 kernel: Killed process 14204, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3669452kB, file-rss:52kB
Sep 30 14:53:53 pcdnasdevapp03 kernel: Killed process 14595, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3670324kB, file-rss:36kB
Sep 30 15:08:30 pcdnasdevapp03 kernel: Killed process 2040, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3677796kB, file-rss:4kB
Sep 30 17:11:11 pcdnasdevapp03 kernel: Killed process 6102, UID 0, (clickhouse-clie) total-vm:21182044kB, anon-rss:3677456kB, file-rss:40kB
[root@pcdnasdevapp03 clickhouse]#

linux程序被killed

参考：https://blog.youkuaiyun.com/shuihupo/article/details/80905641

再次执行：xz -v -c -d < ontime.csv.xz | clickhouse-client –query=”INSERT INTO test.ontime FORMAT CSV”

同时再打开一个页面我的天内存占满了

ggggggg

参考：https://clickhouse.yandex/docs/en/getting_started/example_datasets/ontime/

http://www.cnblogs.com/cyq632694540/p/7003264.html

https://www.cnblogs.com/gomysql/p/6655553.html

https://www.jianshu.com/p/e2cdbbd8ec70

修改内存溢出 out of memory

gggggggg

[root@pcdnasdevapp03 clickhouse]# cat  /proc/sys/vm/drop_caches
3
[root@pcdnasdevapp03 clickhouse]# echo 0 > /proc/sys/vm/drop_caches
[root@pcdnasdevapp03 clickhouse]# cat  /proc/sys/vm/drop_caches
0
[root@pcdnasdevapp03 clickhouse]# cd /proc/sys/vm
[root@pcdnasdevapp03 vm]#  free -lm
             total       used       free     shared    buffers     cached
Mem:          3831        140       3690          0          2         18
Low:          3831        140       3690
High:            0          0          0
-/+ buffers/cache:        120       3711
Swap:        10239        265       9974
[root@pcdnasdevapp03 vm]# cat /proc/sys/vm/oom_dump_tasks
1
[root@pcdnasdevapp03 vm]# echo 0 > /proc/sys/vm/oom_dump_tasks
[root@pcdnasdevapp03 vm]# cat /proc/sys/vm/oom_dump_tasks
0
[root@pcdnasdevapp03 vm]# cd /usr/local/clickhouse/
[root@pcdnasdevapp03 clickhouse]# ll
total 3509708
-rw-r--r--  1 root root       6380 Sep 28 10:31 clickhouse-client-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root   17166720 Sep 28 10:44 clickhouse-common-static-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root    4355556 Sep 28 10:30 clickhouse-debuginfo-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root      11708 Sep 28 10:28 clickhouse-server-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root       9412 Sep 28 10:26 clickhouse-server-common-18.12.17-1.el7.x86_64.rpm
-rw-r--r--  1 root root     642608 Sep 28 11:18 clickhouse-test-18.12.17-1.el7.x86_64.rpm
drwxrwxr-x 68 root root       4096 Dec 25  2012 glibc-2.17
drwxr-xr-x 61 root root       4096 Sep 28 09:36 glibc-2.17-build
-rw-r--r--  1 root root   23189442 Sep 27 20:28 glibc-2.17.tar.gz
-rw-r--r--  1 root root    6312878 Sep 28 14:12 libicu50-5.1.1-alt6.x86_64.rpm
-rw-r--r--  1 root root    7643068 Sep 28 15:19 libicu-last-50.1.2-11.el6.remi.x86_64.rpm
drwxr-xr-x  6 1000 1000       4096 Sep 29 16:58 mwget_0.1.0.orig
-rw-r--r--  1 root root     397190 Sep 29 16:40 mwget_0.1.0.orig.tar.bz2
-rw-r--r--  1 root root 3532492212 Sep 30 03:04 ontime.csv.xz
drwxr-xr-x  2 root root       4096 Sep 29 16:41 test
drwxrwxr-x 20 1000 1000       4096 Sep 28 14:31 unixODBC-2.3.7pre
-rw-r--r--  1 root root    1669682 Sep 28 14:28 unixODBC-2.3.7pre.tar.gz
[root@pcdnasdevapp03 clickhouse]# xz -v -c -d < ontime.csv.xz | clickhouse-client –query=”INSERT INTO test.ontime FORMAT CSV”
    --- %             212.7 MiB / 5,781.3 MiB = 0.037    30 MiB/s         3:11
Killed
[root@pcdnasdevapp03 clickhouse]# cat /proc/sys/vm/oom_dump_tasks
0
[root@pcdnasdevapp03 clickhouse]# cd /proc/sys/vm
[root@pcdnasdevapp03 vm]#  free -lm
             total       used       free     shared    buffers     cached
Mem:          3831        137       3693          0          2         17
Low:          3831        137       3693
High:            0          0          0
-/+ buffers/cache:        117       3713
Swap:        10239        266       9973
[root@pcdnasdevapp03 vm]# cd /usr/local/clickhouse/
[root@pcdnasdevapp03 clickhouse]# xz -v -c -d < ontime.csv.xz | clickhouse-client –query=”INSERT INTO test.ontime FORMAT CSV”
    --- %             212.7 MiB / 5,781.1 MiB = 0.037    31 MiB/s         3:09
Killed

一顿操作猛如虎，还是GG!!! oh my god 你强任你强，我就不跟你玩内存满了玩个毛线

参考：https://blog.youkuaiyun.com/ukgikgb/article/details/80107350

设置个新用户玩玩吧~~~

[root@pcdnasdevapp03 etc]# pwd
/etc
[root@pcdnasdevapp03 etc]# cat metrika.xml 
<yandex>
<networks>
<ip>::/0</ip>
</networks>
</yandex>
[root@pcdnasdevapp03 etc]# cd clickhouse-server/
[root@pcdnasdevapp03 clickhouse-server]# ll
total 52
-rw-r----- 1 clickhouse clickhouse 15931 Sep 30 16:08 config-preprocessed.xml
-rw-r--r-- 1 root       root       15793 Sep 29 15:09 config.xml
-rw-r--r-- 1 root       root        3882 Sep 17 23:17 server-test.xml
-rw-r----- 1 clickhouse clickhouse  5195 Sep 30 16:52 users-preprocessed.xml
-rw-r--r-- 1 root       root        5182 Sep 30 16:52 users.xml
[root@pcdnasdevapp03 clickhouse-server]# pwd
/etc/clickhouse-server
[root@pcdnasdevapp03 clickhouse-server]# cat users.xml
<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
<use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>
    <users>
        <default>
            <password></password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default> 
        <dba>
            <password_sha256_hex>6de608c69f21b017a6148f31cfae59aa0cc1e5abfc174c752b0c0abfd3274936</password_sha256_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
               <database>test</database>
            </allow_databases>
        </dba> 
        <readonly>
            <password></password>
            <networks incl="networks" replace="replace">
                <ip>::1</ip>
                <ip>127.0.0.1</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
        </readonly>
    </users>
    <quotas>
        <default>
            <interval>
                <duration>3600</duration>
                <queries>0</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </default>
    </quotas>
</yandex>
[root@pcdnasdevapp03 clickhouse-server]#

dba随便命名

密码是参照：https://www.cnblogs.com/gomysql/p/6708796.html

[root@pcdnasdevapp03 ~]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
3iD5SG6L
e6cfd8b39bd6c6503a5486e9d7ff23ccca72b1876a7ccdb6e3fb2f8e6a927476

仅输入PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

生成

密码：3iD5SG6L

密文：e6cfd8b39bd6c6503a5486e9d7ff23ccca72b1876a7ccdb6e3fb2f8e6a927476

[root@pcdnasdevapp03 clickhouse-server]# clickhouse-client -u dba -h 127.0.0.1 --password 3iD5SG6L
ClickHouse client version 18.12.17.
Connecting to 127.0.0.1:9000 as user dba.
Connected to ClickHouse server version 18.12.17 revision 54407.
pcdnasdevapp03 :)

客户端连接成功！

用idea在连接下

ggggggggggg