Hi Folks, I am in the process of developing an online RSS aggregator site, http://www.kedoya.com . The site is currently in beta stage and is painfully slow due to large database. Due to its nature, Kedoya database grow by 10 MB everyday and will be 4 GB in size by the end of this year. What is the best way to drammatically speed up my website other than restructuring the database design and indexes? Thx a million!
Hi coolcow, I would strongly recommend you look at database partitioning (http://dev.mysql.com/tech-resources/articles/performance-partitioning.html). Database partitioning lets you split a very large table into a number of smaller tables. You could partition your feeds by feed category, score, or date and time (or any combination that makes sense) and that will reduce the query time. Best of all, MySQL will re-write your query for you so you don't have to tell it which partition to use! HTH
If you don't want to change anything as fas as structure is concerned, you probably need to adjust your configuration file. There's a point at which config changes aren't going to do anything else. I would make sure that all of your table structures and indexes are perfectly optimized before you rule out making any changes with them. It's very common to find a poorly designed / no index which is single-handedly causing the problems. What hardware do you have it on?
Optimizing application, database and database server is the best bet. Did you try MySQL query cache feature? Application level cache can increase your performance as well.
Thx Petey. Can database partioning combined with http://danga.com/memcached/ to increate the speed of the database further?
Hi coolcow, I haven't worked with memcache myself so I'm only working from what I have Googled but I reckon that it would work very well with MySQL partitions. I would partition the MySQL database by year+month (using the MySQL date extract function). This gives me a performance improvement and makes it very easy to manage the database in the long term (I prune old data by dropping partitions NOT by running expensive delete queries). I would disable the MySQL query cache. If the database is frequently adding new data then there is a good chance that the MySQL query cache will be stale most of the time. Queries would be slower in this scenario as each query would have the additional overhead of refreshing the cache so I switch it off and use the memory saved for memcache instead. I would implement memcache and set it's cache to expire every 30 minutes. This gives me the best of both - I get the benefits of caching while I 'drip feed' new data into my database. The first query after the cache expires is going to be the slowest so I might schedule a task in cron to run that query on the server rather than ask a site visitor to run it. Petey
I too have that problem. I have a freaking two page website: http://free5dayfatloss.com/ with almost no pictures, and only jpeg banner, and it takes a long pause before loading. I wish I could fix this