Drupal misused the database. For Drupal 7 I made the apdqc module for async prefetch MySQL. no deadlocks makes it easy to scale, load balance more Apache servers in front. DB size was 100gb; well into millions of rows of nodes; all logged in users. Site I made this for has been up for years; Fix the database layer and Drupal 7 will scale like crazy.