What is the mystery of turbulence?

Turbulence is indeed an unsolved problem both in physics and mathematics. Whether it is the "greatest" might be argued but for lack of good metrics probably for a long time.

Why it is an unsolved problem from a mathematical point of view read Terry Tao (Fields medal) here.

Why it is an unsolved problem from a physical point of view, read Ruelle and Takens here.

The difficulty is in the fact that if you take a dissipative fluid system and begin to perturb it for example by injecting energy, its states will qualitatively change. Over some critical value the behaviour will begin to be more and more irregular and unpredictable. What is called turbulence are precisely those states where the flow is irregular. However as this transition to turbulence depends on the constituents and parameters of the system and leads to very different states, there exists sofar no general physical theory of turbulence. Ruelle et Takens attempt to establish a general theory but their proposal is not accepted by everybody.

So in answer on exactly your questions :

yes, solving numerically Navier Stokes leads to irregular solutions that look like turbulence

no, it is not possible to solve numerically Navier Stokes by DNS on a large enough scale with a high enough resolution to be sure that the computed numbers converge to a solution of N-S. A well known example of this inability is weather forecast - the scale is too large, the resolution is too low and the accuracy of the computed solution decays extremely fast.

This doesn't prevent establishing empirical formulas valid for certain fluids in a certain range of parameters at low space scales (e.g meters) - typically air or water at very high Reynolds numbers. These formulas allow f.ex to design water pumping systems but are far from explaining anything about Navier Stokes and chaotic regimes in general.

While it is known that numerical solutions of turbulence will always become inaccurate beyond a certain time, it is unknown whether the future states of a turbulent system obey a computable probability distribution. This is certainly a mystery.

Turbulence is not one of the great unsolved problems in physics. Physics tells us exactly how turbulence emerges as a direct consequence of local mass and momentum conservation. We can create multiparticle computer models such as lattice gas automata that generate turbulence at large length and time scales. We can write down the equations that govern turbulence. These are the Navier-Stokes equations.

From a fundamental physics perspective, turbulence is a solved problem that has entered the engineering realm a long time ago.

So what is the unsolved problem associated with turbulence? In short, turbulence is an unsolved problem not in physics but in mathematics. The point is that mathematicians struggle to answer the question if the Navier-Stokes equation always allows for solutions that at fine enough length and time scales are well behaved. In fact, if you manage to conclusively answer this question ("yes" or "no"), you will win a math prize that comes with a handsome cheque of $ 1,000,000.

In case you want to give it a try, the precise question is:

Prove or give a counter-example of the following statement: In three space dimensions and time, given an initial velocity field, there exists a vector velocity and a scalar pressure field, which are both smooth and globally defined, that solve the Navier–Stokes equations.

The math difficulties have to do with the fact that turbulence emerges when the highest derivative term in the Navier-Stokes equations (the viscosity term) becomes small compared to the other terms. You can take almost any non-linear partial differential equation, and get mathematicians to cringe simply by multiplying the highest derivative term with a factor $\epsilon$ and ask about the limiting behavior of the equation when $\epsilon$ approaches zero.

Fundamental physicists shrug and continue studying real mysteries such as quantum gravity.

Re: What is the mystery of turbulence? In 1941,RA Bagnold, discussing the long-crested simple harmonic waves that arise in wind or water flows during transition – and which remain and increase in amplitude at turbulent flow rates – philosophied thus: "instead of finding chaos and disorder, the observer never fails to be amazed at a simplicity of form, an exactitude of repetition and a geometric order.” DG Thomas (Science 1964) using a layer of tiny glass beads along a cylinder in water flows found that the beads formed simple harmonic waves at transition, persisting at turbulent flow rates, relating them to Bagnold's sand waves, feeling there was a similar Physics cause.

Both resemble the accumulation of particles in a standing waves sound field as in the Kundt's tube experiment of high school physics. Bagnold's photographs show sand particles ejected perpendicular to the flow, being deposited at shallow angles on the crests, just as one mught expect if a simple harmonic standing wave sound field had developed during transition and persisted in turbulence.

A similar wave pattern is seen, outlined by periodic dilations and narrowing of the compliant walls of arteries during rapid injections of radiopaque x-ray "dyes" during rapid injections (arteriographic standing waves), as if a stationary simple harmonic sound field was created by the injection shear forces.

In 1867, Tyndall found that specific simple harmonic sounds would cause turbulence to erupt in laminar jets, concluding that the sound waves were superimposed on similar sound waves ("vibrations") created by fluid shear forces along the tube walls, amplifying them, triggering turbulence at lower flow rates. Tyndall believed that this solved the mystery of transition to turbulence. I agree with Tyndall.