Performance of WITH tables that later get joined to smaller tables

I’m trying to develop a general framework for getting chunked cohorts out of a large database. Because of various factors, it’s convenient to write queries like this:

with tab as (
    select
            whatever
        ,   id
    from whereever

    union all

    select
            whatever
        ,   id
    from whereever2
)
select
        t.whatever
    ,   t.id
from tab as t
inner join cohort as c on c.id = t.id

Now, I’m aware that most databases rewrite your query for efficiency, “under the hood.” If they didn’t, then this query would pull everything from whereever, and then filter it using the join below. This would be inefficient.

Alternatively, I could do this:

and then:

select
        whatever
    ,   id
from whereever
inner join cohort on w.id = cohort.id

union all

select
        whatever
    ,   id
from whereever2
inner join cohort on w.id = cohort.id

This one is uglier, repetitious and harder to write programmatically.

In principle, which should be faster, and WHY?

If it matters, I’m on a MS SQL database, but I’d be interested to know whether the answer would vary with postgres.

c# – slow loop performance with larger date ranges

I have the following algorithm to calculate certain ratios of “work”, “low work” and “no work” over a set date range (startDate, endDate). The algorithm works fine, but when i increase the date range it obviousely runs slower because it has to loop over every day of that range for every minute. Is there a way to improve the performance with a different kind of loop or for the where clause to find the current date within the listWorkTime

private List<DateSharedWork> CalculateDateSharedWork(DateTime startDate, DateTime endDate, ICollection<WorkTime> listWorkTime)
        {

      List<DateSharedWork> listDateSharedWork = new List<DateSharedWork>();

      // +1 to include last day at full
      int range = endDate.Subtract(startDate).Days + 1;

      // start at startDate
      Parallel.For(0, range, i =>
      {

        DateTime currDate = startDate.AddDays(i);

        //set minute interval
        double everyNMinutes = 1.0;
        double minutesADay = 1440.0;

        // reset counter
        int work_counter = 0;
        int lowWork_counter = 0;
        int noWork_counter = 0;

        int l = (int)(minutesADay / everyNMinutes);

        for (int j = 0; j < l; j++)
        {

          DateTime check15 = currDate.AddMinutes(j * everyNMinutes);

          // check if listWorkTime includes current date
          var foundTime = listWorkTime.Where(x => check15 >= x.FromDate && check15 <= x.ToDate).ToList();

          if (foundTime.Count(x => x.TimeRangeId == 1) > 0)
          {
            // found interval that is within work hours
            work_counter++;
            noWork_counter++;
          }
          else
          {
            if (foundTime.Count(x => x.TimeRangeId == 2) > 0)
            {
              // found intervall that is within low work hours
              lowWork_counter++;
              noWork_counter++;

            }
          }
        };

        double work = everyNMinutes / minutesADay * work_counter;
        double lowWork = everyNMinutes / minutesADay * lowWork_counter;
        double noWork = 1.0 - (everyNMinutes / minutesADay * noWork_counter);

        listDateSharedWork.Add(new DateSharedWork(currDate, work, lowWork, noWork));

      });

      listDateSharedWork.Sort((x, y) => DateTime.Compare(x.Date, y.Date));

      return listDateSharedWork;

    }

performance – Why does my site load slower on the first attempt from an incognito browser?

Chrome’s incognito window uses a fresh cache, so you could say that Chrome has two caches – a “normal” cache and an “incognito” cache. When you close ALL incognito windows and tabs, the incognito cache gets cleared. With default Chrome settings, the normal cache persists across windows and browsing sessions.

It takes extra time for your site to load in incognito because your browser is re-downloading a lot of the page’s resources that it would otherwise have already cached, because it’s working with a fresh incognito cache.

You can improve load times for repeat visitors by making sure your site’s resources are being served with long-life cache headers where appropriate, so the browser won’t re-download them for a long time.

You can improve load times for first-time visitors by making sure your site’s resources are lean, so don’t include tons of JavaScript, images, or other things that slow down page load – or load them on demand with e.g. the loading="lazy" image attribute or async and defer JavaScript attributes. This may come down to going lean on plugins.

You can improve load times for both by making sure your server hardware is fast (i.e. not on over-provisioned shared hosting) and your server software is lean (e.g. not bogged down by too many plugins).

charts – How to avoid performance issues in user-customizable dashboards by limiting in some way the amount of information being displayed

I work for a Product that allows users to create customs dashboards. They have the possibility to create 1-25 custom charts and the available chart types are indicators, columns, bars, area, pie, and line.

The problem that we are currently facing is that some users create dashboards with dimensions that not only bring an insane amount of data which causes performance issues but also are really hard to read and analyze.

I would like to receive some advice about how to:

  • Reduce the data that we render in the charts.
  • Limit the users so they don’t create no-sense reports.

Thanks in advance,

rendering – Z-Fighting mitigation performance overhead

Which of following approaches will have less performance overhead(using threejs renderer)?

I am currently have an issue with z-fight for distant objects, while my near/far plane is already set to most optimal setting to show close and far objects. I am thinking to apply some more advanced techniques for z-fighting. Also I’ve check depth configuration, and it is set to 24 bits already by default.

I am still planning to do performance testing to see which will work better. However I would ask a question in advance, if anyone already had an experience with that, and can suggest which of the approaches has less performance overhead.

More details about both approaches can be found here: https://cesium.com/blog/2018/05/24/logarithmic-depth/

  1. Most simples approach from coding perspective, is to enable logarithmicDepthBuffer like on manual bellow https://threejs.org/docs/#api/en/renderers/WebGLRenderer.depth – and I am planning to start prototyping using it, however the drawback it will disable this https://www.khronos.org/opengl/wiki/Early_Fragment_Test kind of optimization.
  • My concern if I will start scaling my scene with more object and shaders, it could be a performance issue then.
  1. So I was thinking about alternative approach, so called Multi-frustum rendering – basic idea is to render same scene multiple time, with different near/far plan setting without clearing color buffer.
  • It will keep Early Fragment Test enabled
  • However this approach is not for free, I have to render my scene multiple time.
  • I still can do some extra-optimization, by rendering only certain object for certain near/far plan settings.

What approach should have less performance overhead for the same scene?

php – Building a pre-loader for performance gains in WordPress

As the title, I am building a pre-loader for WordPress that will deliver performance boosts in loading, as well as metrics such as Google Page Speed.

Now, this is something that we do at work, and I could quite easily copy what has been used there but feel it is ‘stolen knowledge’. I could be writing anything and have very little understanding of what I am typing as it was developed by seniors.

So, given that I know what the basics of the pre-loader are (load critical styles, defer certain scripts, remove unwanted WP scripts) I had a bash at creating one myself that appears to work:

// If there are problems with caching, 
// change this version number
define('CACHE_VERSION', '1.0.0');

class WpboilerInliner {

    function __construct() {
        add_action( 'init', array(&$this, 'init') );
        add_action( 'wp_head', array(&$this, 'addCriticalCss') );
        add_action( 'wp_footer', array(&$this, 'addGeneralCss') );
    }

    // This will add the critical CSS to the header
    function addCriticalCss() {
        // Set to not load in the admin as this will 'break' it
        if(!is_admin()) {
            $criticalFonts = '<link rel="preconnect" href="https://fonts.googleapis.com">
                              <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
                              <link href="https://fonts.googleapis.com/css2?family=Poppins:ital,wght@0,200;0,300;1,300&display=swap" rel="stylesheet"> ';
    
            $criticalCSSContent = file_get_contents( get_template_directory_uri() . '/css/atf.css' );
            $criticalCSS = "<style type='text/css'>
                                <!-- BEGIN CRITICAL STYLES -->
                                {$criticalCSSContent}
                                <!-- END CRITICAL STYLES -->
                            </style>";

            echo $criticalFonts . $criticalCSS;

        }
    }

    // General styles, these will be added in the footer
    function addGeneralCss() {

        // Add the filename to be added to the footer(below the fold) here
        // Add files in their correct cascade order
        // e.g filename.css
        // filename.min.css 
        // subdirectory/filename.css
        $generalCssFileName = array(
            'general.css',
            'type.css',
        );

        foreach($generalCssFileName as $cssFileName) {
            $linkFormat = '<link rel="stylesheet" href="' . get_template_directory_uri() . '/css/%s?ver=%s" />';
        
            $cssLink = sprintf($linkFormat, $cssFileName, CACHE_VERSION);

            echo $cssLink;
        }
    }

    function init() {

        // Remove everything to do with emojis
        remove_action( 'wp_head', 'print_emoji_detection_script', 7 );
        remove_action( 'admin_print_scripts', 'print_emoji_detection_script' );
        remove_action( 'wp_print_styles', 'print_emoji_styles' );
        remove_action( 'admin_print_styles', 'print_emoji_styles' );

        remove_filter( 'the_content_feed', 'wp_staticize_emoji' );
        remove_filter( 'comment_text_rss', 'wp_staticize_emoji' );
        remove_filter( 'wp_mail', 'wp_staticize_emoji_for_email' );

        add_filter( 'tiny_mce_plugins', 'disable_emojis_tinymce' );
        add_filter( 'wp_resource_hints', 'disable_emojis_remove_dns_prefetch', 10, 2 );

        // Remove version number from header
        remove_action( 'wp_head', 'wp_generator' );

        remove_action( 'wp_head', 'wlwmanifest_link');
        remove_action( 'wp_head', 'rsd_link' );

        // Removes shortlink
        remove_action( 'wp_head', 'wp_shortlink_wp_head' );

        // Removes feed links 
        remove_action( 'wp_head', 'feed_links', 2 );

        // // Removes comments feed 
        remove_action( 'wp_head', 'feed_links_extra', 3 );

        /**
         * Filter function used to remove the TinyMCE emoji plugin 
         * 
         * @param array $plugins 
         * @return array Difference between the two arrays
        */
        function disable_emojis_tinymce( $plugins ) {

            if( is_array( $plugins ) ) {
                return array_diff( $plugins, array( 'wpemoji' ) );
            } else {
                return array();
            }

        } 
        
        /**
         * Remove emoji CDN hostname from DNS prefetching hints
         * 
         * @param array $urls URLs to print for resource hints 
         * @param string $relation_type The relation type the URLs are printed for 
         * @return array Difference between the two arrays 
        */
        function disable_emojis_remove_dns_prefetch( $urls, $relation_type ) {

            if( 'dns-prefetch' == $relation_type ) {
                /** This filter is documented in wp-includes/formatting.php */
                $emoji_svg_url = apply_filters( 'emoji_svg_url', 'https://s.w.org/images/core/emoji/2/svg/' );

                $urls = array_diff( $urls, array( $emoji_svg_url ) );
            }

            return $urls;

        }

        // Load JS files
        wp_enqueue_script('wpboiler-critical-js', get_template_directory_uri() . '/js/atf.min.js', array(), CACHE_VERSION, false);
        wp_enqueue_script('wpboiler-general-js', get_template_directory_uri() . '/js/general.min.js', array(), CACHE_VERSION, true);
 
    }

}

$wpboilerInliner = new WpboilerInliner();

Are there ways of improving this? It has not yet been tested with plugins, but on a basic install it seems to work as expected (from what I can tell). I came into an issue with the admin area when trying to load in the fonts and the critical styles which is why they are wrapped in !is_admin(). Functions disable_emojis_tinymce() and disable_emojis_remove_dns_prefetch() were taken from a post by Kinsta.

Are there methods I can improve here? Are there some major errors that I am likely to encounter with what I have currently written? My PHP knowledge is limited so I understand some of the more basic concepts such as sprintf and the basic use of arrays and loops.

Regolithmedia Since 2010 – High Performance Premium SSD VPS, 34 Locations From $8/Month

REGOLITH MEDIA – Your All In One Business Solution.
Since 2010 We already serve thousands transactions and client all around the world. Join US! You will discover services suitable for your needs.


Code:

News! You can Access Regolithmedia Website by enter "reg.uno" in your browser Check out our website to discover our other service Like SSD Shared Hosting, Cheap Domain, Cheap SSL, And Premium Cloud VPS

PROMOTION
All Plan Bonus Free Rego’s Web Optimization Worth > $500
Premium SSD VPS SP1

Get 10% Discount on 512MB Plan, use “10OFF“, Discount recurring as long as you renew for monthly payment.

Premium SSD VPS

Get 10% Discount on 1GB and 2GB plan, use “RegExp10“, Discount recurring as long as you renew for monthly payment.

Premium SSD VPS
Locations

1. Jakarta, Indonesia located on CBN Indonesia Data Centre(Ipv6 Support)

2. Hong Kong, China Located On Equinix Data centre

3. Singapore, Singapore Located on Ascenix Singapore Data Centre (Ipv6 Support)

4. Sydney, Australia Located on Equinix Data centre (Ipv6 Support)

5. Buffalo, New York(Central USA) located on Colocrossing Datacenter

6. Dallas, Texas (Central USA ) located on Colocrossing Datacenter

7. Los Angeles, California(West Coast ) located on Colocrossing Datacenter(Asia Optimized IP)

8. Chicago, Illinois (Central USA ) located on Colocrossing Datacenter

9. SCDallas, Texas (Central USA ) located on Psychz Data cente(Ipv6 Support)

10. Toronto, Canada Located On Prioritycolo Data centre(Ipv6 Support)

11. Amsterdam, Netherlands (Amsterdam) located on Serverius datacenter (Ipv6 Support)

12. Docklands, England located on Connexions4London datacenter(Ipv6 Support)

13. Maidenhead, England located on Iomart datacenter (Ipv6 Support)

14. Frankfurt, Germany located on First Colo datacenter (Ipv6 Support)

15. Dublin, Ireland located on Digiweb Datacenter (Ipv6 Support)

16. Warsaw, Poland located on Equinix Datacenter

17. Melbourne, Australia Located on Equinix Data centre

18. Tokyo, Japan Located On Equinix Asia Datacenter

19. Dubai, Dubai Located On Equinix United Arab Emirates Data centre

20. Vienna, Austria Located On FirstColo Poland Data centre(Ipv6 Support)

Dallas 512MB 1024MB 2048MB 4096MB Custom (For 4GB need or above)
Dedicated Core 1 Dedicated CPU Cores @ Intel Xeon E5-2660 OR Xeon Gold 1 Dedicated CPU Cores @ Intel Xeon E5-2660 OR Xeon Gold 2 Dedicated CPU Cores @ Intel Xeon E5-2660 OR Xeon Gold 2 Dedicated CPU Cores @ Intel Xeon E5-2660 OR Xeon Gold ? Dedicated CPU Cores @ Intel Xeon E5-2660 OR Xeon Gold
Disk Space 9GB SSD Space @ Raid-10 Enterprise-grade SSD 14GB SSD Space @ Raid-10 Enterprise-grade SSD 29GB SSD Space @ Raid-10 Enterprise-grade SSD 29GB SSD Space @ Raid-10 Enterprise-grade SSD ?GB SSD Space @ Raid-10 Enterprise-grade SSD
Premium Bandwidth 750GB Data Transfer @ 250Mbps 1.5TB Data Transfer @ 250Mbps 3TB Data Transfer @ 250Mbps 6TB Data Transfer @ 250Mbps ?TB Data Transfer @ 250Mbps
Backup Storage 12.5GB Backup Storage(Snapshot backup) 25GB Backup Storage(Snapshot backup) 50GB Backup Storage(Snapshot backup) 50GB Backup Storage(Snapshot backup) ?GB Backup Storage(Snapshot backup)
Ram & Swap 512MB Ram DDR4+ 1024 MB Swap 1024MB Ram DDR4 + 1024 MB Swap 2048MB Ram DDR4 + 1024 MB Swap 4096MB Ram DDR4 + 1024 MB Swap ?MB Ram DDR4 + 1024 MB Swap
Control Panel & 100% Uptime Warranty Yes Yes Yes Yes Yes
Choices Of 20 locations No Yes Yes Yes Yes
Free Cloudflare Railgun (Worth $200/month) Yes Yes Yes Yes Yes
IPv4 & Ipv6 1 IPv4 Addresses & Ipv6 Available & Internal Ip(Private Lan @1Gbps) 1 IPv4 Addresses & Ipv6 Available & Internal Ip(Private Lan @1Gbps) 1 IPv4 Addresses & Ipv6 Available & Internal Ip(Private Lan @1Gbps) 1 IPv4 Addresses & Ipv6 Available & Internal Ip(Private Lan @1Gbps) ? IPv4 Addresses & Ipv6 Available & Internal Ip(Private Lan @1Gbps)
Price $10.77/Month $19.5/Month $24/Month $51/Month $?/Month
ORDER NOW ORDER NOW ORDER NOW ORDER NOW Contact US

Can’t Order? create ticket to sales we will help you to order
Buy in Bulk? Contact US, We can Arrange something special for you

Premium SSD VPS SP1
Locations

1. Miami, Florida(West Coast USA) located on Total Server Solution Datacenter

2. Dallas, Texas (Central USA ) located on Total Server Solution Datacenter

3. Los Angeles, California(West Coast ) located on Total Server Solution Datacenter

4. Chicago, Illinois (Central USA ) located on Total Server Solution Datacenter

5. New York City, New York (East Coast USA) located on Telehouse Datacenter(Ipv6 Support)

6. Salt Lake City, Utah(West Coast USA) located on Softlayer Datacenter

7. Seattle (West Coast USA) located on Total Server Solution Datacenter

8. Atlanta, Georgia (East Coast USA) located on Total Server Solution Datacenter

9. Phoenix (West Coast USA) located on Total Server Solution Datacenter

10. Toronto, Canada Located On Total Server Solution Data centre

11. Vancouver, Canada Located On Total Server Solution Data centre

12. London, England located on UK2 datacenter(Ipv6 Support)

13. Amsterdam, Netherlands located on Total Server Solution datacenter(Ipv6 Support)

SP1 512MB SP1 1024MB SP1 2048MB SP1 4096MB SP1 8192MB SP1 16384MB
Dedicated Core 1 Dedicated CPU Cores @ Intel Xeon E5-2660 4 Dedicated CPU Cores @ Intel Xeon E5-2660 4 Dedicated CPU Cores @ Intel Xeon E5-2660 4 Dedicated CPU Cores @ Intel Xeon E5-2660 4 Dedicated CPU Cores @ Intel Xeon E5-2660 4 Dedicated CPU Cores @ Intel Xeon E5-2660
Disk Space 15GB SSD Space @ Raid-10 25GB SSD Space @ Raid-10 50GB SSD Space @ Raid-10 100GB SSD Space @ Raid-10 200GB SSD Space @ Raid-10 400GB SSD Space @ Raid-10
Premium Bandwidth 1TB Data Transfer @ 1Gbps 3TB Data Transfer @ 1Gbps 4TB Data Transfer @ 1Gbps 5TB Data Transfer @ 1Gbps 6TB Data Transfer @ 1Gbps 7TB Data Transfer @ 1Gbps
Ram & Swap 512MB DDR4 Ram + 1024 MB Swap 1024MB DDR4 Ram + 1024 MB Swap 2048MB DDR4 Ram + 1024 MB Swap 4096MB DDR4 Ram + 1024 MB Swap 8192MB DDR4 Ram + 1024 MB Swap 16384MB DDR4 Ram + 1024 MB Swap
IPv4 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses
Free SSl & 100% Uptime Warranty Yes Yes Yes Yes Yes Yes
Location: US, UK, NL, CA Yes Yes Yes Yes Yes Yes
No Control Panel, Root access Only Yes Yes Yes Yes Yes Yes
Price $10/Month $19/Month $35/Month $65/Month $125/Month $245/Month
ORDER NOW ORDER NOW ORDER NOW ORDER NOW ORDER NOW ORDER NOW

Premium CLOUD VPS SP1 >> New Line Product
Locations

London City, England located on Amito UK datacenter

Twogb Fourgb Sixgb Eightgb
Dedicated Core 2 Dedicated CPU Cores @ Intel Xeon E5-2660 2 Dedicated CPU Cores @ Intel Xeon E5-2660 2 Dedicated CPU Cores @ Intel Xeon E5-2660 2 Dedicated CPU Cores @ Intel Xeon E5-2660
Disk Space 48GB SSD Space @ Raid-10 48GB SSD Space @ Raid-10 48GB SSD Space @ Raid-10 48GB SSD Space @ Raid-10
Premium Bandwidth 3TB Data Transfer @ 250mbps 6TB Data Transfer @ 250mbps 9TB Data Transfer @ 250mbps 12TB Data Transfer @ 250mbps
Ram & Swap 2048MB DDR4 Ram + 2048 MB Swap 4096MB DDR4 Ram + 2048 MB Swap 6144MB DDR4 Ram + 2048 MB Swap 8192MB DDR4 Ram + 2048 MB Swap
IPv4 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses 1 IPv4 Addresses
Free SSl & 100% Uptime Warranty Yes Yes Yes Yes
Location: UK Yes Yes Yes Yes
Control Panel Yes Yes Yes Yes
Price $18/Month $42/Month $70/Month $90/Month
ORDER NOW ORDER NOW ORDER NOW ORDER NOW

Can’t Order? create ticket to sales we will help you to order
Buy in Bulk? Contact US, We can Arrange something special for you

Payment

-Paypal (USD)

-Local Bank : BCA(IDR)


REGOLITH MEDIA – Provide Business Solution Since 2010

How does MacOS decide when to use M1’s performance vs. efficiency cores?

How does MacOS decide when to use M1’s performance vs. efficiency cores? – Ask Different

LeetCode 05, Manacher’s algorithm. What causes so poor performance (time and space)?

I finished LeetCode 05 with simplified (for ease of implementation) Manacher’s algorithm. IMHO it should keeps O(n) time and space complexity. However, LeetCode’s benchmark ranked my solution as worst 5% and 30%. May anybody helps me diagnose where it went wrong?

Code:

data class LimitInfo(var center: Int, var rightMost: Int)

class Solution {
    fun longestPalindrome(s: String): String {
        val newS = s.fold("#") { acc, c -> "$acc$c#" }
        val radii = MutableList(newS.length) { 0 }
        val limitInfo = LimitInfo(0, 0)

        fun symRadius(center: Int, checkedRadius: Int): Int {
            var radius = checkedRadius + 1
            while (newS.getOrNull(center - radius)?.equals(newS.getOrNull(center + radius)) == true) radius += 1
            return radius - 1
        }

        newS.indices.forEach { center ->
            val space = limitInfo.rightMost - center
            val checkedRadius = when {
                space < 0 -> 0
                else -> minOf(radii(limitInfo.center * 2 - center), space)
            }

            val radius = symRadius(center, checkedRadius)

            radii(center) = radius

            if (center + radius > limitInfo.rightMost) {
                limitInfo.rightMost = center + radius
                limitInfo.center = center
            }

        }

        val res = radii.withIndex().maxBy { it.value }!!.let { (loc, radius) ->
            newS.slice(loc - radius..loc + radius)
        }.filterIndexed { index, _ -> index % 2 == 1 }
        return res
    }
}
```

performance – I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)

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 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 :/

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies 5000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive.com Proxies-free.com New Proxy Lists Every Day Proxies123.com Buy Cheap Private Proxies; Best Quality USA Private Proxies