[ svepomalo @ 08.01.2022. 21:17 ] @
Cao svima, imam problem sa query: Code: 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` 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 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] Code: 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 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] |