Sphinx + MySQL + PHP 1.2 billion DNS data query in seconds

Sphinx + MySQL + PHP 1.2 billion DNS data query in seconds

Recently, I got data from nearly 1.2 billion global ns nodes. I wanted to use it to perform a nationwide dns lookup domain name and then collect and scan nationwide websites, later, I found that the number of websites was not very accurate, and it was difficult to complete such a huge task with the energy and financial resources of a person, so I did not proceed, leaving only the building notes.

Text Format, simple text search, the speed is too slow, it takes nearly 5-10 minutes to search, it is decided to import it into the database for optimization, the speed should not be improved, the computer only has the AMP environment, so you decide to pour it into mysql,

In the beginning, Navicat is used for import. The data format is ip, and ns is used for import for nearly five hours, this is a 54G data file in plain text format!

Later I found that it took about 30 minutes to use mysql's built-in load data local infile. I forgot to create a new key during the first import, so I had to re-import it.

Mysql> load data local infile 'e: \ dns \ rite \ 20141217-rdns.txt 'into table dns
Fields terminated ',';
Query OK, 1194674130 rows affected, 1700 warnings (29 min 26.65 sec)
Records: 1194674130 Deleted: 0 Skipped: 0 Warnings: 1700

Because an id field was added, the import speed was significantly reduced, but it took about one and a half hours to complete the import of 55 GB of data.
The next step is to create an index. Because of the fuzzy query I need, the Full Text + Btree is created here. It takes about three days for the index to be created, during this period, I accidentally closed the mysql execution window and thought it would be finished. I finally found that mysql still silently created indexes in the background.
After an index is created, it is found that the query speed is a little faster than that without an index.

Select * from ns where ns like '% weibo.com'

It took 210 seconds, but it was still too slow.
Then we started to use SPhinx for index improvement,
Download the 64-bit sphsf-mysql SUPPORT package from the official website.
Configure the configuration file, and configure the mysql account and password in src.

Source src1
{
SQL _host = localhost
SQL _user = root
SQL _pass = root
SQL _db = ns
SQL Port = 3306
SQL _query = \
SELECT id, ip, ns from ns // write the query statement here
SQL _attr_uint = id
 

Then configure the port, log, and pid file path in searchd.

Searchd
{
Listen = 9312.
Listen = 9306: mysql41
Log = E:/phpStudy/splinx/file/log. log
Query_log = E:/phpStudy/splinx/file/query. log
Pid_file = E:/phpStudy/splinx/file/searchd. pid
 

Switch to the bin directory of sphinx to create an index and execute

Searchd test1 # test1 is your source name

It took me less than two hours to complete the setup,
Switch to the api directory and run

E: \ phpStudy \ splinx \ api> test. py asd
DEPRECATED: Do not call this method or, even better, use SphinxQL instead of
API
Query 'asd 'retrieved 1000 of 209273 matches in 0.007 sec
Query stats:
'Asd 'found 209291 times in 209273 documents ents
Matches:
1. doc_id = 20830, weight = 1
2. doc_id = 63547, weight = 1
3. doc_id = 96147, weight = 1
4. doc_id = 1717000, weight = 1
5. doc_id = 2213385, weight = 1
6. doc_id = 3916825, weight = 1
7. doc_id = 3981791, weight = 1
8. doc_id = 5489598, weight = 1
9. doc_id = 9348383, weight = 1
10. doc_id = 18194414, weight = 1
11. doc_id = 18194415, weight = 1
12. doc_id = 18195126, weight = 1
13. doc_id = 18195517, weight = 1
14. doc_id = 18195518, weight = 1
15. doc_id = 18195519, weight = 1
16. doc_id = 18195520, weight = 1
17. doc_id = 18195781, weight = 1
18. doc_id = 18195782, weight = 1
19. doc_id = 18200301, weight = 1
20. doc_id = 18200303, weight = 1

Tested and found that the speed was really fast. I wrote a PHP script to call it.

<? Php
Include 'sphinxapi. php ';
$ Conn = mysql_connect ('127. 0.0.1 ', 'root', 'root ');
Mysql_select_db ('ns ', $ conn );
$ Sphclient = new SphinxClient ();
$ Now = time ();
$ Sphinx-> SetServer ('127. 0.0.1 ', 127 );
$ Result = $ sphexample-> query ('weibo. com', 'test1 ');
Foreach ($ result ['matches'] as $ key => $ val ){
$ SQL = "select * from ns where id = '{$ key }'";
$ Res = mysql_query ($ SQL );
$ Res = mysql_fetch_array ($ res );
Echo "{$ res ['IP'] }:{ $ res ['ns']}";

}
Echo time ()-$ now;
?>

It basically achieves second query !, The last output time only takes 0!
 
123.125.104.176: w-176.service.weibo.com
123.125.104.178: w-178.service.weibo.com
123.125.104.179: w-179.service.weibo.com
123.125.104.207: w-207.service.weibo.com
123.125.104.208: w-208.service.weibo.com
123.125.104.209: w-209.service.weibo.com
123.125.104.210: w-210.service.weibo.com
202.106.169.235: staff.weibo.com
210.242.10.56: weibo.com.tw
218.30.114.174: w114-174.service.weibo.com
219.142.118.228: staff.weibo.com
60.28.2.221: w-221.hao.weibo.com
60.28.2.222: w-222.hao.weibo.com
60.28.2.250: w-222.hao.weibo.com
61.135.152.194: sina152-194.staff.weibo.com
61.135.152.212: sina152-212.staff.weibo.com
65.111.180.3: pr1.cn-weibo.com
160.34.0.155: srm-weibo.us2.cloud.Oracle.com
202.126.57.40: w1.weibo.vip.hk3.tvb.com
202.126.57.41: w1.weibo.hk3.tvb.com
0

This article permanently updates the link address: