php/mySQL without limits please

Problems with the Windows version of XAMPP, questions, comments, and anything related.

php/mySQL without limits please

Postby exdelierium » 31. January 2012 00:51

Hi,
I need my PHP Server and MySQL DB be without limits, so it can process a request that takes hours(!).
I already set the maximum execution time of php to 10 hours and the memory_limit to -1 (unlimited) in the php.ini, which I checked in the phpinfo() and its set as it should be. But still, when I start a script to count the lines of my over 500 mySQL tables (the database is 50GB big) I get the error:

Fatal error: Out of memory (allocated 12582912) (tried to allocate 11534316 bytes) in C:\xampp\htdocs\do.php on line 40

My hardware shouldnt be the Problem (lots of RAM, HDD, etc. btw: its running on windows 7), and reading in the .csv data took my pc 5.5 hours, but worked without problems
(mysql_query("LOAD DATA INFILE '$datei' INTO TABLE $tab FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n';"))
Now I just wanna count the lines with the following script:

Code: Select all
    $result=0;
    for ($i=1;$i<=585;$i++) {
       if ($i!=438) {
          $tab="f".$i;
          echo $tab."<br>";
          $abfrage=mysql_query("SELECT ID FROM $tab")or die(mysql_error());
          $result=$result + mysql_num_rows($abfrage);
       }
    }
    echo $result;


Its getting to table 32+- and then gives mit the fatal error.

Does anyone know how this can happen? Is there something else I have to change to get rid of all the limits set?

Thanks for your help
Alex

PS: Please don't try to convince me php/mySQL is not good for 50GB data, I just have to use it :-)
exdelierium
 
Posts: 4
Joined: 31. January 2012 00:25
Operating System: Windows 7

Re: php/mySQL without limits please

Postby Sharley » 31. January 2012 01:02

In the mysql folder there are some template my.ini files that you might like to experiment with.

They are named with appropriate reference to their content settings.

To use, either change the settings in the \mysql\bin\my.ini file preferred (make a backup copy) or rename it and copy in it's place one of the more suitable files from the templates and rename it my.ini not forgetting to edit the Linux referenced paths to those from the original my.ini and see how those setting affect your issue.

The default my.ini file is for low end PCs - read the comments at the top of this file and so you may need to take at look at what settings you can change to good effect.

These suggestions may at least give you somewhere to start, remembering that a zero in the configuration file may mean 'no limit'.

Remember also to restart MySQL after any changes to the my.ini file you make to see the effects.

Good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: php/mySQL without limits please

Postby hackattack142 » 31. January 2012 01:33

If the above does not work, from what I have read, it is probably the MySQL client built into PHP that is running out of memory and it is not governed by the PHP Memory Limit directive. You could consider the SQL Query "SELECT COUNT(ID) FROM $tab" so you are not returning all the data back needlessly and also using the MySQL LIMIT keyboard to break down large queries into smaller ones.
XAMPP Control Panel Developer
Latest CP: viewtopic.php?f=16&t=48932
hackattack142
 
Posts: 703
Joined: 20. May 2011 23:29
Operating System: Windows 7 Ultimate SP1 64-Bit

Re: php/mySQL without limits please

Postby exdelierium » 31. January 2012 01:40

Thanks. But I already set all numbers in that file times 1000 and it doesn't change a thing (yes I restarted MySQL AND Apache after my change).
Anything else I can do?

@hackattack142
This is just a test-query. After that I have to process the data in several ways, so if it doesn't master this "easy" counting query the processing will be impossible.

FYI:
I have to analyze about 500 million stock transactions for my thesis in business economics. All tables consist of only 7 rows and all I need to do is anlalyze buys and sells for their cent-value (how many transactions for x dollar and 1 cent, x.02, x.03, ... , x.99). Should be very easy (just counting if's) if I could make my system do it without breaking up after a few seconds.....
exdelierium
 
Posts: 4
Joined: 31. January 2012 00:25
Operating System: Windows 7

Re: php/mySQL without limits please

Postby hackattack142 » 31. January 2012 02:36

If the memory limit you are hitting is indeed in the MySQL client of PHP, you could try freeing memory after each transaction and after you have processed the information from that query instead of relying on automatic cleanup http://php.net/manual/en/function.mysql-free-result.php
XAMPP Control Panel Developer
Latest CP: viewtopic.php?f=16&t=48932
hackattack142
 
Posts: 703
Joined: 20. May 2011 23:29
Operating System: Windows 7 Ultimate SP1 64-Bit

Re: php/mySQL without limits please

Postby exdelierium » 31. January 2012 02:58

Great! Seems to work. Thank you!
exdelierium
 
Posts: 4
Joined: 31. January 2012 00:25
Operating System: Windows 7

Re: php/mySQL without limits please

Postby exdelierium » 01. February 2012 23:56

Even though it does work now, it takes more than a day to do on step of my whole process. I saw in my task-manager that the cpu-usage is on average only 3%. Can I somehow increase that to do things faster?
Thanks
Alex
exdelierium
 
Posts: 4
Joined: 31. January 2012 00:25
Operating System: Windows 7

Re: php/mySQL without limits please

Postby hackattack142 » 02. February 2012 23:35

I do not use PHP so I cannot give you specifics other than what I can find on Google but if you are desperate, you could look into parallel processing/multithreading and it also depends if there are any bottlenecks elsewhere in your system or hardware.
XAMPP Control Panel Developer
Latest CP: viewtopic.php?f=16&t=48932
hackattack142
 
Posts: 703
Joined: 20. May 2011 23:29
Operating System: Windows 7 Ultimate SP1 64-Bit


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 40 guests