Millions of nodes

The last time I looked at Views was a long time ago. It could have been Views1, I don't recall. At the time, the code contained dynamically generated SQL queries which appeared like they would run without the benefit of proper indexes. I stopped investigating Views at that point. Has that changed at all?

Ummm - you seem to be confusing an issue with a contributed module with Drupal core. I suggest filing an issue with forum_access and maybe offering up some help to incorporate your fixes.

As far as memcache -vs- Drupal's native DB cache, you need to remember that a vast majority of Drupal installations out there are on shared hosting. They don't have memcache on their servers, and in their cases the native DB caching in Drupal provides a significant performance boost. The developers did keep performance in mind by providing a mechanism where the small percentage of sites that have access to and can use other caching systems are able to. That speaks to the versatility and power of Drupal.

And what about Drupal.org using Apache solr? It shows that Drupal again can be extended for different uses and server configurations. Again - not everyone has access to Solr, so they can use the native Drupal search. Or if they don't like that or Solr they can use something else like Sphinx. The point is you will never get the performance or flexibility that these other search engines have in native MySQL. Again - Drupal is mostly installed on shared hosts without these services.

Is Drupal a wrong choice?

I wouldn't say so. Drupal has a lot of features that would probably fit nicely with your site. You probably will also have a few issues with a site that big but they are not insurmountable if you do thing sensibly.

I can renounce to Search if it's such a problem (is it enough to disable the module?).

You would need to turn off the standard search and use something like Solr which can handle millions of rows.

I would like to know which other kind of limitations i should expect.

I touch on some of these things here:
http://cruncht.com/93/drupal-troubleshooting
And some of the problems I ran into on my big site are here:
http://cruncht.com/361/uriverse-dbpedia-drupal-case-study#performance-shortcomings

To recap:
- difficult to browse content in the admin section for editing due to a join from node to user table to get back the nodes
- difficult to edit content if some of your nodes have many node references
- avoid teaser/node presentation for long lists in views if your nodes have a lot of cck properties (node references etc) as node load is slow
- the standard taxonomy page as well as the view is unusable with lots of nodes. You can switch Solr in for this.
- getting your url_alias and node indexes into mysql key buffer is crucial or your site will be deathly slow when showing lots of links as well as for simple node loads in any situation
- some modules don't work so great with large amounts of data. Sometimes it is due to dodgy joins, 'or' comparisons or just plain bad db design. Up to you to do due diligence on the modules to make sure they work for your dataset.
- the standard SQL created by views is way too slow for most queries built using the GUI. The main problem is that LEFT JOIN is used in many cases (across to the CCK tables) when an INNER would be fine. You can rewrite the SQL using a hook to fix this though.
- Be careful in general with views and all SQL. Avoiding joins in queries is a biggy as well as making sure proper indexes are being used.

I've read above zeezhao's problem with node import. Could I overcome it using Import/export api module or the import feature of Feeds module?

The way I did it isn't "right" way to do it but I resorted to writing my own SQL and PHP scripts to import the data. In the end I just couldn't be bothered to work out how to programatically create content types with schemas using CCK. Ditto for creating the nodes. However, I don't think using the Drupal mechanisms would have worked anyway owing to the size. Do you really want to suffer node load overhead 3M times? Fast loads with MySQL can get thoughputs of 10K+ rows/s. Using Drupal will obviously be factors slower than this. With 3M nodes it could mean the difference between days/weeks vs months of import time.

During initial load into MySQL make sure you do it from a file directly into the table and make sure the file is on a different disk to the DB. Speedup here is 2 to 3 factors faster than iterating and inserting. Here are some more notes on DB import stuff.

Would i face the same problems of the search module, when i for example click on a news tag to see all the news with the same tag?

Solr is very fast with filtering. I haven't done it with tags though. I'm not sure what consequences that has for the size/performance of the Solr repository. I do remember reading that more indexed fields equals more RAM in Solr. If you have 3M nodes then you could be looking at 1G of RAM (very big guestimate) to handle it. More fields may make this grow. Just flagging this as an issue.

Does the taxonomy module have the same implications of the search module for very large databases?
You can still use taxonomy with your site. Just be aware that you will probably need Solr to do cool stuff (faceted filtering) quickly.

I'm strongly interested in Views2 module too, I would use it together with extensions like charts module, tagmap, etc to visualize trends in news.

I don't know these modules so can't comment.

Are there ways to make non-greedy sql queries and avoid performance breakdowns?

Not sure what you mean by "non-greedy". Views works by firstly getting a list of node ids and then it works out how to present then (node, teaser, fields). For node and teaser a node load is done. This can be slow if nodes are heavy and lookup in DB is slowish. I found that using Fields was a big help. You still suffer two hits to the DB for each node (node and url_alias) but the data that is being loaded is much smaller. This assumes that you have some heavy nodes with lots of CCK.

I should add that it won't be a traffic intensive site, just our research team and maybe some clients (let's hope!).

The other bit of advice I seem to be dolloping out these days is that Boost will help your site a lot and take the pressure off MySQL if things are intense at the DB. Good luck with the traffic.