We have an inventory of 400,000 items on Magento. Daily we add around 10,000 new items which we add using Magmi (a mass importer plugin) in a single operation. Our inventory is also available on Marketplace platforms such as eBay and Amazon so updates to quantity are of a time critical nature to avoid listing items that have sold elsewhere. For this reason we upload quantity updates every half hour.
The issue we have is basically that Magento forces us to reindex all after every update that we run, the indexing time on the updates takes over 30 minutes meaning that Magento is constantly reindexing (as soon as reindexing is complete the next update is ready and uploaded and reindexing begins again). While reindexing the website performance is badly affected and it makes it virtually unusable. I should note that when doing a quantity update we do not use the reindexall php call, only catalog_product_price.
I am sure there must be a way to only reindex items that have been modified or are new since the last reindex operation. This would speed things up dramatically. Each half hourly update has only 1500 quantity modifies and if we could find a way to only reindex these, the whole process should only take a matter of seconds.
So far we have tried
increasing the database server size
increased query_cache_size to 32M in my.cnf
added query_cache_limit to 2m in my.cnf
increased innodb_buffer_pool size to 1024 in my.cnf ( have a 2gb RAM server)
increased php_value memory_limit from 64m to 256m in .htaccess file in root of Magento webserver
Might be worth mentioning we have a seperate Database and App server for Magento