| Posted by pejman_view, 09-12-2013, 12:27 AM | | Hi,
I have a beowser-game which has many 2000 online users instantaneously. the users maybe press one or two clicks a second!
I need a configuration for my.cnf ,server information is below:
CPU : Dual x Quad Core Xeon L5520 (2.26Ghz)
Ram : 36Gb DDR3
HDD : 1TB Sata
____________
2000 online users
1 Milion clicks an hour.
|
| Posted by TheWareHive, 09-12-2013, 12:29 AM | | Is your DB my Read or Write heavy? Also, I would upgrade to a better HDD setup like a RAID with at least SAS drives if not host the DB on a separate server and run SSD drives. What is your current configuration and what kind of issues are you having now? Are your tables crashing? We would need more info in order to help you.
|
| Posted by gooomba, 09-12-2013, 04:59 AM | | Moved to the appropriate section.
|
| Posted by Lee@GenesisAdaptive, 09-12-2013, 01:31 PM | | How big is your database?
|
| Posted by pejman_view, 09-12-2013, 04:02 PM | | Hi,
Thank you all for your replies:
Yes, It is.
I am going to order SSD HDD.
Yes, Also shows max_connection_user error.
15 cronjobs refresh all tables and rows every seconds, this cron jobs process everything, attacks, movements, resource production. When I run cron jobes. we found this errors. and even these cron use 50 percent of cpu.
We are at beginning of the game , so database is only 150mb, with only 90tables. most of them are myISAM. But we log everything in datbase so it's going to get bigger every seconds.
I use default directadmin huge configuration: I used this command:
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
|
| Posted by Scott.Mc, 09-12-2013, 04:30 PM | | Why don't you provide the output of something like
https://raw.github.com/major/MySQLTu...ltuner.pl|perl
and
curl https://launchpadlibrarian.net/78745...mer.sh|/bin/sh
So we know what your data set is like.
|
| Posted by 10bus, 09-12-2013, 04:40 PM | | Very timely post. I've been doing a lot of mysql optimization work lately!
So you stated that most of your tables are MyISAM and not InnoDB. When you say 'most', what exactly are you talking about here? They have pretty different tuning approaches. InnoDB has a lot of great features that might be important to your application, such as row-level locking and better data integrity.
Bottom line: MySQL is not processor intensive, it's memory and disk intensive. The default cache parameters are very low - review those and increase, as appropriate. Improve your disk subsystem and add as much memory as you can afford.
Couple of fast and easy performance tips if you are using Linux: mount /tmp in memory. MySQL writes temp table data to this location. To do this, edit your /etc/fstab file and add this line: "none /tmp tmpfs defaults 0 0". Reboot and enjoy.
Download and run the mysqltuner application. It will analyze your setup and offer pretty helpful recommendations.
When MySQL crashes, examine the error log. It will be very helpful in troubleshooting the problem(s).
Use the iostat, top to monitor your processes.
Use the mysqlreport program to analyze your memory usage, slow queries, cache hits, etc... Very helpful report.
Anaylze your queries: "EXPLAIN EXTENDED (insert query here)". It will tell you how many records MySQL has to search to give you a result and, if present, which keys/indexes it can use for that query. If you aren't using keys and indexes, then fix that ASAP.
Some queries can't be cached in memory and explicitly cause a disk-based temp table to be created, specifically: (1) Presence of a BLOB or TEXT column in the table, (2) Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes, (3) Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.
That's just off the top of my head. I'd really recommend hiring a DBA to help you optimize your queries and tune the server. It will pay off in the long-run!
Thanks,
<>
Last edited by anon-e-mouse; 09-12-2013 at 05:10 PM.
|
| Posted by TheWareHive, 09-12-2013, 06:43 PM | | Your MySQL usage and crons will overload your SSD drive in no time. You will need a RAID array with at least SAS or SSD drives or you will run into bottlenecks.
|
| Posted by gPowerHost, 09-12-2013, 08:44 PM | | How mission critical is the data? If the possibility of an occasional blip would not be that bad (maybe a rare event but it could/will occur). Then how large could the database be? If you have high quality server components, if the possibility of losing a few seconds of game data on a rare occasion would be OK and if the size of the database would not exceed your RAM then you could hire an expert to setup a MySQL MEMORY (HEAP) Storage Engine see: http://dev.mysql.com/doc/refman/5.0/...ge-engine.html
And then you could properly engineer a replication to a hard drive. Pretty simple stuff: http://dev.mysql.com/doc/refman/5.0/en/replication.html What this does is loads your entire MySQL database into RAM and all of the transactions are so lightning fast it will make your head spin. Now you might say but yes what if the power goes out? That is why you need to decide if this fits your needs. Lets say you replicate to disk every minute or five minutes. And the server crashes, you are going to lose those transactions since the last replication to disk. Not to minimize games, but you would know if this would be an acceptable trade-off and risk.
I have set these up in the past to do huge statistical database jobs where maybe the job will run for a day or two. I've intentionally crashed them and was able to recover to the replication point upon startup. I also do this for small db's that meet the criteria.
I wouldn't do this with bad equipment, nor with live financial transactions (unless you have an ace staff).
Also, as indicated SSD's are great but not for heavy writing. This is because SSD's have their weaknesses (like all things) and once data is written to a physical location on the SSD, it has to be erased before the same place can be written to. Depending on your setup erasure might be possible but erase is expensive (meaning it take a great deal of resources and time). If you are writing rarely, and reading 99% (arbitrary number you will have to calculate!) then SSD's might be good. Probably not.
As for all the things 10bus said, you bet those are good places to focus.
Now one last possibility, that would normally be my first option, you could just increase the Buffer Pool Size to essentially put the whole db in RAM (running in the buffer pool). But I would run tests (and get an array of small sized high speed and high performance drives) because the database is also written to on the disk. I would want to know what would happen this way if the drives couldn't keep up. I'd look to test this. If this avenue give the performance then go this way, but I just have a feeling it might not.
Please let us know how it works out!
|
| Posted by net, 09-12-2013, 08:48 PM | | Moved > Hosting Security and Technology .
|
| Posted by RRWH, 09-12-2013, 09:17 PM | | This should be a sticky, I say the same thing nearly every time these sort of questions come up.
OP - until you actually provide BOTH outputs nobody can make any meaningful suggestions.
|
|
 Add to Favourites
 Print this Article
|
