Mysql problem CPU usage

Support for the latest build of L2J Server, get help here with installations, upgrades, problems.
Do not post bugs reports here, use viewforum.php?f=77 instead.
There is no support for other server builds than the official provided by l2jserver.com
Forum rules
READ NOW: L2j Forums Rules of Conduct
User avatar
Nakamoura
Posts: 62
Joined: Wed Sep 02, 2009 8:03 am

Re: Mysql problem CPU usage

Post by Nakamoura »

I was searching in the internet for some Configs about MySQL And GameServer Configs for big community with less lag..By searching long ago now i found on MaxCheaters forum this but its for Windows 2003 O.S :

Code: Select all

I am writting this guide for people who use windows server 2003. This will mostly also apply to people who use other versions of windows. 1.Hardware/OS Before doing anything, you should have a look at your server hardware. A intel pentium core 2 duo with 2 gb ram may look nice but with l2j, its not.Although this is budget based and you will probally build up some nice server allong the way of having a private server, you will need some powerfull hardware to support 1000+.Currently i am using this configuration: Cpu: Quad Core Xeon 3220 - 2.40GHz (Kentsfield) - 2 x 4MB cacheMBram: 8 GB DDR2 667HDD: 73GB SA-SCSI 15K RPMHDD2: 73GB SA-SCSI 15K RPMConnection: 1000mbit up/downOS: Windows server 2003 64bit RC2 You might think that cpu power is all that matters when hosting a gameserver but in fact, your harddisk and ram are way more important.If you want to run more then 600 players with 0 lag i am advicing you to go for 15k rpm hdds. I have two of them and without these i wouldnt be able to run 1000+.The first harddisk is running my windows and has all my program files on it etc. The second harddisk is dedicated for my database. If you want performance youshould always do this. Will increase acces time allot and as l2j with allot of player on has many writes/reads its a must if you want lag free gameplay.About the ram, 6 Gb should do fine have run on that for a long time.About the connection, 100mbit is more then enough. OS, well i recommend using the 64bit version of windows server 2003. This way you can run your java and mysql in 64 bit wich speeds it up a bit.Also, you can allocate more ram to your startup file but i will tell more about this later on.  2.Windows server 2003 So lets see, we got a awesome server machine wich is up and running with windows server 2003. I think we are ready for some configuration changes for windows.The first thing you need to change is the resource allocating. Windows server sets it to background services by default.]Although this might be very good for stuff like Exchange, we dont want it like this. 1.Other mouse button on my computer and select properties(new window will popup)2.Click on the "Advanced" tab3.You will see Performance with a button "settings...." Click on it.(new window will open)4.Click on the "Advanced" tab5.Now set Processor scheduling and Memory usage to "Programs".6.Press apply and dont close the window yet cause we are also gonna change the pagefile size. Ok now the resource allocation is correct. Now we are going to change the pagefile size. The pagefile is windows virtual memory wich is stored on the harddisk.As the pagefile is used allot we want to increase it and we want to set it to a set value. That means setting the initial size the same as the maximum.This is done cause this way you want have fragmentation with your page file and your hardisk doesnt have to resize it all the time wich creates extra reads and writes.Lets continu where we left the Advanced tab open. 7.You will see Virtual Memory with a button that says "Change". Click on this.(New window will popup)8.Now set it to custom size.9.Windows advices you to take 1.5x your ram size as your page file. I am not following this and i am setting mine on 6114. Make sure that the initial size and maximum size are the same!10.Once you have filled in the two valeus, press set and then apply.11. Apply all and restart your computer. If you want to give the pagefile optimal performance and you got an harddisk unused you can also change the location of the pagefile. This will give it a little bit more performance.  3.Mysql Well well, we are at MySql now. Most people hate changing the config files but its really needed if you want a nice server.Im not an expert with this but hours of reading and watching mysql workshops i came up with some stuff wich needs to be changed.I will post parts of the Mysql config i am using so you will know what you need to change.Although the main resource mysql wants is ram its not needed to set giant values at the ram settings.This will only create a unwanted giant space usage in your page file. Just keep a look at the size of your database regulary and change the size of the ram setting according to it.(example: My database is 200 mb in total, i will set my ram usage size to 512mb). Shutdown your Mysql before changing anything!!! Ok open your my.ini(text between ----->blaatblaat<--- are my comments! First setting you should check:# The default storage engine that will be used when create new tables whendefault-storage-engine=INNODB -------->Set this to a high value, something like mine.# The maximum amount of concurrent sessions the MySQL server will# allow. One of these connections will be reserved for a user with# SUPER privileges to allow the administrator to login even if the# connection limit has been reached.max_connections=600  -------->This can be set at 0, not used by l2j and only slows it down# Query cache is used to cache SELECT results and later return them# without actual executing the same query once again. Having the query# cache enabled may result in significant speed improvements, if your# have a lot of identical queries and rarely changing tables. See the# "Qcache_lowmem_prunes" status variable to check if the current value# is high enough for your load.# Note: In case your tables change very often or if your queries are# textually different every time, the query cache may result in a# slowdown instead of a performance improvement.query_cache_size=0  ------>If your tables are getting big, you should locate more to this.This should be more then enough though!# Maximum size for internal (in-memory) temporary tables. If a table# grows larger than this value, it is automatically converted to disk# based table This limitation is for a single table. There can be many# of them.tmp_table_size=256M --------->setting this to 8mb is more then enough# Additional memory pool that is used by InnoDB to store metadata# information. If InnoDB requires more memory for this purpose it will# start to allocate it from the OS. As this is fast enough on most# recent operating systems, you normally do not need to change this# value. SHOW INNODB STATUS will display the current amount used.innodb_additional_mem_pool_size=8M ------>This one is tricky and i dont know wich one is the best, never had time to test it allot.------>I do know that if you set it to "0" it will create major I/O spikes------>I havent had any problems using option "2"# If set to 1, InnoDB will flush (fsync) the transaction logs to the# disk at each commit, which offers full ACID behavior. If you are# willing to compromise this safety, and you are running small# transactions, you may set this to 0 or 2 to reduce disk I/O to the# logs. Value 0 means that the log is only written to the log file and# the log file flushed to disk approximately once per second. Value 2# means the log is written to the log file at each commit, but the log# file is only flushed to disk approximately once per second.innodb_flush_log_at_trx_commit=2 ------->Not needed to be any higher then 16Mb, comment of mysql explains itThe size of the buffer InnoDB uses for buffering log data. As soon as# it is full, InnoDB will have to flush it to disk. As it is flushed# once per second anyway, it does not make sense to have it very large# (even with long transactions).innodb_log_buffer_size=16M --------> Set this to the ammount of ram you have free for your database. At mine, 1024 is enough.# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and# row data. The bigger you set this the less disk I/O is needed to# access data in tables. On a dedicated database server you may set this# parameter up to 80% of the machine physical memory size. Do not set it# too large, though, because competition of the physical memory may# cause paging in the operating system. Note that on 32bit systems you# might be limited to 2-3.5G of user level memory per process, so do not# set it too high.innodb_buffer_pool_size=1024M ----------> I have always set this to 50% of my innodb_buffer_pool_size.----------> Remember if you change this you need to delete your log files in order to start mysql again.----------> They are located here: MySQL Server 5.0\data----------> you should delete ib_logile0 and ib_logfile1 if you change this.# Size of each log file in a log group. You should set the combined size# of log files to about 25%-100% of your buffer pool size to avoid# unneeded buffer pool flush activity on log file overwrite. However,# note that a larger logfile size will increase the time needed for the# recovery process.innodb_log_file_size=512M --------->The thread concurrency depends on your hardware.--------->you should play around with it a bit but if you cpu is a powerfull one you can easily set this to 60# Number of threads allowed inside the InnoDB kernel. The optimal value# depends highly on the application, hardware as well as the OS# scheduler properties. A too high value may lead to thread thrashing.innodb_thread_concurrency=60 -----> Setting so mysql uses your memory instead of the pagefilememlock -----> Creates a file for every table. this creates a minor performance boost-----> Remember, if you want this setting to work you have to add it to your config and then re-import your database again.-----> Only that way it will create a file per table.innodb_file_per_table  Ok that should cover mysql. I recommend getting a dedicated hardisk for your database. This will improve performance ALLOT!.   4.L2j Threadpool config Well we are getting there, only a couple of things left to do and your good to go.Lets start with the threadpool config.Here is defined how much threads can be created(Correct me if im wrong!).These settings have always been a mistery to me so i have experimented allot with them.Before setting them really high you should once again consider your cpu. Dont set them to high if you dont have a fast cpu. Open your options.properties file and look for this: # ================================================== ===============# Threads configuration - Take care changing this# ================================================== =============== ThreadPoolSizeEffects = 50ThreadPoolSizeGeneral = 65 #Default 2UrgentPacketThreadCoreSize = 10#Default 4GeneralPacketThreadCoreSize = 20#Default 4GeneralThreadCoreSize = 20 AiMaxThread = 20 ------------------------------------------------------------------ I have set them 5x the normal values. This works great for my server but you should experiment with it.Just start of with normal settings and see how it runs. If you think it lags and your cpu isnt used much just set it 2x normal values.You should play around with this until you find a nice configuration for your hardware.  5.Other configurations Ok now its time for some other configurations. --------> This should not log items, wil suck up your I/O load. Logging chat well, also not doing that for the same reason.# ================================================== ===============# Logging features# ================================================== ===============# Logging ChatWindowLogChat = False# Logging Item handling NOTE: This can be very space consuming if enabled for all items.LogItems = False# Log GM actionsGMAudit = True -------> Having them not running around can improve performance a bit# Maximum range mobs can randomly go from spawn pointMaxDriftRange = 0 -------> Definitly set this to false!# setting false can improve server performance on high rate serversPreciseDropCalculation = False -------> Dont know precisly wich is the best one but 1 has always worked great for me.# This is setting of experimental Client <--> Server Player coordinates synchronization,# 0 - no synchronization at all# 1 - parcial synchronization Client --> Server only * using this option it is difficult for players to bypass obstacles# 2 - parcial synchronization Server --> Client only# 3 - full synchronization Client <--> Server# -1 - Old system: will synchronize Z onlyCoordSynchronize = 1  6.Startup parameters Ok so everything is set to go just for one thing, your java startup parameters.This is just like the thread pool config, they should be configured for your hardware.There are a couple of things you need to think about.First, if you have 32 bit java you cannot set your xmx and xms higher then 1536Second, the -server parameter. You should only use this if you have 2 Cores or more and more then 2 GB ram! If you dont DO NOT USE this parameter.The difference between starting your server in -server mode is that it runs waaaaaay better. Will have less lag and better resource usage. So if you can, always use this.Other settings like ram all depend on how much you got. You should set these setting according to your ram size: -Xmx4096m -Xms2048m -Xmn1024m -XX:PermSize=256m.At my server this works great and i havent touched this for ages.  title Game Server Console:startecho Starting L2J Game Server.echo.REM -------------------------------------REM Default parameters for a basic server.REM java -Xmx1024m -cp ./../libs/*;l2jserver.jar net.sf.l2j.gameserver.GameServerREMREM If you have a big server and lots of memory, you could experiment for example withjava -server -Xmx4096m -Xms2048m -Xmn1024m -XX:PermSize=256m -XX:SurvivorRatio=8 -Xnoclassgc -XX:+AggressiveOpts -cp ./../libs/*;l2jserver.jar net.sf.l2j.gameserver.GameServerREM -------------------------------------  Ok thats it, a guide to configurate your server to be able to run 1000+ players. I have done it myself with this config with almost no lag.If someone has a comment on it please let me know, also when you think i made a mistake or something.The grammer in it well, dont have time to check it all so sorry if its not correct. Have fun with your gameserver.  Written by Nitrozik for the L2j CommunityLast edited by DaRkAnGeL; 01-18-2008 at 07:54 PM.. 
If someone can check it and tell us any idea about this it will be good...

Thanks...
kama3a
Posts: 125
Joined: Mon Jul 07, 2008 4:01 pm

Re: Mysql problem CPU usage

Post by kama3a »

Tnx for replay but already i made test on full innodb and full myisam, atm i use splited the charcaters skills, items and 2 more big tables are on myisam, others are on innodb.
I think that decrease the work of mysql but not alot.
I try with more then 100 configs for the mysql but for now not so good performance.

I will try with disable the cache but not so sure gonna work .. :)

tnx again anyone who have idea to share it.

p.s. can the sata II slows alot the server ? mybe the problem is that i dont use SSD..
seires
Posts: 73
Joined: Wed Dec 14, 2005 5:24 pm

Re: Mysql problem CPU usage

Post by seires »

kama3a wrote:Tnx for replay but already i made test on full innodb and full myisam, atm i use splited the charcaters skills, items and 2 more big tables are on myisam, others are on innodb.
I think that decrease the work of mysql but not alot.
I try with more then 100 configs for the mysql but for now not so good performance.

I will try with disable the cache but not so sure gonna work .. :)

tnx again anyone who have idea to share it.

p.s. can the sata II slows alot the server ? mybe the problem is that i dont use SSD..
Yes, your harddrives should be either SCSI 15k or SSD.
User avatar
jurchiks
Posts: 6769
Joined: Sat Sep 19, 2009 4:16 pm
Location: Eastern Europe

Re: Mysql problem CPU usage

Post by jurchiks »

ssd writes slow. Faster than 7,2k rpm, but slower than raptors.
If you have problems, FIRST TRY SOLVING THEM YOURSELF, and if you get errors, TRY TO ANALYZE THEM, and ONLY if you can't help it, THEN ask here.
Otherwise you will never learn anything if all you do is copy-paste!
Discussion breeds innovation.
kama3a
Posts: 125
Joined: Mon Jul 07, 2008 4:01 pm

Re: Mysql problem CPU usage

Post by kama3a »

Atm before time on my old machine was sata II again didnt had lag or delay problems.
Atm java and mysql make rly high pereformance, still checking all kind of configs but w/o so nice results.

the major issue stays, when player make subclass or add new one mysql start select insert etc.. from the charcater skills table - that table is bigest so it rly slows the mysql.
seires
Posts: 73
Joined: Wed Dec 14, 2005 5:24 pm

Re: Mysql problem CPU usage

Post by seires »

jurchiks wrote:ssd writes slow. Faster than 7,2k rpm, but slower than raptors.
SSD is faster than anything else on random writes/reads and has 0ms access time. At least a good SSD is, Intels X25-M G2 for example.
kama3a
Posts: 125
Joined: Mon Jul 07, 2008 4:01 pm

Re: Mysql problem CPU usage

Post by kama3a »

So any real suggestions? im sure the HDD`s type dont make such big problems on the CPU usage or to slow mysql and etc etc...
seires
Posts: 73
Joined: Wed Dec 14, 2005 5:24 pm

Re: Mysql problem CPU usage

Post by seires »

kama3a wrote:So any real suggestions? im sure the HDD`s type dont make such big problems on the CPU usage or to slow mysql and etc etc...
Well your hard drives are too slow to be hosting lots of people. But yea, that doesnt necessarily have to be the problem.

The more disk usage = the more CPU usage.
kama3a
Posts: 125
Joined: Mon Jul 07, 2008 4:01 pm

Re: Mysql problem CPU usage

Post by kama3a »

seires wrote:
kama3a wrote:So any real suggestions? im sure the HDD`s type dont make such big problems on the CPU usage or to slow mysql and etc etc...
Well your hard drives are too slow to be hosting lots of people. But yea, that doesnt necessarily have to be the problem.

The more disk usage = the more CPU usage.
all logs from the java and binary logs from mysql are stoped.
for now the CPU is acting normal but it had some strange peek at the morning.
Too much things for tests -.- ... any one can share good config ?
User avatar
badboy29
Posts: 417
Joined: Fri Apr 24, 2009 5:34 am
Location: Brazil

Re: Mysql problem CPU usage

Post by badboy29 »

Try...

Code: Select all

# The default storage engine that will be used when create new tables whendefault-storage-engine=INNODB # Set the SQL mode to strictsql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections = 300table_cache = 3000query_cache_size = 0tmp_table_size = 128Mmax_heap_table_size = 128Mthread_cache_size = 8thread_concurrency = 8memlocklog_slow_querieswait_timeout = 3600interactive_timeout = 3600query_prealloc_size = 16kthread_concurrency = 8max_allowed_packet = 16Mthread_stack = 192Ktransaction_isolation = READ-COMMITTED #*** MyISAM Specific optionsmyisam_max_sort_file_size = 10Gmyisam_max_extra_sort_file_size = 10Gmyisam_sort_buffer_size = 30Mkey_buffer_size = 187Mread_buffer_size = 64Kread_rnd_buffer_size = 256Ksort_buffer_size = 256k # *** INNODB Specific options *** innodb_additional_mem_pool_size = 8Minnodb_buffer_pool_size = 1Ginnodb_data_file_path = ibdata1:10M:autoextendinnodb_autoextend_increment = 8innodb_file_io_threads = 4innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 16Minnodb_log_file_size = 256Minnodb_max_dirty_pages_pct = 90innodb_flush_method=O_DIRECTinnodb_lock_wait_timeout = 120innodb_file_per_table = 1innodb_open_files = 2048innodb_support_xa = 0 skip-federated 
Aka UnHoly
shippou
Posts: 74
Joined: Thu Jun 25, 2009 9:27 pm

Re: Mysql problem CPU usage

Post by shippou »

Hello!

I running this version H5.
dp 9641
gs 6445

I have a problem with my server.
Im have a server machine with i5 3570k 8G ram 120 SSD as main hard disk.
Curretnly running windows 2003.

And the problem is there is a randomly occuring lagg effect.
Some char ported away.
I have self experience with my gm char.
I ported to giran my char is appeard but the town was empty, after re loggin i was in tlaking island.
My players have the same problem.

I tried on win2008 the lagg was banished but the server crashed in 1-3 days randomly whitout any sign. ( its have dayly auto restart )

It is very annoying.

I tried different java versions, without any effect.

Noting in errolog except the buffer errors but it happen every time when a char take on buff
máj. 12, 2015 6:16:29 DU com.l2jserver.gameserver.network.clientpackets.RequestBypassToServer runImpl
WARNING: [Character: Charname[268545764] - Account: Charname - IP: xxx.xxx.xxx.xxx] sent bad RequestBypassToServer: "npc_268479515_Buff 1268 1062 1243 1045 1048 1397 1242 1077 1240 1086 1043 1032 1036 1035 1068 1044 1182 1191 1033 1189 1040 1204"
java.lang.NullPointerException
at com.l2jserver.gameserver.model.actor.instance.L2NpcBufferInstance.onBypassFeedback(L2NpcBufferInstance.java:171)
at com.l2jserver.gameserver.network.clientpackets.RequestBypassToServer.runImpl(RequestBypassToServer.java:162)
at com.l2jserver.gameserver.network.clientpackets.L2GameClientPacket.run(L2GameClientPacket.java:70)
at com.l2jserver.gameserver.network.L2GameClient.run(L2GameClient.java:1091)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Anyone have ideas?
Post Reply