imam problem sa query:
Code:
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all` FORCE INDEX(`phone_number`) WHERE `phone_number` != ''
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all` FORCE INDEX(`phone_number`) WHERE `phone_number` != ''
Jednostavno vrti preko 30 minuta i nista.
Sa i bez force index je isto.
Ovaj ispod radi, za 15 minuta:
Code:
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all`
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all`
Ovo je create table:
Code:
CREATE TABLE `fb_all` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`source` enum('poshmark','fb-raid','linkedin-nz','wishbone','500px','chatbooks','hurb','wattpad','geniusu','havenly','eatstreet','romwe','vakinha','you-now','mathway','houzz','glofox','meetmindful','newmeet','fb-nz','fb-france','pizap') NOT NULL DEFAULT 'poshmark',
`fb_id` varchar(255) NOT NULL DEFAULT '',
`username` varchar(255) NOT NULL DEFAULT '',
`first_name` varchar(255) NOT NULL DEFAULT '',
`last_name` varchar(255) NOT NULL DEFAULT '',
`gender` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`phone_number` varchar(255) NOT NULL DEFAULT '',
`country` varchar(5) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fb_id` (`fb_id`),
KEY `country` (`country`),
KEY `source` (`source`),
KEY `first_name` (`first_name`),
KEY `last_name` (`last_name`),
KEY `gender` (`gender`),
KEY `email` (`email`),
KEY `phone_number` (`phone_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1351044264 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `fb_all` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`source` enum('poshmark','fb-raid','linkedin-nz','wishbone','500px','chatbooks','hurb','wattpad','geniusu','havenly','eatstreet','romwe','vakinha','you-now','mathway','houzz','glofox','meetmindful','newmeet','fb-nz','fb-france','pizap') NOT NULL DEFAULT 'poshmark',
`fb_id` varchar(255) NOT NULL DEFAULT '',
`username` varchar(255) NOT NULL DEFAULT '',
`first_name` varchar(255) NOT NULL DEFAULT '',
`last_name` varchar(255) NOT NULL DEFAULT '',
`gender` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`phone_number` varchar(255) NOT NULL DEFAULT '',
`country` varchar(5) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fb_id` (`fb_id`),
KEY `country` (`country`),
KEY `source` (`source`),
KEY `first_name` (`first_name`),
KEY `last_name` (`last_name`),
KEY `gender` (`gender`),
KEY `email` (`email`),
KEY `phone_number` (`phone_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1351044264 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
Table size:
Data: 97.4 GiB
Indez: 256.2 GiB
Overhead: 5.0 MiB
Effective: 353.5 GiB
Total: 353.5 GiB
Total rows: 1.3B
SHOW ENGINE INNODB STATUS\G kaze:
Code:
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-01-08 21:30:39 0x7f179ca5d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 2279 srv_idle
srv_master_thread log flush and writes: 2282
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2625458
OS WAIT ARRAY INFO: signal count 11510392
RW-shared spins 9205463, rounds 81113515, OS waits 2259218
RW-excl spins 14923191, rounds 44498289, OS waits 343314
RW-sx spins 716, rounds 3064, OS waits 25
Spin rounds per wait: 8.81 RW-shared, 2.98 RW-excl, 4.28 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 719221981
Purge done for trx's n:o < 719221980 undo n:o < 0 state: running but idle
History list length 1475
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421214367043968, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421214367039752, ACTIVE 1737 sec fetching rows
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 397, OS thread handle 139739389703936, query id 3280 localhost root Sending data
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all` FORCE INDEX(`phone_number`) WHERE `phone_number` != '' INTO OUTFILE '/storage/mysql-tmp/fb_all_phone_numbers.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
Trx read view will not see trx with id >= 719221981, sees < 719221981
---TRANSACTION 421214367035536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
5892849 OS file reads, 1796412 OS file writes, 80952 OS fsyncs
159.61 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 323697, seg size 323699, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 26560697, node heap has 0 buffer(s)
Hash table size 26560697, node heap has 2 buffer(s)
Hash table size 26560697, node heap has 10 buffer(s)
Hash table size 26560697, node heap has 2 buffer(s)
Hash table size 26560697, node heap has 6 buffer(s)
Hash table size 26560697, node heap has 830927 buffer(s)
Hash table size 26560697, node heap has 0 buffer(s)
Hash table size 26560697, node heap has 0 buffer(s)
54091.30 hash searches/s, 14571.14 non-hash searches/s
---
LOG
---
Log sequence number 7649617274630
Log flushed up to 7649617274630
Pages flushed up to 7649617274630
Last checkpoint at 7649617274621
0 pending log flushes, 0 pending chkp writes
11803 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 109662175232
Dictionary memory allocated 230112
Buffer pool size 6553200
Free buffers 8
Database pages 5722245
Old database pages 2112148
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 143871, not young 20904642
1609.80 youngs/s, 68932.36 non-youngs/s
Pages read 5886618, created 133, written 1744492
159.61 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 6 / 1000 not 291 / 1000
Pages read ahead 0.00/s, evicted without access 369.88/s, Random read ahead 0.00/s
LRU len: 5722245, unzip_LRU len: 0
I/O sum[128400]:cur[1096], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 819200
Free buffers 1
Database pages 714792
Old database pages 263838
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20886, not young 2702892
161.95 youngs/s, 10281.24 non-youngs/s
Pages read 736758, created 5, written 213386
23.33 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 4 / 1000 not 284 / 1000
Pages read ahead 0.00/s, evicted without access 48.32/s, Random read ahead 0.00/s
LRU len: 714792, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 819100
Free buffers 1
Database pages 715918
Old database pages 264254
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19915, not young 2462413
186.27 youngs/s, 1194.94 non-youngs/s
Pages read 737850, created 0, written 217618
2.67 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 9 / 1000 not 62 / 1000
Pages read ahead 0.00/s, evicted without access 41.65/s, Random read ahead 0.00/s
LRU len: 715918, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 819200
Free buffers 1
Database pages 715578
Old database pages 264128
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24310, not young 3905492
261.58 youngs/s, 10440.85 non-youngs/s
Pages read 747390, created 0, written 219265
24.99 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 12 / 1000 not 482 / 1000
Pages read ahead 0.00/s, evicted without access 40.32/s, Random read ahead 0.00/s
LRU len: 715578, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 819100
Free buffers 1
Database pages 715244
Old database pages 264005
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21720, not young 2851110
255.25 youngs/s, 11781.41 non-youngs/s
Pages read 740378, created 0, written 221689
26.99 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 6 / 1000 not 321 / 1000
Pages read ahead 0.00/s, evicted without access 54.65/s, Random read ahead 0.00/s
LRU len: 715244, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 819200
Free buffers 1
Database pages 715154
Old database pages 263972
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16864, not young 2448903
299.57 youngs/s, 20642.79 non-youngs/s
Pages read 730911, created 0, written 222647
47.98 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 7 / 1000 not 519 / 1000
Pages read ahead 0.00/s, evicted without access 67.98/s, Random read ahead 0.00/s
LRU len: 715154, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 819100
Free buffers 1
Database pages 715217
Old database pages 263995
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 22067, not young 3105290
268.91 youngs/s, 4080.31 non-youngs/s
Pages read 742048, created 0, written 216997
9.66 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 12 / 1000 not 193 / 1000
Pages read ahead 0.00/s, evicted without access 26.66/s, Random read ahead 0.00/s
LRU len: 715217, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 819200
Free buffers 1
Database pages 715252
Old database pages 264008
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12613, not young 2033369
86.64 youngs/s, 5672.44 non-youngs/s
Pages read 727460, created 64, written 211000
13.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 2 / 1000 not 146 / 1000
Pages read ahead 0.00/s, evicted without access 44.99/s, Random read ahead 0.00/s
LRU len: 715252, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 819100
Free buffers 1
Database pages 715090
Old database pages 263948
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5496, not young 1395173
89.64 youngs/s, 4838.39 non-youngs/s
Pages read 723823, created 64, written 221890
11.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 3 / 1000 not 209 / 1000
Pages read ahead 0.00/s, evicted without access 45.32/s, Random read ahead 0.00/s
LRU len: 715090, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=16085, Main thread ID=139627702925056, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 520216113
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 68667.78 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.001 sec)[b][/b]
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-01-08 21:30:39 0x7f179ca5d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 2279 srv_idle
srv_master_thread log flush and writes: 2282
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2625458
OS WAIT ARRAY INFO: signal count 11510392
RW-shared spins 9205463, rounds 81113515, OS waits 2259218
RW-excl spins 14923191, rounds 44498289, OS waits 343314
RW-sx spins 716, rounds 3064, OS waits 25
Spin rounds per wait: 8.81 RW-shared, 2.98 RW-excl, 4.28 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 719221981
Purge done for trx's n:o < 719221980 undo n:o < 0 state: running but idle
History list length 1475
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421214367043968, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421214367039752, ACTIVE 1737 sec fetching rows
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 397, OS thread handle 139739389703936, query id 3280 localhost root Sending data
SELECT COUNT(DISTINCT `fb_id`) FROM `fb_all` FORCE INDEX(`phone_number`) WHERE `phone_number` != '' INTO OUTFILE '/storage/mysql-tmp/fb_all_phone_numbers.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
Trx read view will not see trx with id >= 719221981, sees < 719221981
---TRANSACTION 421214367035536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
5892849 OS file reads, 1796412 OS file writes, 80952 OS fsyncs
159.61 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 323697, seg size 323699, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 26560697, node heap has 0 buffer(s)
Hash table size 26560697, node heap has 2 buffer(s)
Hash table size 26560697, node heap has 10 buffer(s)
Hash table size 26560697, node heap has 2 buffer(s)
Hash table size 26560697, node heap has 6 buffer(s)
Hash table size 26560697, node heap has 830927 buffer(s)
Hash table size 26560697, node heap has 0 buffer(s)
Hash table size 26560697, node heap has 0 buffer(s)
54091.30 hash searches/s, 14571.14 non-hash searches/s
---
LOG
---
Log sequence number 7649617274630
Log flushed up to 7649617274630
Pages flushed up to 7649617274630
Last checkpoint at 7649617274621
0 pending log flushes, 0 pending chkp writes
11803 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 109662175232
Dictionary memory allocated 230112
Buffer pool size 6553200
Free buffers 8
Database pages 5722245
Old database pages 2112148
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 143871, not young 20904642
1609.80 youngs/s, 68932.36 non-youngs/s
Pages read 5886618, created 133, written 1744492
159.61 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 6 / 1000 not 291 / 1000
Pages read ahead 0.00/s, evicted without access 369.88/s, Random read ahead 0.00/s
LRU len: 5722245, unzip_LRU len: 0
I/O sum[128400]:cur[1096], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 819200
Free buffers 1
Database pages 714792
Old database pages 263838
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20886, not young 2702892
161.95 youngs/s, 10281.24 non-youngs/s
Pages read 736758, created 5, written 213386
23.33 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 4 / 1000 not 284 / 1000
Pages read ahead 0.00/s, evicted without access 48.32/s, Random read ahead 0.00/s
LRU len: 714792, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 819100
Free buffers 1
Database pages 715918
Old database pages 264254
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19915, not young 2462413
186.27 youngs/s, 1194.94 non-youngs/s
Pages read 737850, created 0, written 217618
2.67 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 9 / 1000 not 62 / 1000
Pages read ahead 0.00/s, evicted without access 41.65/s, Random read ahead 0.00/s
LRU len: 715918, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 819200
Free buffers 1
Database pages 715578
Old database pages 264128
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24310, not young 3905492
261.58 youngs/s, 10440.85 non-youngs/s
Pages read 747390, created 0, written 219265
24.99 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 12 / 1000 not 482 / 1000
Pages read ahead 0.00/s, evicted without access 40.32/s, Random read ahead 0.00/s
LRU len: 715578, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 819100
Free buffers 1
Database pages 715244
Old database pages 264005
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21720, not young 2851110
255.25 youngs/s, 11781.41 non-youngs/s
Pages read 740378, created 0, written 221689
26.99 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 6 / 1000 not 321 / 1000
Pages read ahead 0.00/s, evicted without access 54.65/s, Random read ahead 0.00/s
LRU len: 715244, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 819200
Free buffers 1
Database pages 715154
Old database pages 263972
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16864, not young 2448903
299.57 youngs/s, 20642.79 non-youngs/s
Pages read 730911, created 0, written 222647
47.98 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 7 / 1000 not 519 / 1000
Pages read ahead 0.00/s, evicted without access 67.98/s, Random read ahead 0.00/s
LRU len: 715154, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 819100
Free buffers 1
Database pages 715217
Old database pages 263995
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 22067, not young 3105290
268.91 youngs/s, 4080.31 non-youngs/s
Pages read 742048, created 0, written 216997
9.66 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 12 / 1000 not 193 / 1000
Pages read ahead 0.00/s, evicted without access 26.66/s, Random read ahead 0.00/s
LRU len: 715217, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 819200
Free buffers 1
Database pages 715252
Old database pages 264008
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12613, not young 2033369
86.64 youngs/s, 5672.44 non-youngs/s
Pages read 727460, created 64, written 211000
13.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 2 / 1000 not 146 / 1000
Pages read ahead 0.00/s, evicted without access 44.99/s, Random read ahead 0.00/s
LRU len: 715252, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 819100
Free buffers 1
Database pages 715090
Old database pages 263948
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5496, not young 1395173
89.64 youngs/s, 4838.39 non-youngs/s
Pages read 723823, created 64, written 221890
11.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 3 / 1000 not 209 / 1000
Pages read ahead 0.00/s, evicted without access 45.32/s, Random read ahead 0.00/s
LRU len: 715090, unzip_LRU len: 0
I/O sum[16050]:cur[137], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=16085, Main thread ID=139627702925056, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 520216113
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 68667.78 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.001 sec)[b][/b]
Code:
root@server ~ # mysql --version
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@server ~ # mysql --version
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Code:
#
# * Fine Tuning
#
#key_buffer_size = 16M
max_allowed_packet = 256M
#innodb_flush_log_at_trx_commit = 2
#thread_stack = 192K
#thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
max_connections = 4000
innodb_buffer_pool_size = 100G
innodb_log_file_size = 1024M
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
#
# * Fine Tuning
#
#key_buffer_size = 16M
max_allowed_packet = 256M
#innodb_flush_log_at_trx_commit = 2
#thread_stack = 192K
#thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
max_connections = 4000
innodb_buffer_pool_size = 100G
innodb_log_file_size = 1024M
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
RAM je 128GB
Imam prostora na disku.
Jel zna neko zasto ovako?
Sta mogu da uradim?
[Ovu poruku je menjao svepomalo dana 08.01.2022. u 22:30 GMT+1]