[Help] Cấu hình MySQL xử lý >= 50M records

Chào ae,

Số là mấy hôm nay crawl và insert scraped data vào CSDL dữ liệu MySQL. Bây giờ bảng dữ liệu này (InnoDB) hơn 50 triệu records rồi, như hình sau:


Mặc dù vẫn tiếp tục insert được data vào bảng này, tuy nhiên mình ko thể thực thi câu lệnh truy vấn SELECT (mình chạy lệnh SQL khi kết nối trực tiếp với mysql trên hệ điều hành Ubuntu, do thời gian thực thi cực lâu, chờ hoài ko thấy nên mình stop command luôn). Mình đã chỉnh cấu hình MySQL để điều chỉnh một số thông số cho InnoDB. Cú pháp select như sau:

Cấu hình my.cnf đã điều chỉnh một số thông số:

Anh em có kinh nghiệm chỉ giúp với!

Thanks,

 

Hóng cái cho biết sau này còn xử lý. DB lớn nhất của mình còn chưa đc 1 triệu

 

Chậm do query chứ ko phải cấu hình. Order 50mil rows chỉ dựa trên ram và cpu thì phải máy khá khủng mới nhanh nổi. Rẻ tiền thì bỏ chất xám ra.

 

select order 1 phát 50M rows cơ à bác. sao bác không chia nhỏ nó ra nhỉ, thực ra có mấy khi dùng hết 50M rows 1 lúc đâu

 

Đánh index chửa

 

Order by id thì chắc là index rồi

 

Bác dùng câu lệnh "between" id ấy. Chứ limit là nó lâu trùm luôn

 

Xin cám ơn các anh em đã tư vấn hỗ trợ,

Thanks anh, em đã chạy được mấy câu lệnh SELECT sau khi optimize table. Tuy nhiên tốc độ truy vấn chưa như kỳ vọng.

Khi dùng MySQL between thì có lẽ bạn thường sẽ count(*) để biết tổng rows, sau đó thì random SELECT đoạn giữa hay thế nào vậy?

DB này chứa các scraped posts từ các victim sites. Khi mình tách data cho từng site của mình (mỗi site mỗi DB nhỏ hơn) để users truy cập thì tự nhiên số lượng rows sẽ thấp hơn 300k rows để truy vấn cho nhanh hơn.

Đã đánh index rồi.

Đoạn SELECT mà mình thường dùng nhiều nhất là:

Ghi chú:
+ Trường random chứa các số ngẫu nhiên từ 0-255, trường status cũng là số để phân loại một số thứ.
+ Mấy fields với WHERE hoặc ORDER thì mình toàn cho index hết.

Hiện tại, tốc độ truy vấn của câu lệnh SELECT 1 thì tầm <10ms, tuy nhiên với câu SELECT 2 thì tầm 40-120s. Hiện tại, mình chưa dùng Redis để cache SELECT 2 (tương lai sẽ áp dụng), có cách nào để tối ưu SELECT 2 để tốc độ truy vấn thấp hơn ko anh em?

Mình tò mò cú pháp thêm keyword EXPLAIN vào trước SELECT 2 thì tốc độ truy vấn cực nhanh và có field rows, mình lấy dữ liệu field rows này trong cú pháp EXPLAIN SELECT count(*) from <table> thì tuyệt vời.

 

theo https://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows:
InnoDB ko lưu row counts nên mỗi lần query sẽ phải đếm lại. có lẽ giải pháp khả thi nhất chỉ có thể là cache thôi

hoặc bác có thể đọc thêm https_stackoverflow_com /questions/19267507/how-to-optimize-count-performance-on-innodb-by-using-index, em lười chưa đọc

 

Mình vừa xem được một giải pháp cũng rất ổn, đó là dùng SQL EXPLAIN SELECT count(*) from <table>, bạn lấy dữ liệu từ field rows của kết quả trả về nhé! Và mình vừa xem url thứ 2 của bạn cũng nói vấn đề cú pháp EXPLAIN mà mình đề cập. Thanks,

 

bác report lại tốc độ nhé, em không dùng mysql nhưng cũng hóng phát

 

Select count(*) của MySQL càng chậm khi db càng lớn. Thường chỉ dùng khi 2 cases sau xảy ra cùng lúc là: db dc insert/delete với tần suất rất cao (số rows không ổn định) VÀ cần đếm số rows chính xác tuyệt đối ngay lúc query.
Còn lại, đếm rows count từ information_schema nhanh hơn nhiều. Cú pháp chi tiết search thêm nhé.

 

Thanks anh Andy,
SQL: EXPLAIN SELECT count(*) from <table> cũng tương tự như select từ information_schema.INNODB_SYS_TABLESTATS.NUM_ROWS về tốc độ truy xuất tương đồng và dữ liệu cũng thuộc mức tương đối chính xác đó anh ^^

 

Để lúc nào ngồi máy anh test thử xem.

 

Ban thu select count(id) from table, thay vi * nhe
Neu dung * thi no se kiem tra tat ca cac column, thay vi su dung id da duoc index.

(xin loi vi bo go tieng viet tren may dang bi hong)

 

Nếu table sử dụng InnoDB engine thì lấy trường NUM_ROWS nên SQL là: SELECT * FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = '<db_name>/<table_name>'
Lưu ý: có dấu / giữa db_name và table_name ^^ Hình như 2 cú pháp này nó từ 1 nguồn dữ liệu nên nhanh giống nhau, nhưng nếu db write quá nhiều thì dữ liệu này mang tính tương đối thôi.

Thanks bạn, nhưng nếu dữ liệu tầm trên 10M thì việc select * hoặc cụ thể từng trường ko có tác dụng gì nhiều lắm khi dùng với hàm COUNT(), nó sẽ có tác dụng tăng tốc khi truy vấn thông thường ấy.

 

Bạn hiểu bản chất của việc count là đếm những trường dữ liệu != Null, khi count(*) đồng nghĩa với việc trong row có bất kỳ dữ liệu khác null được đếm là 1. Việc Id đã được index, và mặc định là có nên chạy rất nhanh, thay vì phải check tất cả các trường khác

Bạn có thể tham khảo ở đây. https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count

Về bản chất dữ liệu lớn tầm cả chục chiệu bản ghi là rất dễ để đánh giá việc này và cho bạn một kinh nghiệm rõ ràng nhất.

 

Count theo id cũng chậm lắm, làm tầm chục tr rows là thấy ngay chứ test trên vài trăm K rows ko thấy đâu.

 

Thấy cái câu lệnh của bác như dùng để phân trang. Còn dùng câu lệnh nào cũng vẫn phải count mà. Nếu count nó chậm quá thì bác lúc bác insert vào db thì lưu ra file text 1 số ID lớn nhất. Lần sau chỉ việc đọc file đỡ phải count.

 

Mình ít dùng count rows để phân trang, mình phân trang theo kiểu previous page và next page thôi nên ko cần dữ liệu count rows. Mình dùng count rows để thống kê là chính.

Ở các posts trên mình đã tìm được số count rows tương đối chính xác đối với các db có lượng write IO nhiều. Nên dùng cách đó thay cho việc ghi file, đọc file số Last ID mà bạn đề cập, vì số này sai số lớn hơn rất nhiều so với số liệu rows đang lưu trữ.