1

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000.

  • I am using Centos 7.9 and Mysql 5.7 Community Server.
  • The database is around 40-60 GB on each server, all tables are InnoDB
  • Total records are 100 million urls split on 3 servers, 100 million "links", 100 million url_meta.
  • 13 million domains, etc.
  • Each table is around 15 GB on each server (links, urls, url meta, etc).
  • CPUs are Ryzen 5 3600 with 12 threads
  • 64 GB ram on each server
  • nvme SSDs:
  • 1x GIGABYTE GP-ASM2NE6500GTTD 500GB (not "enterprise" type)
  • 2x KINGSTON SEDC1000BM8480G 480GB "enterprise" nvme.

My biggest concern now is that the Gigabyte nvme shows as 30% wear, after just 3 months. The kingston enterprise ones says they are at 2%

raid command smartctl says I read around 10 TB and I wrote around 70 TB on each nvme.

I had my innodb_buffer_pool_size set to around 1 GB if I remember correctly, I increased it now and forgot previous value :confused:

The crawler constantly reads urls to crawl from the table of 30-40 million records on each server, sorts them by last crawl date ASC, reads remote url content, updates url_meta in database (title, description, etc). Updates links found in that url, link titles, etc.

This quickly makes the tables very fragmented and unless I run "optimize table", they return queries very slowly.

I tried creating a copy of 2-3 most important tables and only update that once a week, and use it for reads, so it remains defragmented. This is when I noticed the worn SSD, the 2 servers with enterprise Kingston nvme completed (copy +optimize tables) in 3 hours, the Gigabyte one in 9 hours.

A search query in the crawler is returned in 10 seconds using the "live" fragmented tables, vs around 0.2 seconds after tables are optimized/defragmented.

What should I do in order to optimize this and avoid destroying the nvmes ?

I am thinking eider:

  1. try a hardware setup with HDDs and only use the nvme SSDs for read-only cache. Do I have a chance to run all these queries from HDDs ?
  2. optimize all the caching options in order to write to disk as rarely as possible. Can I get tips on this please ?
  3. just use a SSD with more TBW ?

For my second option ... I am not familiar with the tuning options at all, which ones should I look into besides innodb_buffer_pool_size ? 32GB out of the 64 GB is a good start for this situation ? And I seen there are some options that control how often the cached data is "flushed" / written to SSD ? Can I get some info on this please ? Ideally I would like it to use the ram as much as possible and write very rarely to SSD. Losing data is not a huge deal but I would lose time while crawling it again.

If I switch to HDDs because of all the write commands, would the 64 GB memory help ? Or will the queries become unusable slow ? I seen a raid card with flash cache and HDDs is faster than HDDs alone, but raid card flash cache worns just like the SSDs, no ?!

I am kind of lost :/

Improve this question

2 Answers 2

Reset to default
4

InnoDB + SSD ==> no fragmenatation worth noting ==> Don't use OPTIMIZE TABLE. What you claim to experience in this area does not make sense.

innodb_buffer_pool_size should be about 70% of the RAM after accounting for your other apps. (1G is too low.) This may be part of the performance issue you are seeing.

OPTIMIZE TABLE ==> lots of extra writes ==> wear out SSDs without wear-laveling.

This may help: innodb_doublewrite = OFF. It poses a slight risk of data corruption, depending on the details of the OS and SSD.

This may help: sync_binlog = OFF

Are you using autocommit = ON? Or regularly using BEGIN...COMMIT? I suggest using the latter around the various inserts/updates for each page being processed. (I assume that involves several writes to several tables.)

If you have some spinning drive space handy, consider moving "logs" to there. (The old wisdom was that the 'sequential' nature of logs was well-suited to HDD. I don't know if it is still true now, but it would save some of the "wear" of the SSDs.)

Turn off the Query Cache.

For further analysis: http://mysql.rjweb.org/doc.php/mysql_analysis

Higher QPS

For the revised QPS:

  • Use "enterprise" SSDs
  • Set buffer_pool as big as possible without swapping
  • do the BEGIN..COMMIT (where practical)
  • look for any "slow" queries during the spikes. (During a spike, the system is at risk of getting 'stuck' due to the rising number of connections that seem to never terminate. Speeding up a long-running query may be a quick fix. Look for such now as a prophylactic.
Improve this answer
6
  • autocommit was on in variables, so that is what I am using (default). Did both answers say that fragmentation do not happen when using SSD ? Could that be an index fragmentation rather than data fragmentation ? Because I do see faster results after OPTIMIZE table_name and before I run inserts/updates again.
    – adrianTNT
    Commented Sep 5, 2021 at 2:05
  • Fragmentation does happen in both the Data and Index BTrees. But it is not worth using the sledgehammer called "Optimize Table".
    – Rick James
    Commented Sep 6, 2021 at 0:48
  • I turned back the processes and was very wrong about queries per seconds (I updated the question). Each server has 3k-4k queries per second non-stop (short spikes to 12k-14k). Just in case you think this new info requires any update on your answer. Thank you.
    – adrianTNT
    Commented Sep 6, 2021 at 14:45
  • @adrianTNT - I added to my Answer.
    – Rick James
    Commented Sep 6, 2021 at 19:05
  • One more question: with my case and setting buffer_pool_size to ~48GB, can I rely on default values for things like query_cache_size, query_cache_limit, sort_buffer_size ? Or do I need to tune these too ?
    – adrianTNT
    Commented Sep 6, 2021 at 20:13
3

First, I would recommend using enterprise-grade storage devices, regardless of which type. They are significantly more reliable. Your high-traffic usage is going to wear out any consumer-grade device.

You haven't described any of the queries that run slowly. In my experience, there's little difference in performance between a fragmented table and an optimized table. The non-optimized table takes more storage space, but since InnoDB internally stores records as long linked lists and does a lot of random access anyway, it doesn't really help much if rows are stored sequentially.

RAM is orders of magnitude lower latency even than NVMe storage devices, so it would be better to hold more data in RAM than to worry about which type of storage device you use.

Increase RAM allocation: increase innodb_buffer_pool_size to at least 10% of your tablespace size. Right now you have 1GB, which is 1/60th of the tablespace size. Your queries are likely to be disk-bound, frequently swapping InnoDB pages in and out of the buffer pool.

Since you have plenty of RAM on the server, you might as well allocate a lot more than 10% of your tablespace. I'd suggest 50% of your tablespace size, or about 30GB, and see if performance improves.

You should also make sure to optimize queries well with indexes. This a complex subject. You might like my presentation: How to Design Indexes, Really (video). The idea is to use indexes to narrow down searches instead of scanning large sets of data one row at a time.

Improve this answer
11
  • Did both answers say that fragmentation do not happen when using SSD ? Could that be an index fragmentation rather than data fragmentation ? Because I do see faster results after OPTIMIZE table_name and before I run inserts/updates again.
    – adrianTNT
    Commented Sep 5, 2021 at 2:05
  • Regarding slow queries, I don't have specific queries that run slowly, "normal" ones take a few seconds when table is not optimized, this is most important one: SELECT * FROM links WHERE match(link_text) against('blue socks' IN BOOLEAN MODE) LIMIT 2000 (against 35 million records). And I have 9 indexes (link_date, link_text, link_sharding, link_maintenance_date, etc). Tracking many things, maybe here is my problem.
    – adrianTNT
    Commented Sep 5, 2021 at 2:08
  • 2
    Thanks for the updated information, but my answer remains the same. Scanning data in RAM is still much better than scanning data on storage, so try to size your buffer pool to hold as much of your frequently-accessed data as possible. This probably does not need to be the whole dataset. Most applications access data in a non-uniform way; a minority of the data serves a majority of the requests (see the Pareto principle).
    – Bill Karwin
    Commented Sep 6, 2021 at 17:03
  • 2
    Set query_cache_size=0 and query_cache_type=0. It's a bad feature, it causes more performance problems than benefits. They have removed the query cache in MySQL 8.0.
    – Bill Karwin
    Commented Sep 6, 2021 at 20:16
  • 1
    As for sort_buffer_size, this is not easy to answer. It depends on the queries you run, and whether your queries cause sorting without the aid of an index. Also depends on how many rows your query needs to sort. So there is no single answer for all cases. Here's an example of a blog trying to analyze the benefit of changing sort_buffer_size: percona.com/blog/2010/10/25/…
    – Bill Karwin
    Commented Sep 6, 2021 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.