Posted by shanefjordan on September 18, 2008 at 1:30pm
We are running RHEL5.1, Apache2, MySql5, PHP5 and have run into an issue. Our site runs find with normal usage, but if you hold the refresh (F5) key, then the site will crash. We have gotten it to the point that it will not crash the server, but it will take a while before the page responds (for any user). I know that this does not happen on other sites, so we are most likely missing a configuration setting somewhere. Here is what we have:
Apache
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 2
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 40
MaxClients 40
MaxRequestsPerChild 2000Mysql
max_connections = 800
max_user_connections = 800
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 32M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16MAnyone have any ideas?
Thanks,
Shane
Comments
"Crash"?
Well, you have to define what "crash" is better.
We know the end result, the server dies, but you need to run top and show processlist to see what the server is doing when it crashes. Does it run out of CPU? Does it thrash? Is it MySQL that dies? ...etc.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
We have just narrowed it
We have just narrowed it down. It looks like MySQL processes are just sitting there sleeping.
Sounds like perhaps physical
Sounds like perhaps physical memory is getting used up and it's starting to swap processes. How much RAM is on the server? What does the top command say when it's "crashing"
Solution?
What is the solution to this? I am having the same issue. How would you prevent someone from 'jamming' the server by hammering the refresh key?
DoS Attack
What you describe is called a "denial of service" attack; a pretty well known internet phenomena:
http://en.wikipedia.org/wiki/Denial-of-service_attack
Non-maliciously, you might know it as a "slashdotting":
http://en.wikipedia.org/wiki/Slashdotted
In the case of the latter, that's what high-performance is all about: keeping your site up and running even when it's experiencing a huge traffic spike. In the case of malicious DoS or DDoS attacks, they can only really be handled on the network/routing level. Apache (and Drupal) will always faithfully try to respond to requests. Should they be deluged with 1000s of bogus ones, it's not for them to decide if they're legitimate or not.
http://www.chapterthree.com | http://www.outlandishjosh.com
https://pantheon.io | http://www.chapterthree.com | https://www.outlandishjosh.com
Thanks for your reply and
Thanks for your reply and the links. Unfortunately it doesn't really offer me any real solutions. On the one hand, I have a .NET site running on a machine whose processor and memory is barely touched when dealing with many requests, and on the other, a Drupal site where each request is consuming upwards of 20MB of memory. It might not be a fair comparison in all respects, but the end goal is the same: a relatively high-traffic site which cannot simply be taken out of action by someone hammering the refresh key.
Is it not possible to tell Apache to limit the interval between requests from a single IP?
No
Nope. You can limit the number of process threads that apache will maintain, but it won't throttle by IP by default. if you're actually concerned about this, you need to research the underlying issues more thoroughly.
For drupal vs the .NET site: 1) you should be using an opcode compiler (consensus recommends APC) and 2) it sounds like your .net site has a static file cache, which is a great idea when there are lots of anonymous page requests. Look into the boost project for drupal, as many people have been talking about and using successfully in this forum.
http://www.chapterthree.com | http://www.outlandishjosh.com
https://pantheon.io | http://www.chapterthree.com | https://www.outlandishjosh.com
DoS and/or DDoS are not Drupal issues
So you need to look at a different level on the lamp stack.
Using apache configuration options you can control how many resources are consumed on the server, but does not prevent from certain situations. You may need to look at information about Apache security, for example:
http://www.apachesecurity.net/tools/
http://www.modsecurity.org/
You may also need to look at your firewall and network configuration.
Further assistance?
DoS attacks are far more relevant on Drupal because of the high memory usage per request. Unfortunately the responses in this thread have not pointed to anything useful. I don't want to buy a book on Apache security to solve this single problem, and I don't even know where to start with mod_security in order to find the information I need. Maybe someone could suggest a practical solution to the problem? Why aren't more people suffering from this? Have I missed something somewhere?
Securing your sever at the
Securing your sever at the upper levels is the most practical solution. There isn't a magic bullet to prevent such attacks either. Solutions can range from mod_security to custom firewall solutions that monitor the requests( if X amount come into the same path in Y seconds it gets blacklisted for Z seconds), to even custom kernel hacks to alter the resources used per request.
On the Drupal level, a good solution is utilizing a static cache module for pages(ie: Boost). That way those requests end up coming from a static file, not even touching PHP/MySQL. That will work for anonymous people hammering the refresh. If it's a logged in user, then it won't, but if that's the case I would find out who is doing it and block their IP from the server.
Drupal has a high memory usage, but it's in the same ballpark as most CMS' out there, as well as forums. You stated earlier that the problem was with MySQL filling up with sleeping connections, so the the problem is more so lying there. You have this in your MySQL settings:
wait_timeout = 1800
Change that to something like 10 seconds and your sleeping connections should start clearing out.
Of course none of this really helps if your attacker is determined and knowledgeable. Nothing compares to being a vigilant server admin and taking corrective actions to stop the attacks at the upper levels. Also server tuning is an ongoing process. You should always take time to make sure your different levels are tuned properly (Apache, MySQL, PHP) and use programs like AB or Siege to determine what the upper bounds of your server are.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
performance/flexibility
its the classic tradeoff. Drupal is flexible but it can suck up memory so tuning is necessary.
Its not really a Drupal problem though, its just that you've stepped your game rather quickly.
If you are able to run up your own server with a single computer (i.e hitting the F5 key) you need to find the bottleneck. Analyzing your mysql installation is usually the first step. I like the tuning_primer script on day32 (http://www.day32.com/MySQL/) myself. Tuning for number of connections you allow as well as timeout times to prevent large amounts of sleeping processes.
Doing the same with Apache tuning too. Don't leave lots of processes running if you're not using them.
Everybody runs into this problem sooner or later it all depends on how well the server is tuned before you install Drupal. If its not tuned at all or tuned for a static website then you're, most likely, going to have problems.
-Jacob Redding
-Jacob Redding
yes
Well, it sounds like you've missed many the great posts on other threads that explain basic steps to take for running drupal in high-performance environments. Read through some of the other threads or check out some of the external reources like 2bits.com or tag1consulting.com. This is a good place to start:
http://tag1consulting.com/files/checklist.pdf
http://www.chapterthree.com | http://www.outlandishjosh.com
https://pantheon.io | http://www.chapterthree.com | https://www.outlandishjosh.com
Performance and Security are different beasts
Both layers require you know a little about what you're doing (or pay someone else who does). For performace, there are posts in this group that could give you hints on how to optimize your site. But DoS/DDoS prevention is a complete different thing. Any site could be affected by a DoS attack if you do nothing to prevent that. That's what I tried to mean before.
Thanks for the extra
Thanks for the extra information here. I am planning to take the following actions:
I'll return and let you know how this works out.
Drupal and Denial of Service
I think I have a partial solution to this problem.
I have to admit I am somewhat surprised that others using Drupal are not experiencing the same issues Chris Cohen is having. I have a dedicated 4 processor Intel Xenon system with 4 gigs of ram. By most standards, this is a fairly powerful machine for Drupal. The only site running on the system is a drupal system. So I have the latitude to tune the system to one site. Note that I'm using EAccelerator, Drupal's cache, Boost module and Block Cache Alter Module. I've also tried installing memcache, but I didn't get great results. It might be because it can't run with EAccelerator. I've seen conflicting views on this. But that is a whole different issue.
To rephrase the problem. I'm somewhat surprised that a site can be taken down (even if just temporarily) simply by hitting refresh on your browser over and over again. This is generally not an issue for non-authenticated users because modules such as the Boost module are available. The static pages don't generate hits on MySQL so the server can take the hits.
The bottleneck bringing down the site on this "refresh DNS" attack is actually MySQL rather than Apache. Although it would be nice to find a way for Apache to be a gatekeeper so that it could protect MySQL. In any event, it should not be possible to take a Drupal site down (or any other CMS) simply by hitting refresh a ton of times.
Even if some of the pros on this board have fixed this issue, I still believe it must be an issue for many Drupal sites out there.
The first thing I did to combat the potential attack was to install "mod_evasive." I configured it using the defaults provided on numerous sites (2 of the same pages loaded in 1 second) and I have it up and running. It happened to blacklist the Spinner Bot. Other than that, it did not catch anything including my attempts to break the site. I messed around with the configuration for mod_evasive but that did not stop the simple attack I did on my site. A gatekeeper like mod_evasive would be great because it would shutdown the attacker before MySQL was to beat up. This didn't work, so I went onto other solutions. Note that my KeepAlive for mod_evasive was set to 2 seconds and then pushed up to 10 seconds. Mod_evasive does not share its knowledge of the DNS attack across apache processes so it is my understanding that with a short KeepAlive or with KeepAlive off the module won't work since the memory of the attack dies with the Apache process. Still, mod_evasive did not help me with KeepAlive set at 10 seconds (which I think would be enough)
The problem is that when MySQL gets overloaded on my system it starts writing large (in the hundreds of megabytes) .MYD files to /var/tmp. The one thing I have learned is that when you have a lot of connections coming in AND you are writing to disk, your system is screwed. The disk I/O will cause the machine to grind to a halt. The other result of MySQL getting overloaded is I get messages in Drupal log starting with "Incorrect key file for table '/tmp/#sql ..."
I figured I would remount /tmp as a ram disk by adding "tmpfs /tmp tmpfs defaults, mode=1777 0 0" to /etc/fstab. The goal was to enhance the performance of the system so that it could keep up with the potential attacker. Note that this was /tmp, not /var/tmp. The thought being that if it was writing to ram, perhaps the temp files would get released and would not stack up because of the much faster I/O.
From reading on the Internet, it is my understanding that /tmp is cleared out at reboot but that /tmp/var stays on a reboot. Therefore, on most setups you only want to put /tmp in RAM. Because if /tmp/var were to be lost, programs would still expect that data to be there after boot. So when I mounted /tmp as a ram disk, I wasn't expecting it to fix the problem. It was more of a performance enhancement although I was eyeing also moving /tmp/var to ram in the same manner.
On my 4GB system, it created a 2GB drive using the default settings. Although tmpfs is a dynamic ram drive rather than a fixed drive so it is not eating up ram if it doesn't need it for those not familiar with it.
Once I mounted /tmp to ram, I could still bring down the system using page reloads while logged in, but I really had to be trying to do so. Additionally, the system after the attack could recover on its own. Basically, MySQL would go down at some point and the load would go back down. Once the load was back down, the site would come back up without sysadmin getting involved. Before I did the ram drive, the system would thrash so much, that it required a restart of Apache and MySQL to settle things down.
The last thing I decided to do was mount /var/tmp as a ram drive. Because my system is only supporting this site and by my estimates no critical data is stored on /tmp/var, I thought it was worth mounting it as a ram drive.
The result is that the system is much more resilient to an authenticated user pounding it with the refresh button. Not only is it much more difficult to bring the site down, but as soon as the attack stops the CPU load comes back down very quickly. The reason the load is spiking is because MySQL is generating "incorrect key file" errors as a result of the attack.
In any event, I'm still not thrilled, but forcing the MySQL files generated by the attack into memory greatly lessens the impact on the system and allows it to recover nicely since much of the Disk I/O has been reduced.
I hope this comment is helpful to others out there. If anyone has any other ideas on how to get mod_evasive working or any tweaks to MySQL that might be done to stop a potential DNS attack simply by hitting the refresh button, I would greatly appreciate more dialog on this very important issue.
My goal would be to have Apache (using mod_evasive or otherwise) detect the duplicate page loads and throw out a static 403 page so that MySQL is not hit. Mod_evasive is supposed to do this. The strange thing is that I think mod_evasive would likely work for a more sophisticated attack that generated more hits. The page refresh seems to be too slow to engage mod_evasive, but fast enough to mess with a server and MySQL.
You can change MySQL to use a
You can change MySQL to use a different temporary directory, so that you don't have to use your system one on TMPFS:
http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html
I have used that method before and it works rather well, as long as you never plan on doing any replication. The other nice thing about that is you can set up the tmpfs to also serve pages for Boost.
But one thing to look at is the queries on your pages. It sounds like some of the queries are causing temporary tables to be created. That can be rather common if you do some weird views or other complex queries. I would check and see if you have MySQL's query cache enabled and that it isn't too small (download tuning-primer.sh script and it will let you know how things are running and what needs tuned). That would help out a lot.
If that's all OK then I would look for the bad queries and either look at rewriting them, coming up with a decent index for them or caching them (if it's views, you can set cache in views).
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Refresh Issue
Good point on be able to use different temp directory although for performance reasons I want all the temp files to be in memory. Good idea on using tmpfs to serve Boost pages. Although at some point, 4GB won't be enough for all of it.
I think you're right on the queries. I have a 128MB query cache. I've tried it higher than this amount but it is not recommend. I suppose above 128MB you eat up more CPU going through the cache than it is worth having the extra data cached. I do use views with caching enabled. I'm also using Block Cache Alter so that all the view blocks are cached.
Point being if I had everything cached 100% then these attacks would not be hitting the database. That is kind of where I'm headed.
I'm somewhat disappointed that eAccelerator (shm size = 64) is not doing the job. I'm a novice at a lot of this, so for example I don't understand exactly what eAccelerator does verses memcache.
Does anyone run eAccelerator with Memcache. It doesn't seem that Drupal has great options for caching authenticated users. Cache Router seemed promising, but it doesn't work with the new version of eAccelerator.
Basically it seems that Drupal users have two options to prevent a page refresh attack 100%:
I think I know what needs to be done and I've done most of it. There is one last piece that needs to be put in place. I just don't know the correct tool for #1 and #2. #1 might mean more trial and error with mod_evasive. #2 might mean Memcache. I've seen other option such as applying patches to Drupal but I don't really want to go down that road.
Memcache will help with
Memcache will help with caching database queries. eAccelerator is an opcode cache. that means it keeps a copy of your scripts as php compiles them for execution. This will make subsequent runs of the same script faster.
You should try APC instead of eAccelerator, as the consensus around here is that it is more stable, or the Zend Community Server, which has Zend Optimiser+, which is a bit faster than APC (we use Zend).
For authenticated users, give Authcache a shot - it is boost for logged in users, but it has quite a few caveats. caching is hard, and caching for logged in users is even harder.
Finally, make sure to have a look at Varnish.
Cheers,
Martijn
On a base Drupal 6 install
On a base Drupal 6 install with 5,000 nodes, using Drupal's standard cache and APC for op-code caching, my old dual-core development server with 2gb of ram can pump out 31 page requests per second for a logged in user and that's with the test running for 5 minutes. Actually on my server my biggest bottle neck was the hard drive and Apache. MySQL was taking up the least amount of resources. That's an awful fast clicker on the refresh button, but not for a script requesting the same page over and over again.
The first thing I would do is install the devel module and enable query output in the settings. Check it to sort by query time and start at the top of the list on your front page and work down checking the queries. If you have PHPMyAdmin on your server, just paste the query into the sql window and put EXPLAIN at the beginning of the query and run it, then you will see exactly what MySQL is doing to run that query. The last 2 columns are the most important. If Rows is a very high number, then MySQL is having to search through all that data to return your results. Better indexing will help. Also if the last row shows "Using Where; Using Temporary; Using Filesort" then you have a problem and need to look at doing better indexing, new queries or caching.
On the attacks from the Apache side, there are modules out there to help like mod_dosevasive. It's not a regular Apache mod and you would have to download the source and compile it, but it does prevent multiple requests from the same IP in X seconds. This really should be a last resort though. Before that I would look at tuning Apache. It's better to get Apache to the point that it serves 503 pages before your database knocks you off line. Things like max_requests and timeout help with that.
For DNS attacks, check out Fail2Ban. Most distributions have this in their repositories so you can usually get it via apt-get or yum (You need a later version for DNS protection and it is easy to compile). I install this on every server I set up, especially to block things like brute force SSH attacks.
As far as Eaccelerator, drop that. Go with APC. It's much more stable and a lot faster to the point that it will be included with core PHP 6.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Fail2ban
I'll check out Fail2ban. Thanks for the tip.
Don't try and hide the problem
It sounds to me like you might be putting layer upon layer of clothing over top of a gaping wound to hide the problem, but you are not addressing the root issue - you have a gaping wound. If MySQL is writing hundreds of megabytes of temporary files per page load then I think the most likely explanation is that you have complex queries that are not using an index. I would review what queries are being executed with the Devel module and EXPLAIN and also setup the MySQL slow query log (to also log queries that don't use indexes). You might also want to look at the DB Tuner Drupal module, it may be able to automatically add the indexes that you need.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
You may be right
Dalin:
You may be right. I'll fully admit I'm much more comfortable doing non-database programming and messing with the server than diving into the databases. I'll see if DB Tuner can provide any help. I'm somewhat skeptical of how much success I'll have with the database because the one thing that mysqltuner points out is that I have too many temporary tables written to disk. Usually around 40-45% of my tables are written to disk according to MySQLTuner.
I have raised the size of temp_table_size and max_heap_table_size up to 512M and no matter how high I go I get the same result. I currently have it set at 16M. No reason to piss away ram. I believe the issue may be that the Drigg module I'm using saves data in format BLOB. I haven't verified that myself; however, from what I've read the inability to bring the size of the table written to disk down could be the BLOB data type. Hence, rather than rewriting the database I figured I would kind of trick the system by making the temp a ram drive.
It is interesting how performance tuning and security (DNS attacks) have become the same issue for me. I suppose I need to make a better determination whether the DB is the problem.
In your experience, a authenticated user can keep hitting refresh and MySQL can handle it? I suppose if the queries are clean it doesn't get overloaded? Perhaps this is the reason only certain people are having issues. Basically, have a badly tuned database will also open you up to DNS attacks since your system is somewhat fragile. Perhaps that will be the conclusion here.
Thank you for the input.
Heavy queries? Too many modules?
Like what was said above: you are trying to hide a problem. The site is using excessive resources, most probably a couple of views on the main page, or too many modules.
But perhaps you also have a lot of 404s on images that don't exist in the theme, and that is causing one refresh request to mushroom into tens of requests. See this article on reducing server resource utilization on busy sites by implementing fast 404s for how to make these take far less resources.
Also, if this is the case, it would explain why a site under stress in a lab settings would be able to do 31 requests per second, but hitting the Refresh button would kill it. The reason is that in a lab, using ab or sieege for example, you are probably loading the HTML page only but not any of its static files, like a real browser would do.
In the end, try to identify WHAT is being overused when Refresh is hit too much, and then try to solve that. Everything else is speculation and guessing.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Update
An update:
I installed memcache. It is providing a nice performance boost and the site seems to be running well. In fact, I'm quite happy with the performance of Boost, EAccelerator, Memcache and Block Cache Alter (this really helps with my views blocks).
If I slam refresh while logged in, I can still overrun MySQL. This must mean that Memcache is not caching the entire page load since MySQL is getting hit (perhaps AuthCache might help fill this hole in the caching). The good news is that even with my CPU (4 cpus) spiking to a sever load over 10 during an attack it still serves up pages pretty quickly with Memcache running (before, with sever load that high, the system would crawl). Moreover, once the attack stops, it recovers almost instantly. I still don't love the fact that it is possible for someone to mess with my site simply by hitting refresh over and over again. So I'm still hoping for a total solution.
I know what is causing the server spike. When MYSQL gets all of these requests, PHP is generating this error. I might see it 20 times in a row in my log.
"Incorrect key file for table '/tmp/#sql_7ccb_11.MYI'; try to repair it query: SELECT * FROM node n LEFT JOIN drigg_node dn2 ON dn2.dnid = n.nid WHERE ( 1=1 AND !(n.type='drigg' AND dn2.killed = 1 ) ) AND ( !(n.type='drigg' AND n.created > UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) ) ) AND ( n.type = 'drigg' AND n.status = 1 and n.promote = 1 )ORDER BY n.sticky DESC, dn2.promoted_on DESC LIMIT 0, 17 in /home/social43/public_html/modules/drigg/drigg/drigg_ui/drigg_ui.module on line 478."
I never get this database error under normal operating conditions. I'm guessing that if I was a programmer that I could perhaps rewrite the drigg module to fix this query. I don't have the knowledge to do this. This is why I keep trying to hide the problem rather than fixing the database issue. It is not optimal, but might be the best practical solution for me if I can figure out a way to hide it without rewriting code.
Is there any way by simply adjusting php.ini or my.cnf that I might be able to workaround the problem?
Thank you to mdekkers and intoxination for your helpful posts too.
That error is totally MySQL
That error is totally MySQL and is common when using tmpfs for storing temporary files. That's one of the reasons it's not that recommended to use tmpfs for MySQL temporary files. Yeah it works and gives some performance boost, but it can also cause some weird side effects and you got to constantly tune everything.
You really need to focus on looking for the bad queries. Fixing those will help 99% of your problems. Everything else is just trying to hide the underlying problem and is going to lead to more problems down the road, kind of like this one.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Fast 404
Great piece of code on the fast 404. Thank you.
AuthCache
I installed AuthCache and it has closed the loop on the page refresh issue. Thank you to everyone for their responses.
A Fast Site Requires Optimization On All Fronts
I'm writing this post because I think it may really help other Drupal newbies like myself. First, I must say that getting your site running correctly is not an easy task. I've spent 6 months learning Drupal, learning about tuning a server and various caching technologies.
Anyone can see from the posts above that many of the pros were telling me to optimize my database queries. It is tough advice to swallow if you don't have a large budget to hire someone and you yourself are not very experienced with databases. It is tempting to avoid the slow queries and hope that the various modules you have plugged in have been optimized properly. I've learned this is a very bad assumption. Some are coded badly. Some are coded ok for their author, but must be customized if you are using it under different conditions. For example, the author may have 500 nodes and the database calls may be fine for the author. But if you have 100,000 nodes, the module may require performance tuning to work properly.
But this is the honest truth. For a site with heavy database activity, you must have a dedicated server (and not a cheap one, 4 processors, 4 gigs of ram), LAMP optimization, Drupal cache optimization (Authcache, Memcache) and database optimization. I started off with a 512 VPS, upgraded two 2x core machine with 2GBs and finally to the 4 x core with 4GBs. If you have a lot of database access, you'll need the memory for all of the caching technologies.
I finally sat down today and took advice from Kbahey 2bits. He has an article mentioning that fixing the low hanging fruit is often 80% of optimization a site. I noticed a single query on certain pages were causing page generation times of 2.5 seconds instead of 300 ms. By changing a couple of lines of code, I have increased the speed on these pages greatly. Now I don't have to worry about trying to hide the problem by having these pages cached through eternity.
On my main page, I was having a similar problem. With AuthCache/Memcache, if you didn't get the first hit, you received a fast page. But new content is generated pretty often and if you were the unlucky person to receive an uncached page, the page was taking 2-3 seconds to generate. Many of these people would simply bounce away.
By modifying the database calls, I've reduced the page generation time to about 300 ms. I'll admit that it took a lot of trial and error since I'm not a programmer, but fixing these slow database calls was well worth the time.
The other great thing about having your system firing on all cylinders is that server load goes down. So not only are your queries running faster due to tuning, but because the load is lower it has a cascading effect. Not only are the slow queries gone, but with the CPU load down you pick up a slight speed boost as well.
So again, thank you to everyone on this forum. For the first time in 6 months, I actually have a site that is serving pages at a rate that users expect on the Internet today. Obviously this means lower bounce rates and all that comes with the greater speed.
Modifying code is not a good
Modifying code is not a good option, because it would be quite difficult to update the core to a newer version or install extension. You should use EXPLAIN in MySQL to find out why queries are slow and optimize their speed with right indexes. Also database tuning is absolutely required when you have troubles with data. Take a look here: http://drupalperformanceblog.com/drupal-performance-tips (Database tuning section).
Modifying Code
I modified the code on non-core modules. Although certain modules, such as block cacher alter, do requires patches to the core.