xoops forums

irmtfan

Module Developer
Posted on: 2012/9/24 7:18
irmtfan
irmtfan (Show more)
Module Developer
Posts: 3419
Since: 2003/12/7
#1

how to avoid strange errors when we reach to memory_limit?

In xoops when a script exceed the memory limit the user/visitor/webmaster get some strange errors.
sometimes it is a blank page and sometimes the famous 500 error.
Therefore it would be very hard for simple webmasters and even hard for experienced webmasters to track and find the real error.

how can we enhance the xoops logger and xoops error system for that purpose?
we have the ini_get("memory_limit") so i dont know whether it is good to check the memory by memory_get_usage? every time.

eg: https://xoops.org/modules/newbb/viewpost.php will use a huge memory to save all posts in an array. IMO it is above 64M for xoops.org (Mamba could tell us the exact memory usage)
currently we cannot use SplFixedArray in core/modules because it needs php 5.3 but most of servers still use 5.2


zyspec

Module Developer
Posted on: 2012/9/24 14:22
zyspec
zyspec (Show more)
Module Developer
Posts: 1048
Since: 2004/9/21
#2

Re: how to avoid strange errors when we reach to memory_limit?

irmtfan,

The core team could look at registering a shutdown function that tries to trap the error and, depending on an improved logging capability, be able to put out a message a little more 'user friendly'. Unfortuantely the PHP script is shutting down so improving the message might be about all that could be done. They could 'try' and write to a log file, etc. but since the system is attempting to recover from an error there wouldn't be any guarantee that the log could be updated...

An improved version of something like http://php.net/manual/en/function.set-error-handler.php#88401 might be able to help. It would be an interesting idea for the core team to research to see what could be done though. I recommend adding this to the SVN feature request on Sourceforge.

irmtfan

Module Developer
Posted on: 2012/10/11 7:23
irmtfan
irmtfan (Show more)
Module Developer
Posts: 3419
Since: 2003/12/7
#3

Re: how to avoid strange errors when we reach to memory_limit?

Thank you zyspec
I didnt forget your advice to send it to the tracker.
but I still struggling with this issue.
You know just very big websites with large DB may have these issues.
I also consider that in 99% of cases we have this issue when we use COUNT on the whole table to find all indexes mainly for creating a pagenav in the page. In other cases we always use LIMIT but in COUNT we cannot use LIMIT.

is there anyway to overcome this issue? like an increment COUNT
Now we use this:
$sql 'SELECT COUNT(*) as count'.

Now i have 500 error in my website in newbb because of the above.
I temporary disable the page navigation in viewpost in my website.

kerkyra

Just can't stay away
Posted on: 2012/10/11 13:17
kerkyra
kerkyra (Show more)
Just can't stay away
Posts: 553
Since: 2005/2/14
#4

Re: how to avoid strange errors when we reach to memory_limit?

maybe if you dont use the count in mysql, and just use count(array()) in php to get the size of the results array??? just a suggestion.

zyspec

Module Developer
Posted on: 2012/10/11 14:12
zyspec
zyspec (Show more)
Module Developer
Posts: 1048
Since: 2004/9/21
#5

Re: how to avoid strange errors when we reach to memory_limit?

Sounds like there must be something else going on. Count(*) is pretty fast on an indexed MyISAM database. You might try counting the index (NOT NULL) column something like:

$sql 'SELECT COUNT(`id`) AS count';


You could try doing an MySQL 'explain' on the table in PHPMyAdmin to see if it really is taking an unreasonable amount of time to execute. You might also try repairing the table to see if there's a problem.

irmtfan

Module Developer
Posted on: 2012/10/13 5:20
irmtfan
irmtfan (Show more)
Module Developer
Posts: 3419
Since: 2003/12/7
#6

Re: how to avoid strange errors when we reach to memory_limit?

zyspec:
Thank you for your help.
Firstly i should explain more about my specific issue.
1- i am sure that it is because the memory limit because i found this error in error_log file.
PHP Fatal error:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 11245945 bytesin /home/***USER***/public_html/modules/easiestml/easiestml.php on line 171

but by disabling the easiestml and remove the whole module in ftp i still have 500 errors. so it is not related to easiestml.

2- the memory_limit is 128M and i am in a shared host and it is the maximum allowed memroy. xoops.org have a dedicate so have not any issue.

3- i repaired the tables before but that not solved the issue. i repaired it today too.

4- newbb module use getCount($criteria) XOOPS CORE function for count. ( located in XOOPS/class/model/stats.php)
but i change the COUNT(*) and follow your advise and use `post_id` but still i have the same error. i have the same error with `pid`.

5- here is the mysql explain on _bb_posts table.
Field    Type    Null    Key    Default    Extra
post_id    int
(10unsigned    NO    PRI    NULL    auto_increment
pid    int
(10unsigned    NO    MUL    0    
topic_id    int
(8unsigned    NO    MUL    0    
forum_id    smallint
(4unsigned    NO    MUL    0    
post_time    int
(10unsigned    NO    MUL    0    
uid    int
(5unsigned    NO    MUL    0    
poster_name    varchar
(255)    YES        NULL    
poster_ip    varchar
(15)    NO        0.0.0.0    
subject    varchar
(255)    NO    MUL        
icon    varchar
(25)    NO            
attachsig    tinyint
(1unsigned    NO        0    
approved    smallint
(2)    NO    MUL    1    
post_karma    int
(10unsigned    NO        0    
attachment    text    YES        NULL    
require_reply    tinyint
(1unsigned    NO        0


As i said above i have the same error with `pid`

6- i dont have 500 error when the count is below 50,000 maybe even i dont have error when it is 100,000 ( i should test but it is hard)

kerkyra:
thank you for your post but as you know we should get the count from mysql to have the number of total indexes.

voltan

Theme Designer
Posted on: 2012/10/13 5:54
voltan
voltan (Show more)
Theme Designer
Posts: 724
Since: 2006/12/5
#7

Re: how to avoid strange errors when we reach to memory_limit?

Hello

SELECT COUNT is a bad query and if you check xoops debug sql you see it need to many times for get answer from mysql.

count used for make pagenav and other problem is when you use pagenav whit count and offset you be have too big query
1- for select count
2- for get list of items in pages. when you goo to page 100 and you have 10 item in each page mysql must load 1000 item and show last 10 item it's need to many ram

for example in your website ( jadoogaran ) I check first page and last page query time in news module in about 4000 story

last page : 0.021457
firet page : 0.009671

in newbb it have some query whit more than 1 sec time check it.

For solve it in big tables pagenav system must chane

irmtfan

Module Developer
Posted on: 2012/10/13 6:29
irmtfan
irmtfan (Show more)
Module Developer
Posts: 3419
Since: 2003/12/7
#8

Re: how to avoid strange errors when we reach to memory_limit?

Voltan thank you for reply.
Quote:

1- for select count
2- for get list of items in pages. when you goo to page 100 and you have 10 item in each page mysql must load 1000 item and show last 10 item it's need to many ram

I cannot understand the second issue. to get the item list we always use a limit

so for example in the first page it is:
LIMIT 0, 10

and for example for page 225 if we have 10 items per page we have:
LIMIT 2240, 10

so what is the difference?
but for count we could not use limit.

Also while the count is really fast when the number of items are huge it will affect the website.

IMO just count is the problem.

Quote:

For solve it in big tables pagenav system must chane

so the question is: how can we have a pagenav if we dont count all items?
we cannot use the latest index in the table because maybe many post_id had been deleted. maybe we can use "static php variable" for count items?

zyspec

Module Developer
Posted on: 2012/10/13 13:35
zyspec
zyspec (Show more)
Module Developer
Posts: 1048
Since: 2004/9/21
#9

Re: how to avoid strange errors when we reach to memory_limit?

irmtfan,

Are you saying that if you use something like the code below (with just a COUNT)

SELECT COUNT(*) FROM <tableprefix>_bb_posts;
exit();

that you will get the 500 error or only when you try and use XoopsPageNav() class?

Using COUNT() should not be causing out of memory errors, it seems that there must be something else...

irmtfan

Module Developer
Posted on: 2012/10/14 5:11
irmtfan
irmtfan (Show more)
Module Developer
Posts: 3419
Since: 2003/12/7
#10

Re: how to avoid strange errors when we reach to memory_limit?

Yes,

i have the 500 error only in COUNT(*) when the number of results are large.
eg:
i have error 500 with this query:
SELECT COUNT(*) FROM `bb_posts` WHERE (forum_id IN (1,2) AND approved = '1' )

but dont have when i set a limit. eg: post_time > 2011/01/01
SELECT COUNT(*) FROM `bb_posts` WHERE (forum_id IN (1,2) AND approved = '1' AND post_time > '1293858000')

See Youself:
http://www.jadoogaran.org/modules/new ... ost.php?easiestml_lang=en

My website have posts from 2002 until now.

It seems we should set some limits like the post_time for count.

Edit:
one thing that prove the memory limit:
when i change the limit to post_time > 2010/01/01 i dont have problem as a guest but have 500 error as a webmaster.

when i change the limit to post_time > 2007/01/01 i have error 500 as a guest too.