Massive Data + SQL - Need help from experts lol

[xandeck]

Hello all,

Please, I need some help from the expert scripts guys from this forum that had worked with SQL or MySQL with UNITY.
I really think this can help others users too that plan to use MySQL or store datas using Unity for some database.

I’m in a middle of a project where we store a massive amount of data, using MySQL and a DLL component of MySQL (MySql.Data.dll). Forget everything about security, because this is totally local and will not be used elsewhere, only inside where I work.
This project is more an application test (common software) than a game, just FYI.

For each real second, strings comming from 10 iPods (via network - WiFi) are stored inside a Unity Application Server (made in Unity PRO 2.6.1). The iPods applications were also developed inside Unity iPhone Advance 1.7. The data are just a number, but in string. Very easy so far.

So I store all 10 iPods datas into my MySQL local server each real second, with the datas showing in my server screen. This same second, I need to calculate the Average value of all iPods datas that came to the server. All these works nicelly for about 40 minutes recording… or a little more.

Beyond this time, my Unity server crashs and stays freezed, but my iPods applications can still send datas and the MySQL server are recording (this is one strange thing).

The fact is, I dont know if this happens because of UNITY or any other software running at same time with it (programs like LOGMEIN, TOMCAT, etc, is running at same machine). The PC is a Core 2 Duo, 4 GB RAM memory and sometimes is XP, Vista or 7.
I really think the problem is not in my code script, because it is not anything from other world, the code just get the datas, save into strings and send it to MySQL.

Question: anybody had any experience using MySQL dll to work with UNITY (programming with C# inside UNITY) with massive data?

I really thank you for any help.
Moderators/Devs, if you have any experience with this…

[zumwalt]

You might be reaching a cap of open connections and your memory use on the server has reached 4 gig, look at task manager to see what your program on the server is up to as far as memory goes. You want to use a command that executes nonquery to load the data into mysql. If at all possible, you want to open 1 and only 1 connection and leave it open with only updating your command object and doing the inserts, otherwise if you are constantly opening and closing the connection, something can hang. I can only give some advice without knowing what is realy going on, MySQL can handle thousands of inserts a second, but the OS can only handle hundreds, so you are defininately not over running MySQL, so it has to be something else.

[xandeck]

Hey zumwalt, thank you for the help.

Ok, I will try to answer your questions:

• I use only one connection to the MySQL server, this comes from the Unity Server application, and I do not close everytime I need to insert/delete/update data.
• I use that DataReader from MySQL and I only open and close Commands very often.
• I saw that in some PCs I tested, the memory keep going up every second, this might be the problem… but others PCs dont… only when the iPods are sending data.

I think MAYBE (I’m not sure) the problem could be when you select all infos from a table to search for a specific information, what you think? Because, after 1 hour of datas been inserted, in one table I have more than 50.000 data, and one of my scripts needs to search this table to insert data and to select data… do you think this can cause the hang?

Anyway, imagine a scenery game, I have only 10 players logged and registered, they send data every second (actually, every UPDATE frame of the UNITY game, but I only insert data into MySQL every second) and in this same second, my UNITY server application calculates an average value (like a life points) of every player and then stores this into a table, followed by the time, name of the player and 2 more datas.

[zumwalt]

Are you saying that on every frame or every second you are trying to do a read back or a search for information from the data that is inserted ??? The reader is only good for the records from the read to the end of that read stream as it is. I don’t know if MySQL can do stored procedures, I need to look that up, but in SQL, I use stored procedures to do my inserts and updates and I use a datareader to look at a VIEW, not the table that is holding the information, on top of that, please tell me that your tables have primary keys and are indexed.

Just checked, the latest versions of MySQL have what is called “Routines”, not sure how far back they date, but they are the same as a stored procedure I think, are you using routines?

[xandeck]

Yes, zumwaly, I think its pretty much what you said. I need to make an insert and then an update (and select) on the tables.

What do you mean by “read to the end of that read stream as it is”?

I worked with SQL before, I used stored procedures, but just little bit and I did not try in MySQL. I dont know hot do it.

I use that command below (before this, I open a connection with MySQL, of course):

IDbCommand dbCommand1 = MySQL.Instance.conexaoDB.CreateCommand();
        dbCommand1.CommandText = sqlQuery;
        IDataReader sqlReader1 = dbCommand1.ExecuteReader();

        while (sqlReader1.Read()) {
                //do stuff
        }

        sqlReader1.Close();
        sqlReader1 = null;
        dbCommand1.Dispose();
        dbCommand1 = null;

That’s an example what I’m doing. From every update game, I need to insert some data and then, after insert, I need to select from the data I just inserted and make another insert. Two times this.

I wonder… if I use arrays to store the first datas, and after I use I clean the array… will improve performance? (it will be one less insert in the table).

[zumwalt]

Drinking my first cup of cofee, well, good news is that I have ran into this situation before, and that block of code is the problem. Just a short history while I look back at what I did to solve my issue. I am inserting/updating approximately 1 million records doing a search first for the record and updating it if found, if not then insert. However, I am doing it in a stored procedure in SQL, the procedure returns to me nothing because I am executing the procedure non query and I want nothing back from it. In your case, you want something back, probably in the same routine as your insert/update as long as I am understanding this, it is really all one fat routine. Tell me if I am not getting this but from everything you said, what you are doing is the following all in one swoop (or attempting to do)

1. look to see if entry exists, if so update it
2. if not add it
3. after insert/update then calculate
4. return calculation, display to client or someone/something

What version of MySQL are you using? I have version 5.1.8 server with 5.2 tools installed on my local machine. I could look into Routines in MySQL and see about making a moch routine that would do all of that in one call.

Datareaders are forward looking only, they are fast and effecient but once they pass a record, the do not look back, once they land at the end of the record list, they stop, if you are inserting and updating hundreds of records a second, the reader can easily read a record in one state, and that very next second, the record has a new state, by the time it hits the last known record and stops, a second after that, 100 new records could exist.

[xandeck]

You got the idea - that’s pretty much of it.

Look below:

0. Insert some values (INT and Strings) data into mysql table A. The most important values is an INT and a TIMESTAMP that stores the date. Everything together sums 8 colums, with only one primary key (ID) + auto increment.

1. Every real second, the method above executes.

2. In this same second (but not exacly in the same game frame) I need to get all records from table A from that second and calculate the average value (example: 10 iPods stores 10 INTs, I calculate the average of these 10 iPods). This will store in one colum of the table B.

3. Then I check for other values, etc, if the record above is in the same second, I store this in the second colum of table B. I do this 10 times (its the maximum data).

4. So far 2 tables: A, records all values per second; B, stores the average values of table A, per second. With this, we can say for every 10 records in table A, I have one in table B (but calculates 10 times and reads/update this same row in table B).

5. This average value per second is just stored, it does not need to display in anywhere else. Except that the values of the 10 iPods is show in the server window (like life points that needs to stay in the screen all the time).

MySQL: 5.1.46

I use phpMyAdmin, with PHP and Apache in my local server, but also I use MySQL Admin Tools.

Now that you have talked about this routines, I will search for it… I dont know how to do it yet.

And so, do you think these IDataReaders are the problems? I can’t open another IDataReader with one still open… thats a problem to me, because this could save me some time (not really performance gain). As for what I tested here, it seems the whole problem is because of the amount of data that my code needs to read.

[zumwalt]

I pretty much understand your “big picture” now, so this leads me to a few more questions, are you using Unity to perform all of these database tasks? We need to change that datareader and you will need to write a routine to handle the insert/update between the tables based on your business logic rules.

Here is the base “logic” that should fix this issue, going to take some tweaking though and a routine, you are going to have to live with a delay. First you will need to setup a List<> that will hold the values to be inserted, this is just a buffer anyway and will be added too and removed from as the buffer is dumped into MySQL. You will need a structure that will be the same values of your table, example, if your table has 10 fields:

Field1, … Field10
Your structure would be (in C#)

public struct DatabaseData
{
  public object Field1;
  public object Field2;
  public object Field3;
  public object Field4;
  public object Field5;
  public object Field6;
  public object Field7;
  public object Field8;
  public object Field9;
  public object Field10;
}

Notice how I am using “object” and not a specific type?? There is a distinct purpose in doing this and it matters. When we fill that Field1 with something, it becomes that somethings database type, not value type in our case. Long story, trust me.

Our List will contain an array of type DatabaseData, so List tmpData will suffice for the purposes we need. I will assume you know about Lists and how to use them. Now, your fun part is going to be the reader call. We need a select statement that will pull a subsequent list of records from a view based on your business logic rule, you have this already written most likely, now, here is the real question, what part of the data MATTERS! If only the calculations, guess what, you can create and calculate that without it ever hitting the database and only store the results, that said, let me slow down (4 cups of cofee, bouncing off walls), so what exactly do you have to have stored?

[xandeck]

The logic must work… nice

Yes, I’m using Unity to calculate, insert, update and select data.
The delay… you mean just reading right? Because delay for the store data is a problem…
Yes, I know how to handle lists, dont worry.
So, basically, you saying that I should store the datas into some variable to calculate the average values, instead of saving into database, read, calculate, and then save it again? If so, will this not cause a memory crash too? Imagine 40 thousand records… will you store this amount into variables?

A routine you mean inside Unity routine or the mysql routine?

The only part that matters to me is the average values that the iPods sends every game/frame, but only stores every real second.
The big problem is: it must save at real second, I mean, I cannot store after 1 second more.

[zumwalt]

You are putting too much emphasis on time, you see, you have this wonderful thing called DateTime.Now, and you can have a value / field that can store that information! So your structure can have that field and the database can have that field, so when PlayerA’s information is brought into the grand list which can hold up to 4 gig of information or more at any given time (but you should never have that much in it anyway), your calculations can be a filter of the time based on that time generated. The buffer is something to throw at MySQL and store what is in the buffer, while your loop happens to store the information, after the store is successful, you simply delete it from the List.

You are then calculating on the fly also, and you can have queries that say, give me all data where time >=start and time <=end, so you have all data for that second, you MUST have a field to store that generated time though. This means you can calculate the results -any time- for any current or past time, even do mapping for spikes through that process.

Routine in MySQL not in Unity.

I think I need to unwrap your brain on this, your putting too much work into reading and inserts/updates than you need to. You do know that time is only as good as the delay.

[xandeck]

I’m already suing DateTime.Now. The code below returns me a string in the format I want, look below:

      public string GetTime() {
		string horaFormat = "";
		string minFormat = "";
		string segundoFormat = "";
		string diaFormat = "";
		string mesFormat = "";
        dataAtual = DateTime.Now;
        
		varHora = dataAtual.Hour;
		varMinuto = dataAtual.Minute;
		varSegundo = dataAtual.Second;
		
		varDia = dataAtual.Day;
		varMes = dataAtual.Month;
		varAno = dataAtual.Year;
		
		if (varDia >= 0.0f  varDia <= 9.0f){
			diaFormat = "0" + varDia.ToString();
		} else {
			diaFormat = varDia.ToString();
		}
		if (varMes >= 0.0f  varMes <= 9.0f){
			mesFormat = "0" + varMes.ToString();
		} else {
			mesFormat = varMes.ToString();
		}
		
		if (varHora >= 0.0f  varHora <= 9.0f){
			horaFormat = "0" + varHora.ToString();
		} else {
			horaFormat = varHora.ToString();
		}
		if (varMinuto >= 0.0f  varMinuto <= 9.0f){
			minFormat = "0" + varMinuto.ToString();
		} else {
			minFormat = varMinuto.ToString();
		}
		if (varSegundo >= 0.0f  varSegundo <= 9.0f){
			segundoFormat = "0" + varSegundo.ToString();
		} else {
			segundoFormat = varSegundo.ToString();
		}
		currentTime = varAno + "" + mesFormat + "" + diaFormat + "" + horaFormat + "" + minFormat + "" + segundoFormat;
		currentTimeBD = varAno + "-" + mesFormat + "-" + diaFormat + " " + horaFormat + ":" + minFormat + ":" + segundoFormat;
		diaResposta = diaFormat + "-" + mesFormat + "-" + varAno;
		return currentTime;
    }

In variable currentTime I store the date I need. Then I only select the datas based on the current time/second I want.
I’m already filtering everything based on time and the iPods IDs I want.

The code below save the first data into table A.

public void SaveDatas(string id_aparelho, string horario, string valor, string useMedia, string local) {
		string sqlQuery = "INSERT INTO registros (id, id_aparelho, horario, valor, usaMedia, local) VALUES ('0', '"
		+ id_aparelho + "', '" + horario + "', '" + valor + "', '" + useMedia + "', '" + local + "')";
		
        IDbCommand dbCommand = conexaoDB.CreateCommand();
        dbCommand.CommandText = sqlQuery;
        IDataReader reader = dbCommand.ExecuteReader();
		
		reader.Close();
        reader = null;
        dbCommand.Dispose();
        dbCommand = null;
    }

And the code below calculates one of the average value.

	public void SaveAveragePotValue (){
		string sqlQuery = "SELECT AVG(valor) As ValorMedio FROM registros WHERE usaMedia = 'True' GROUP BY horario";
		float avValue = 0.0f;
		IDbCommand dbCommand1 = MySQL.Instance.conexaoDB.CreateCommand();
        dbCommand1.CommandText = sqlQuery;
        IDataReader sqlReader1 = dbCommand1.ExecuteReader();
		
		while (sqlReader1.Read()) {
			//ver valor medio
			averagevalue = "0";
			//Debug.Log(sqlReader1["ValorMedio"]);
			averagevalue = (sqlReader1["ValorMedio"]).ToString();
			//avValue = (float)Convert.ToDouble(averagevalue);
			avValue = float.Parse(averagevalue);
			avValue = Mathf.Round(avValue * 1000);
			avValue = avValue/1000;
			//Debug.Log("Valor medio alterado: " + avValue);
			averagevalue = avValue.ToString();
			averagevalue = averagevalue.Replace(",", ".");
		}
		
		sqlReader1.Close();
        sqlReader1 = null;
        dbCommand1.Dispose();
        dbCommand1 = null;
		
		//CALCULA DESVIO PADRAO ****************************
		sqlQuery = "SELECT * FROM registros WHERE usaMedia = 'True' AND horario = '" + currentTimeBD + "'";
		IDbCommand dbCommand3 = MySQL.Instance.conexaoDB.CreateCommand();
        dbCommand3.CommandText = sqlQuery;
        IDataReader sqlReader3 = dbCommand3.ExecuteReader();
		
		float[] qtdeCalculos = new float[QView.Instance.arrClients.Count];
		int cont = 0;
		float mediaValores = 0f;
		float somaDP = 0f;
		while (sqlReader3.Read()) {
			qtdeCalculos[cont] = float.Parse((sqlReader3["valor"]).ToString());
			mediaValores += qtdeCalculos[cont];
			//Debug.Log(cont + ": " + qtdeCalculos[cont]);
			cont++;
		}
		
		//Debug.Log("Media: " + mediaValores);
		
		mediaValores = mediaValores / QView.Instance.arrClients.Count;
		cont = 0;
		while (cont < QView.Instance.arrClients.Count){
			qtdeCalculos[cont] = Mathf.Pow((qtdeCalculos[cont] - mediaValores),2);
			//Debug.Log("Novo " + cont + ": " + qtdeCalculos[cont]);
			cont++;
		}
		
		cont = 0;
		while (cont < QView.Instance.arrClients.Count){
			somaDP += qtdeCalculos[cont];
			cont++;
		}
		
		somaDP = somaDP / cont;
		desvioPadrao = (Mathf.Sqrt(somaDP)).ToString();
		
		//Debug.Log("somaDP: " + somaDP);
		//Debug.Log("desvioPadrao: " + desvioPadrao);
		
		float arredonda = float.Parse(desvioPadrao);
		arredonda = Mathf.Round(arredonda * 1000);
		arredonda = arredonda/1000;
		desvioPadrao = arredonda.ToString();
		desvioPadrao = desvioPadrao.Replace(",", ".");
		
		//Debug.Log("arredonda: " + desvioPadrao);
		
		sqlReader3.Close();
        sqlReader3 = null;
        dbCommand3.Dispose();
        dbCommand3 = null;
		
		//SALVAR DADOS
		sqlQuery = "INSERT INTO evento (tipo, dh_evento, valor, dp, qtde_p, valor1, valor2, valor3, valor4, valor5, valor6, valor7, valor8, valor9, valor10) VALUES ('"
		+ nomeCampoMedia + "', '" + currentTime + "', '" + averagevalue + "', '" + desvioPadrao + "', '" + (QView.Instance.arrClients.Count).ToString()
		+ "', '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0' " + ", '0')";
		IDbCommand dbCommand2 = MySQL.Instance.conexaoDB.CreateCommand();
        dbCommand2.CommandText = sqlQuery;
        IDataReader sqlReader2 = dbCommand2.ExecuteReader();
		
		sqlReader2.Close();
        sqlReader2 = null;
        dbCommand2.Dispose();
        dbCommand2 = null;
		
		desvioPadrao = "0";
	}

The code above can be a little confusing for your, because I’m not giving you all the variables necessary to understand, but what I want to show you how I’m doing it. If you want, I can explain better to you by MSN or GMAIL, or anyother live chat

EDIT: I just made a test… I’m runnin the server inside a Core i7 with 8 GB memory… funny is that the server is not eating memory… in fact, is very stable… but If I throw it to a XP PC, it starts to increase memory every second… maybe that’s why it crashs. But I don’t think my server needs just a Core i7 with 8 GB of Ram…

EDIT 2: sometimes it crashs, and sometimes not… WTF :lol:

[zumwalt]

Direct chat will have to wait until weekend, however, I already know one performance boost right off the bat, datatables. You can do the calculations with it without waiting on the readers or in place reading, you are doing work on the data while the reader is still open per record, not efficient, works, but not efficient. This whole process needs redone, one thing though that can free up your reading would be something like:

PSEUDO CODE ONLY, NOT OPTIMIZED, and obviously off the hip without a way to test it at all, also you need to learn about parameters… badly

      //CALCULA DESVIO PADRAO **************************** 
      DataTable dt = new DataTable("thedata");
      sqlQuery = "SELECT * FROM registros WHERE usaMedia = 'True' AND horario = '" + currentTimeBD + "'"; 
      IDbCommand dbCommand3 = MySQL.Instance.conexaoDB.CreateCommand(); 
      dbCommand3.CommandText = sqlQuery; 
      IDataReader sqlReader3 = dbCommand3.ExecuteReader(CommandBehavior.CloseConnection); 
      dt.Load(sqlReader3);
       
      float[] qtdeCalculos = new float[QView.Instance.arrClients.Count]; 
      int cont = 0; 
      float mediaValores = 0f; 
      float somaDP = 0f; 

      if(dt.Rows.Count>0)
      {
         foreach(DataRow r in dt.Rows)
         {
           qtdeCalculos[cont] = float.Parse(r["valor"]).ToString()); 
           mediaValores += qtdeCalculos[cont]; 
           cont++;
         }
      }

      mediaValores = mediaValores / QView.Instance.arrClients.Count; 
      cont = 0; 
      while (cont < QView.Instance.arrClients.Count){ 
         qtdeCalculos[cont] = Mathf.Pow((qtdeCalculos[cont] - mediaValores),2); 
         cont++; 
      } 
       
      cont = 0; 
      while (cont < QView.Instance.arrClients.Count){ 
         somaDP += qtdeCalculos[cont]; 
         cont++; 
      } 
       
      somaDP = somaDP / cont; 
      desvioPadrao = (Mathf.Sqrt(somaDP)).ToString(); 
              
      float arredonda = float.Parse(desvioPadrao); 
      arredonda = Mathf.Round(arredonda * 1000); 
      arredonda = arredonda/1000; 
      desvioPadrao = arredonda.ToString(); 
      desvioPadrao = desvioPadrao.Replace(",", "."); 
       
      if(sqlReader3 != null)
      {      
         sqlReader3.Close();
         sqlReader3.Dispose();
      } 
      
      if(dbCommand != null)
      {
        dbCommand3.Dispose();  
      }

NOTE, i threw this together, in datatables, you can use the compute and sum a column so you can get rid of walking the rows to sum a column, look at this web link for more info on computing the column:

DataTable.Compute(String, String) Method (System.Data)

[xandeck]

Thank you, I will take a look right now

[zumwalt]

FYI, datareaders have this nasty habbit of becoming habititually stuck… open… and you can’t close them until what ever has them stuck (aka read past end of data set) is cleaned up, a trick to avoiding this is to get rid of

   if(sqlReader3 != null) 
      {      
         sqlReader3.Close(); 
         sqlReader3.Dispose(); 
      }

With the CommandBehavior.CloseConnection, once the reader reach’s a certain threshold, the garbage collector will allow it to be cleaned out, that is why your server hangs, it hangs on attempting to close the reader. Sadly, if the reader can never be released, which happens, you end up with a memory leak in your program. To resolve this problem, you schedule a restart of your program during off hours, I have had that happen before. I hate that about datareaders, but it is simple fact.

[xandeck]

This all makes senses better to me now… I just saw a problem where occured in the MySQL, and them the memory problem started in my PC and it crashed Unity… I will try to focus on this formats you gave to me and test it here

[xandeck]

Hey zumwalt, I cant use the “CommandBehavior.CloseConnection” command, it returns me an error: “Connection must be valid and open”

[zumwalt]

That is not why it is throwing that error That error is thrown later when you try to close the reader, remove the reader close code when using that option.

[xandeck]

Ok, but what option is better?

[zumwalt]

CommandBehavior.CloseConnection is better

[xandeck]

Ok, I will change everything today and make a test.

Thanks again 8)