Page 1 of 1

php/mySQL without limits please

PostPosted: 31. January 2012 00:51
by exdelierium
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
Now I just wanna count the lines with the following script:

Code: Select all
    for ($i=1;$i<=585;$i++) {
       if ($i!=438) {
          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

PS: Please don't try to convince me php/mySQL is not good for 50GB data, I just have to use it :-)

Re: php/mySQL without limits please

PostPosted: 31. January 2012 01:02
by Sharley
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. :)

Re: php/mySQL without limits please

PostPosted: 31. January 2012 01:33
by hackattack142
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.

Re: php/mySQL without limits please

PostPosted: 31. January 2012 01:40
by exdelierium
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?

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.

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.....

Re: php/mySQL without limits please

PostPosted: 31. January 2012 02:36
by hackattack142
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

Re: php/mySQL without limits please

PostPosted: 31. January 2012 02:58
by exdelierium
Great! Seems to work. Thank you!

Re: php/mySQL without limits please

PostPosted: 01. February 2012 23:56
by exdelierium
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?

Re: php/mySQL without limits please

PostPosted: 02. February 2012 23:35
by hackattack142
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.