Forum Moderators: phranque
Mysql server is overloaded very often and I can't get better server because it's the best what can I get. I don't know how to solve this problem. I can get one more server, but I am not sure if it could help.
Is there any not expensive way how to make 2 or more servers to serve the same mysql database?
Thanks.
- are your db queries as efficient as possible?
- do you have extra or superfluous queries?
- take a look at the queries that run on every or most all pages of your application - do you really need them? Can you implement some sort of caching for that data?
- put a trace on your db during your most busy periods of the day. What are the long running queries? What are the most repeated queries? Optimize those first.
- are your tables properly indexed?
- is there any data that can be archived outside of the db?
- are there any reporting queries running that can be moved to a less busy time?
- have you properly joined tables (where necessary) (ie. using inner joins whenever possible instead of outer joins)
- are you over using transactions?
- Revisit your mysql settings to ensure you have everything set properly.
That should get you started.
The killers are table scans, sorts and lock escalation. A lot can be solved with pretty minor tweaking once you know where the problem lies. Extra indexing creates an overhead on update but speeds queries. It all depends on the usage pattern, I have speeded up different databases by adding indexes and by stripping our around 90% of the indexing.
Thank you for your replies.
Server specifications:
Intel ® Xeon Quad Core
4 GB RAM
2 x 500 GB Hard Drives
Number of online users at peak times ~ 600-700.
It's a dating website with forum, groups, blogs etc. and the problem is that most of users use instant messagging like a chat. I have a flash chat, but it's not popular - people preffer instant messagging instead of chat. Each instant message takes about 15 database queries (to check mailbox size, blacklists, user status etc, etc). DB queries are as efficient as possible, tables are properly indexed.
Sometimes TOP shows that Mysql is using 100% of CPU. Server load most time is about 0,8 - 1 - 2, but at peak times it grows to 5,6,7,8,9,10... and then website is very slow....
I think I need a better server, but there are no better servers @ my data center. I can get one more server, but how can I make 2 servers act like 1 server (to serve the same database)?
download and run
MySQLTuner
on your server. It is a simple perl script which will tell you what mysql settings need tuning.
Hardware
More ram will help. Ram is now DIRT cheap but your hosters may charge an expensive premium for the upgrade. It will be well worth it though.
How are the two disks configured? You have them in raid for speed or reliability?
I'm not a fan of 2 disk raid and much prefer to set them up as a master disk and ancillary where master runs all the O/S and apps, the ancillary does all the log files, or where you store your database files.
There are many other things you can do such as setting noatime on the disks. This is a big slowdown for disks where it records everytime a file was not accessed. Probably not needed for a site like yours.
If you have the ram set your temp dirs (mysql temp dirs etc) to /dev/shm or create a dedicated ramdrive.
Software
Out of the box most servers are loaded with hell of a lot of uneccessary junk. You run a printer on that thing? No. Get rid of CUPS.
Java, tomcat....if not needed don't have them installed.
There are a lot of services you can stop from running at startup.
Obviously this is not easy on a live server but it is something for you to consider when you next upgrade.
Get the new server and tune it to the max before you transfer users across. Uninstall apps and shutdown services in batches. Reboot server and see what doesn't work.
Summary
Start with the msyql and apache tuning. You can notice big differences in performance with a few simple tweaks.
More ram will help.
[edited by: Frank_Rizzo at 7:43 pm (utc) on April 22, 2009]
It is very easy to install (free), but implementation requires programming knowledge. Here is how it works:
Once you have it installed, you essentially "start" your memcached server using the following command:
memcached -d -u nobody -m 1024 127.0.0.1 -p 11211
This tells it to run on the local machine, using port 11211 (default) and to use 1GB of RAM (1024).
Once it is running, you can utilize the API to essentially write your heavy traffic webpages to the cache. The API allows you to do simple "set" and "get" commands:
my $cached = $memd->set("$cachekey", $html_page);In simple terms... When you generate the html output for a page from the database, before printing it out, simply put it in the "cache". Then next time that page is requested, you can eheck the cache to see if the page exists, if so, use the cached version (by doing a "get" command).
The only other trick, you need to update the cached page if any changes occur in the database. We manage this with another variable we also store in the cache called a dirty-bit. If a user adds a comment to a page, then we essentially erase the cache page and let the next person requesting it regenerate it and re-cache it.
This may sound a bit overwhelming, but it is not that difficult to implement, and it can get you huge performance savings if you are able to cache your most heavily visited pages.
Think about it from a database perspective. If you have a page that changes maybe every few days, but that page is requested thousands of times a day, why do you need to go to the database thousands of times a day? Just put in the cache and you've only read the database one time. After that, all requests pull it straight from RAM/memory. You can imagine the performance improvement you will see.
All the big-time social sites use Memcached (YouTube, Facebook, etc). It is a free tool, the only cost is your time adding some custom programming using their API, which is very easy to use.
Good luck!
About hard drives - I have RAID1.
I already ordered MySQL and Apache tuning services from a server administration company. But even if it helps to decrease server load, it will be only a temporary solution. I am getting more and more visitors and there is no tuning which can help a server to handle unlimited number of requests.
I have noticed that some websites redirect their users to the different servers depending of their load:
www1.domain.com
www2.domain.com
www3.domain.com ....
It's not a problem to upload the same files to 3 or more servers and then redirect users to different servers. But MySQL database updates every second, every minute. So, it's not possible to have the same database with the same content on more than 1 server. What can you suggest in this situation?
Thank you.
I was thinking about caching webpages, but...
Just an example. I have a forum which is very busy and caching could help to decrease server load. The problem is that all forum pages contain information which changes all the time:
- number of reads
- number of posts in the thread
- last post time/poster
- users main photo thumbnail
- number of posts for each user
The same in other sections.
If I use caching, users will see no changes in cached pages.
I am wrong?
Each instant message takes about 15 database queries (to check mailbox size, blacklists, user status etc, etc).
Not to be a jerk, but 15 queries to send one instant message is not "as efficient as possible." I think you need to really look at that application and decide what needs to be done in real time and what can be done in a batch at low usage times.
For example, calculating someone's mailbox size each time they get a new message is really inefficient. You can do this 4 or 5 times a day and set a flag in the db.
but how can I make 2 servers act like 1 server (to serve the same database)?
The neat thing about memcached, it is a general caching utility, you can cache anything! You don't have to cache entire pages, you can cache sections of pages that don't change often, but still require a complex query on a large table, etc.
For instance on some of my pages, I was also not able to cache the entire page due to some real time information I display, however I was able to cache the query that pull all the "related stories" for the page.
You still need to manage changes by either removing that cache entry, or replacing it, etc... but the trick is to find the right balance where you are not doing that too often, and still saving yourself tons of mysql db cycles...
Hopefully you get the point. It is a very powerful and useful (and simple to use) caching utility that can cache pretty much anything... html, query results, text blocks... you name it...
Intel ® Xeon Quad Core
Sometimes TOP shows that Mysql is using 100% of CPU
Server load most time is about 0,8 - 1 - 2, but at peak times it grows to 5,6,7,8,9,10
Server loads of two to three times the number of cores are not alarming, as long as your processing is equally distributed over all cores. But if one core is doing all the work and the other three are effectively sleeping, your server will severely slow down with a server load of 10.
Based on these figures and without further information, your application currently seems to be CPU bound, not memory or disk. Furthermore, most dedicated boxes for rent at data centers are 32 bit anyway, so adding more than 4 GB is not very effective ;)
You have a quad core processor, but not all MySQL database layouts including MyISAM and InnoDB scale well over multiple cores. So effectively you could have 4 cores but practically use only one of them. If you start top and set the multi-CPU view by pressing 1. If during peak time one core is doing considerably more work than the others, this can be the problem.
The problem with MySQL is that it isn't multi-process like Apache which scales evenly over multiple cores, but multi-threaded which effectively gives it one process which spawns internal subprocesses for sub-tasks. The development of better scaling over multiple-core computers is underway but not finished yet. Expect it to become better in future versions.
There are special patches from Google and MySQL forks like Percona and OurDelta which perform better in scaling over multiple cores, but installing and maintaining them is a lot of work for a website owner and I wouldn't recommend it to you. If you use InnoDB as your database engine, you might consider downloading the offical InnoDB plugin available at [innodb.com...] which contains some of the Google patches which improve multi-core performance.
With multiple cores you have also the problem of shared memory bandwidth and locking of shared memory between different processes. Therefore you might even get better performance from a high-speed Pentium 4 single core processor with fast memory bus speed compared with your current quad-core. And such a server comes at a lower price than your quad-core :)
gcan. It takes less than a minute to wget and run mysqltuner.pl The report will instantly make recommendations for your settings.
wget mysqltuner.com/mysqltuner.pl
chmod 0700 mysqltuner.com
./mysqltuner.pl
It will produce a report similar to:
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-isam to MySQL configuration to disable ISAM
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)
Agreed that mysql won't use more than 4gb but adding more ram can help - especially for /dev/shm or a dedicated ramdrive. Put your mysql temp dir, and your session dir in there and this can reduce the load considerably.
Turning off noatime can help reduce disk load (which can indirectly affect cpu load).
mount
you should see something like:
/dev/md2 on / type ext3 (rw,noatime)
If there is no noatime flag on your main partition your disk is effectively running with the handbrake on.
That's a couple of simple tweaks which can be done in minutes which makes a big difference to bog standard software images installed by webhosters.
Once that is done move onto making your server leaner by getting rid of uneccessary apps.
rpm -q cups
If you have cups installed and you don't use the server for printing then why have it installed? It's probably started as a service and is using up cycles. Ditch it and the dozens of other apps which are bloating your server. (Note that this type of optimization is the most dangerous as. Best to do this when moving to a new server where you slim down the apps on the new server rather than on a live server).
An experienced server administrator will do the tuning on Saturday.
I will try mysqltuner.
I will let you know the results.
------------------------------------
maximillianos, is memchached better than Pear Cache Lite or Zend Cache? I will work on chaching, however as I wrote in my previous post, I don't have a lot of pages (or sections of pages) that can be chached beause everything changes every minute.
-------------------------------
stajer, 15 queries to send an instant message is total amount of queries per page:
5 queries are used do display actual information in user info panel:
1 query to check the number of friends who are online
1 query to check the number of favorites who are online
1 query to check if there are new users who have viewed user's profile
1 query to check for new instant messages
1 query to check for new "virtual hugs"
2-3 queries are used to control online users list:
1 query to check if user exists in online users table
1 query to insert user in online users table or to update time in this table
1 query to delete expired users from online usrs table (today I removed this and acced a cronjob to do this once in 5 minutes).
7 queries are used to send an instant message:
1 query to retrieve user info and settings from users table.
1 query to retrieve recipients info and settings from users table.
1 query to check if function of sending instant messages is not blocked for this user.
1 query to check if user doesn't exist in the recipient's blacklist
1 query to insert instant message in messages table
1 query to insert instant message in copies table if user checked it
1 query to check if recipient is online (to send mail notification about new message if recipient is not online).
1 query to update the number of total messages in recipients table.
So, I don't see any queries that can be removed from this script. If you have any ideas how to optimize it, please let me know.
You may find that you would see improvements with caching the script files (apc cache) rather than the pages. Your script files will be constant even if your content changes and thus is suitable for caching.
In pecking order I would:
tune mysql
tune apache
tune a cache system
check the O/S is now not running low on memory.
Note that linux is efficient in that it leaves very little free memory. Don't be alarmed if you see
Memory Used 90%
Free 10%
It will allocated unused memory to buffers and it's own caching system.
Only be alarmed if you see high swap file usage. Ideally the swap file should never have to be utilised.
[edited by: Frank_Rizzo at 10:35 am (utc) on April 24, 2009]
maximillianos, is memchached better than Pear Cache Lite or Zend Cache? I will work on chaching, however as I wrote in my previous post, I don't have a lot of pages (or sections of pages) that can be chached beause everything changes every minute.
Sorry, I do not have experience with Cache Lite or Zend Cache. I have only worked with memcached since I read the following sites utilize it I figured it had a pretty good reputation... so I just went with it:
From Wikipedia - memcached:
The system is used by several very large, well-known sites including YouTube, LiveJournal, Wikipedia/Wikimedia, Wikia, SourceForge, Metacafe, Facebook, Digg, Twitter, Fotolog, The Pirate Bay and Netlog.
1 query to check if user exists in online users table
1 query to insert user in online users table or to update time in this table
This could be combined in one query by using the "INSERT ... ON DUPLICATE KEY UPDATE ..." syntax. In that case you always use an INSERT statement to create a new user. If that fails because the user already exists, automatically an update of one or more fields is performed.
Just a thought...
You should research using "triggers" on your db. For example, let's take this set of queries:
5 queries are used do display actual information in user info panel:1 query to check the number of friends who are online
1 query to check the number of favorites who are online
1 query to check if there are new users who have viewed user's profile
1 query to check for new instant messages
1 query to check for new "virtual hugs"
Rather than run a separate query for each of these things, add 5 columns (onlinefriends,favorites,newviews,instantmessages,virthugs) to the USER table. Each of those columns will contain an up to date number for those items you had to query one by one.
Each of those columns starts at "0."
A trigger is an automatic db function that updates a table upon another db action. the user table.
For example, when a new IM is sent to a user, you write a trigger that is started when the IM message is inserted into the IM table. The trigger updates the user table to set instantmessage = instantmessage + 1.
If you write triggers for all these events, you can just do one select on the user table to get all the current statistics. You don't have to do count or sum operations on a subset of data from a large table.
I know this is slightly counter to the db idea of "normalize, normalize, normalize" but for statistic type functions like you are running this works much better. The queries are simpler and fewer.
This could be combined in one query by using the "INSERT ... ON DUPLICATE KEY UPDATE ..." syntax.
lammert, thanks. I agree, I can use "INSERT ... ON DUPLICATE KEY UPDATE ..." instead of 2 queries. I'll change it.
The problem with caching is that the O/S may fight the cache for memory. If a lot of memory is used up for the cache the O/S does not have so much memory to cache files it uses regularily, and mysql may not have enough memory to cache tables and indexes.
That's why I am not sure about using Memchached. I just installed Pear Chache Lite - it's a different caching system which is not using RAM.
I assume MySQL has some sort of replication? Alternatively, you can split the functionality between the servers. Use one just for the IMing and the other for all the other site functionality.
I could split the database. For exapmple:
1 server - forum
1 server - instant messages, etc
The problem is that all scripts need information from user table. So, if I remove forum to server B, but user table stays on server A, the script will have to connect to 2 databases - I don't think it's a good idea.
Another consideration... many of those "real time stats" are very useful to show your board is full of activity... but they don't necessarily have to be "real time". What if you had them update every 10 minutes instead of every single pageview?
In my situation it's not possible to update user stats every 10 or 5 minutes. It could be possible only if I remove user stats to a separate frame, but I don't like frames.
You should research using "triggers" on your db. For example, let's take this set of queries:
Yes, I am using triggers for many situations, but it's not possible with stats like "friends online", "favorites online".........
In my situation it's not possible to update user stats every 10 or 5 minutes. It could be possible only if I remove user stats to a separate frame, but I don't like frames.
If you are reading them from a cache it is possible. You essentially read the cache instead of the database (ie - replace the queries to read the cache first, if no cache entry exists, then read the db as a backup).
Then you have a job that updates the cache every 10 minutes. This is a simple example, but you get the point.
We do similar implementations all over our site. Another example, we have a page that shows all recent activity, it joins data from multiple tables that have an excessive amount of rows. Instead of making that complicated query every time someone opens that page, we instead store that "list" of activity in the cache and append to it as folks make posts around the site. Their posts are of course going into the database, but we also append them to the cache variable that is used to display the recent activity list.
So the activity list remains "real time", but it rarely accesses the database. Ours is setup to "re-sync" with the database every couple hundred entries...
The memcache is pretty flexible... You can get creative and really cut down on your database accesses...
