MySQL Transfers: High CPU

Page: 1 2
ID:1872728
 
Jun 12 2015, 10:31 pm
BYOND Version:508
Operating System:Windows 8 64-bit
Web Browser:Chrome 43.0.2357.124
Applies to:Dream Seeker
Status: Open
Issue hasn't been assigned a status value.
Descriptive Problem Summary: SQL queries are causing CPU spikes, and in our particular use of it we're transferring players from server to server. When the code is waiting for a response from the remote MySQL server, it seems to wait that amount in real-time.

Also, if the MySQL server goes offline or stops responding, the server essentially crashes.

We spoke to this issue with Lummox before and he acknowledged that something unusual was happening.
Jun 12 2015, 11:32 pm
The MySQL Implementation seems very slow in general on BYOND for some reason.
Jun 13 2015, 6:35 am
This isn't CPU usage, it's because the SQL query is blocking.
Jun 17 2015, 11:12 pm
Whoops. This isn't a beta bug - could it be moved to 'bug reports'?

Thanks.
Jun 29 2015, 6:17 am
How are we measuring the CPU here?
Jun 29 2015, 9:36 pm
You can easily see that there is a "pause" when using

http://www.byond.com/forum/?post=1881983

Just compile run and watch the profiler, while using the verb Do i get anything, which runs the proc Check_Payment_System()

Check_Payment_System() does a total of two MySQL queries (small queries) but it seems DreamSeeker freezes for a good 1 - 2 seconds.

Of course this could be down to latency I'm around 253ms from mysql.byondpanel.com

Payment Check() also freezes DS..

A way around this is to have MySQL on each box that holds each server allowing for quick localhost calls, you can set up Master to Master replication with MySQL to force all the records across the "cluster", ideally you shouldn't need to but it'll work.
Jun 30 2015, 1:53 pm
In response to Stephen001
Here's a quick snapshot of a CPU profile:
http://puu.sh/iIA29/e2db5e166b.png

A single MySQL query (regardless of size or complexity, it seems) takes roughly 0.068 cpu and will send world.cpu spiking up to +50% higher than normal values for as long as it the query takes to fully execute.

We have a background process running that updates the amount of players in a several on an external database, for example, every 5 seconds. The query looks something like:

UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]'


Whereas our much larger query used for updating the `player_savefiles` table, which takes up noticeably no more cpu than the previous query, looks like:

var/header = "(`Name`, `Ckey`, `Slot`, `Coords`, `Dir`, `Preview`, \
    `Gender`, `HP`, `MP`, `EXP`, `EXP_Next`, `Level`, `Max_HP`, `Max_MP`, \
    `Melee`, `Agility`, `Spell`, `Techniques`, `Accessories`, `Spell_Points`, \
    `Stat_Points`, `Class`, `PVP_Score`, `Craft_Mem`, `Checkpoint`, `Misc_Data`)"

var/values = "('[P.name]','[ckey(P.key)]',[slot],'[coords]',[P.dir],\
    '[encoded_preview]',[gender],[P.hp],[P.mp],[P.exp],[P.exp_next],[P.lvl],[P.max_hp],\
    [P.max_mp],[P.melee],[P.agility],[P.spell],'[encoded_techs]','[P.write_accessories()]',\
    [P.spell_points],[P.stat_points],'[P.class]',[P.pvp_score],'[list2text(P.crafting_memory, ";")]','[P.checkpoint]','[assoc2text(P.misc_data)]')"

query = "INSERT INTO `player_savefiles` [header] VALUES [values]"
Jun 30 2015, 2:31 pm
world.cpu doesn't measure how much your CPU is actually being used. What it measures is how much of the allotted tick time the server spends running procs. While it's waiting for a response from MySQL it's doing nothing, so the actual CPU usage is low, but world.cpu will be higher because it's measuring the ability to complete the tick on time.
Jun 30 2015, 2:32 pm
In response to Lummox JR
I actually did not know that. Pretty informative, thank you!
Jun 30 2015, 2:49 pm
How big roughly is this servers table (in terms of number of rows), and what's the latency like between the BYOND server and the MySQL server?

Similarly, are you able to operate a mysql native client on the same server as the BYOND server, if it's remote to the MySQL server?
Jun 30 2015, 2:53 pm
The `servers` table looks like this:

CREATE TABLE `servers` (
  `Network` varchar(100) NOT NULL COMMENT 'The name of the cluster.',
  `Address` varchar(100) NOT NULL,
  `Type` varchar(50) NOT NULL COMMENT 'The type of server this represents',
  `Id` tinyint(3) unsigned NOT NULL COMMENT 'The ID of this server in the cluster.',
  `Players` mediumint(8) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


The table doesn't have an index, could that cause any prbolems even if the table doesn't hold any more than 1-10 rows?
Jun 30 2015, 2:59 pm
Here I am pinging from my machine (which I am testing on) to the MySQL server:




And no, I'm not able to operate a mysql client on the same server as the byond server.
Jun 30 2015, 3:06 pm
That's a shame. Can you run a MySQL client local to the server? I'd be interested in you running the following

EXPLAIN UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]'

But substituting all the BYOND variables for appropriate values you currently find in the database. I kind of wonder if MySQL is basically constantly hard-parsing the query every time as you don't have bind variables for your parameters, and that MySQL's server is loaded on CPU.

Can you get CPU load, memory use and disk I/O statistics from the machine the MySQL server is running on at all? If it's a standardish linux SSH you have to the MySQL server, I can provide commands to support that.
Jun 30 2015, 3:18 pm
Unfortunately I'm using MySQL 5.5 and not 5.6, is the version that supports EXPLAIN UPDATE.

Not sure if this will help, but running EXPLAIN SELECT gives me the following:

EXPLAIN SELECT * FROM `servers` WHERE `Address`='192.168.1.233:5000'

------------
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,servers,ALL,,,,,1,Using where
------------


It's standard linux, also. I'm not too savvy with it, so I'd appreciate the commands.
Jun 30 2015, 3:29 pm
In response to Doohl
Doohl wrote:
Unfortunately I'm using MySQL 5.5 and not 5.6, is the version that supports EXPLAIN UPDATE.

Not sure if this will help, but running EXPLAIN SELECT gives me the following:

> EXPLAIN SELECT * FROM `servers` WHERE `Address`='192.168.1.233:5000'
>
> ------------
> id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
> 1,SIMPLE,servers,ALL,,,,,1,Using where
> ------------
>


It's standard linux, also. I'm not too savvy with it, so I'd appreciate the commands.

free -m Free RAM

top List of processes + CPU

dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync disk I/O
Jun 30 2015, 3:54 pm
Alternatively for ongoing disk I/O you've got:

iostat -d

or

iostat -n (if it's mounted on NFS, if in doubt, run anyway)

or

vmstat -d
Jun 30 2015, 3:58 pm
The explain plan was a bit vague, sadly. Try the following:

SET profiling = 1;
UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]';
SHOW PROFILES;
// Select the query ID for your update for use below:
SHOW PROFILE ALL FOR QUERY [query id];
Jun 30 2015, 4:06 pm
Here's all that run in a visual SQL program:

http://puu.sh/iIJSQ/6eca155135.png


As for the CPU/IO/Memory dump, do you want me to get it while receiving / executing queries?
Jun 30 2015, 4:13 pm
In response to Stephen001
Also ioping

Doohl RAM should be OK to do now, its mainly disk I/O that'll be the issue IMO.

A lot of people will use SSD drives (especially on a VPS) for a MySQL server, VPS mainly share drives (and I/O) with the other containers, which could cause your hdd speed to slow down..
Jun 30 2015, 4:15 pm
Unfortunately this is where a native MySQL client on the BYOND server would have been handy, to see what kind of effect the latency is playing on the profile, as that's the last of the environmental things worth exploring.

Which proc in your profile is running that update query (directly or indirectly through another call)? Might be worth sorting by total CPU, to help pick it out, if it's a hog.

Also yep, would agree with ATHK. You should be able to do a quick check of all the stats now really, and we're probably most interested in disk I/O (unless you're over-committed on RAM I guess, but it seems like any paging isn't affecting the query tons).
Page: 1 2