中央银行 大佬有话说 :
大型网盘系统,MySQL8.0和5.7选择哪一个?
本帖最后由 中央银行 于 2023-6-28 09:40 编辑
数据库有138.6MB,每天IP在一万左右。
这是我服务器上运行sysbench的基准测试结果
sysbench –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=******* –db-driver=mysql –mysql-db=tester /usr/share/sysbench/oltp_read_write.lua –threads=16 –time=300 –report-interval=10 –rand-type=uniform run
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads…
Threads started!
[ 10s ] thds: 16 tps: 424.68 qps: 8523.91 (r/w/o: 5967.93/1705.02/850.96) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 486.12 qps: 9714.14 (r/w/o: 6802.71/1939.19/972.24) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 489.99 qps: 9802.81 (r/w/o: 6860.17/1962.76/979.88) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 463.25 qps: 9262.09 (r/w/o: 6485.30/1850.20/926.60) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 498.09 qps: 9963.95 (r/w/o: 6973.00/1994.77/996.19) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 513.21 qps: 10260.97 (r/w/o: 7184.02/2050.53/1026.42) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 487.40 qps: 9757.41 (r/w/o: 6827.70/1954.90/974.80) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 486.20 qps: 9723.49 (r/w/o: 6806.69/1944.40/972.40) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 490.20 qps: 9799.90 (r/w/o: 6861.10/1958.40/980.40) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 476.20 qps: 9523.73 (r/w/o: 6667.12/1904.21/952.40) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 476.40 qps: 9528.19 (r/w/o: 6668.79/1906.60/952.80) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 479.50 qps: 9591.53 (r/w/o: 6714.32/1918.21/959.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 16 tps: 452.90 qps: 9050.57 (r/w/o: 6335.78/1808.99/905.80) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 16 tps: 497.79 qps: 9954.25 (r/w/o: 6966.72/1991.95/995.57) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 16 tps: 468.91 qps: 9377.60 (r/w/o: 6564.44/1875.44/937.72) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 16 tps: 500.80 qps: 10022.50 (r/w/o: 7017.60/2003.20/1001.70) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 16 tps: 502.89 qps: 10064.04 (r/w/o: 7042.49/2015.77/1005.78) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 16 tps: 466.17 qps: 9313.34 (r/w/o: 6519.34/1861.87/932.13) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 16 tps: 445.04 qps: 8902.41 (r/w/o: 6231.37/1780.96/890.08) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 477.40 qps: 9553.84 (r/w/o: 6689.13/1909.71/955.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 472.20 qps: 9440.07 (r/w/o: 6609.78/1885.89/944.40) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 489.76 qps: 9793.17 (r/w/o: 6854.42/1959.23/979.52) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 16 tps: 509.34 qps: 10184.57 (r/w/o: 7128.21/2037.67/1018.69) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 511.10 qps: 10225.00 (r/w/o: 7158.60/2044.20/1022.20) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 483.50 qps: 9666.71 (r/w/o: 6766.31/1933.40/967.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 519.80 qps: 10398.89 (r/w/o: 7277.79/2081.50/1039.60) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 464.40 qps: 9280.75 (r/w/o: 6497.93/1854.01/928.80) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 484.19 qps: 9685.49 (r/w/o: 6779.96/1937.16/968.38) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 464.61 qps: 9292.12 (r/w/o: 6504.38/1858.62/929.11) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 464.80 qps: 9305.17 (r/w/o: 6513.68/1861.79/929.70) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 2022818
write: 577948
other: 288974
total: 2889740
transactions: 144487 (481.57 per sec.)
queries: 2889740 (9631.36 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0333s
total number of events: 144487
Latency (ms):
min: 5.51
avg: 33.22
max: 240.93
95th percentile: 54.83
sum: 4799864.43
Threads fairness:
events (avg/stddev): 9030.4375/39.43
execution time (avg/stddev): 299.9915/0.01
根据提供的sysbench测试数据,我们可以进行以下分析:
SQL统计信息:
– 查询执行次数:
– 读取(read):2022818次
– 写入(write):577948次
– 其他(other):288974次
– 总计(total):2889740次
– 事务数:144487个(平均每秒481.57个)
– 查询数:2889740个(平均每秒9631.36个)
– 忽略的错误数:0个(平均每秒0.00个)
– 重新连接次数:0次(平均每秒0.00次)
总体统计信息:
– 总计时间:300.0333秒
– 事件总数:144487个
延迟统计(以毫秒为单位):
– 最小值:5.51ms
– 平均值:33.22ms
– 最大值:240.93ms
– 第95百分位延迟:54.83ms
– 总和:4799864.43ms
线程公平性:
– 事件数(平均值/标准差):9030.4375 / 39.43
– 执行时间(平均值/标准差):299.9915 / 0.01
根据这些数据,我们可以得出以下结论:
1. 总体性能:在测试期间,系统平均每秒处理481.57个事务和9631.36个查询。这些指标可以帮助评估系统的处理能力。
2. 延迟:平均延迟为33.22ms,最大延迟为240.93ms。第95百分位延迟为54.83ms。延迟较低的系统通常具有更好的响应性能。
3. 线程公平性:根据事件数和执行时间的平均值和标准差,可以评估系统中线程的负载均衡情况。较低的标准差表示线程之间的负载分布较为均衡。
这些数据只提供了测试结果的概览,如果你需要更详细的分析或有其他方面的问题,请提供更多的上下文或数据,我将尽力帮助你。
秦风戈 大佬有话说 :
20G之前不用考虑MySQL的问题,20G之后分库分表,主从复制,读写分离也能解决
talaon 大佬有话说 :
我以为138T
why? 大佬有话说 :
就这点数据没所谓, 你可以前面加个redis 全部 缓存起来
leassy 大佬有话说 :
你这点数据用sqlite都没问题哒https://cdn.jsdelivr.net/gh/master-of-forums/master-of-forums/public/images/patch.gif
hoshino 大佬有话说 :
5.7足够了
zhongziso 大佬有话说 :
什么网盘?地址发一下
ansheng 大佬有话说 :
用PostgreSQL