# Help, I am burning trough my NVME SSDs, 60GB MySQL databases (web crawler) **URL:** \ **Category:** General Questions\ **Created:** [September 4, 2021, 2:54am UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035 "2021-09-04T02:54:08Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![adrianTNT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adriantnt/32/4409_2.png) [@adrianTNT](https://forums.percona.com/u/adrianTNT)\ **Post date:** [September 4, 2021, 2:54am UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/1 "2021-09-04T02:54:08Z")
I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 queries per second (each server) non-stop (I might be wrong, I had to stop it for now). > 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 “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 😕 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 🥴 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 6, 2021, 4:23pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/3 "2021-09-06T16:23:11Z")
> I had my innodb\_buffer\_pool\_size set to around 1 GB This is your main problem. The buffer pool holds both read-cache and is the staging area for writes. So, if this buffer is too small, you will lots of little writes when the pool needs to make room. The buffer pool should be 80-90% of your total system RAM, but not larger than your active dataset. With 64GB of RAM, I would set this to 56GB, leaving 8GB for OS/filesystem buffers. Next, make sure innodb\_log\_file\_size matches 1/2 of your writes MB/hr (yes, per hr). > A search query in the crawler is **returned in 10 seconds** This sounds like you are not correctly indexed. Use EXPLAIN on all of your SELECTs to determine if you see ‘ALL’ anywhere in the results. If so, this is full-table scan and you need some indexes. ---
**Author:** ![adrianTNT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adriantnt/32/4409_2.png) [@adrianTNT](https://forums.percona.com/u/adrianTNT)\ **Post date:** [September 6, 2021, 5:23pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/5 "2021-09-06T17:23:35Z")
Can’t I edit the initial question ? After starting my cron jobs again, I seen it was actually 3k-4k queries per second non-stop, not 200 as I initially posted. This seems important. **I have 9 indexes** (link\_date, link\_text, link\_sharding, link\_maintenance\_date, etc). Tracking many things, **maybe here is my problem**. The above indexes are for links table, this is most important query: `SELECT * FROM links WHERE match(link_text) against('blue socks' IN BOOLEAN MODE) LIMIT 2000` I increased the `innodb_buffer_pool_size` to 32 GB (out of 64 GB available), I have some memcached data too, I will keep an eye to see if I can increase more. `Explain` for above query returned this: | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra | | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | SIMPLE | links | _NULL_ | fulltext | link\_text | link\_text | 0 | const | 1 | 100.00 | Using where; Ft\_hints: no\_ranking, limit = 2000 | | ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 6, 2021, 5:42pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/6 "2021-09-06T17:42:30Z")
The more indexes you have the more WRITE activity will happen per INSERT/UPDATE/DELETE. Only have indexes for specific queries. If an index is not used by any queries, remove it. You can also change innodb\_flush\_log\_at\_trx\_commit=2 to change flush to disk every 1s instead of on every commit. ---
**Author:** ![adrianTNT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adriantnt/32/4409_2.png) [@adrianTNT](https://forums.percona.com/u/adrianTNT)\ **Post date:** [September 6, 2021, 5:53pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/7 "2021-09-06T17:53:34Z")
Thanks, I will also try the `innodb_flush_log_at_trx_commit=2`, Can I make it “flush to disk” even less often ? e.g every few minutes/hours ? If I lose data is not a big deal. I added many indexes like for example `link_domain` so I quickly select all links to a given domain. But this select runs in background, is not critical to be fast, but I thought it makes it go trough less data. Do you think I should NOT add indexes for similar situations ? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 6, 2021, 9:37pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/8 "2021-09-06T21:37:47Z")
You can set innodb\_flush\_log\_at\_timeout=X where X is number of seconds between flushes. Values between 1 and 2700 are allowed. I’ve never heard of anyone making this larger than 3 so you are in uncharted waters. Indexes are important for reading. Without them, you’ll have to scan/read from disk more data than necessary. But indexes add writing overhead. It’s a balance you have to determine for yourself. ---
**Author:** ![adrianTNT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adriantnt/32/4409_2.png) [@adrianTNT](https://forums.percona.com/u/adrianTNT)\ **Post date:** [September 6, 2021, 9:41pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/9 "2021-09-06T21:41:48Z")
I seen many tuning parameters contain `log_` in them (like `innodb_flush_log_at_timeout`), do these strictly refer to saving/buffering the log files or it refers to actual data too ? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 7, 2021, 4:12am UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/10 "2021-09-07T04:12:27Z")
actual data. “log” in this context refers to innodb’s redo logs. ---
**Author:** ![adrianTNT](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/adriantnt/32/4409_2.png) [@adrianTNT](https://forums.percona.com/u/adrianTNT)\ **Post date:** [September 7, 2021, 8:16pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/11 "2021-09-07T20:16:49Z")
I hope that’s my last question … 🙂 Can you tell me what software is used to create the actual chart images of sysbench benchmarks like attached ? ![sysbench_image1.2](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/0/0b47297b12910272499395c42349f4ac30edb492.jpeg) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 7, 2021, 9:20pm UTC](https://forums.percona.com/t/help-i-am-burning-trough-my-nvme-ssds-60gb-mysql-databases-web-crawler/12035/12 "2021-09-07T21:20:50Z")
I don’t know. I’ve never made graphs like that.