Hello
Posted by jero01
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 12:55PM |
Admin Registered: 23 years ago Posts: 4,495 |
Newer versions of ps don't have this issue. MySQL is a threaded application. That 5.7% is for the whole MySQL daemon. You can thank the early procps maintainers for that really crappy output. If you add up the percentages in use for threaded applications, you often find that they are using over 100% of your RAM. no possible??!?!? threads share ram. But, older Linux ps only knows how to show processes and therefore shows the memory associated with that "process" which is the overall daemon memory.
Is there some performance issue that has led you to looking at this?
Is there some performance issue that has led you to looking at this?
Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 01:36PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 02:03PM |
Admin Registered: 21 years ago Posts: 9,240 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:15PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:17PM |
Admin Registered: 21 years ago Posts: 9,240 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:22PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:30PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:41PM |
Admin Registered: 23 years ago Posts: 4,495 |
You can't use more than 100% of memory. If you host really thinks that, they know nothing about the server they are running. If they can give you some queries that are running slow or some pages that are lagging, that would be something interesting. These numbers are nonsensical.
Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 04:46PM |
Registered: 20 years ago Posts: 683 |
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 05:15PM |
Admin Registered: 23 years ago Posts: 4,495 |
Do I need to clarify Pat? =)
ps will report that the threads are using more than 100% of your RAM. But ps is a lying sack of crap.
ps will report that the threads are using more than 100% of your RAM. But ps is a lying sack of crap.
Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: Each mysql process from Phorum taking too much memory and cpu May 23, 2007 05:50PM |
Admin Registered: 19 years ago Posts: 8,532 |
The mysql processlist is a lot more interesting than ps output. Can you run "show processlist" yourself on the server at a time when the server is at a high load? Then you might be able to see what queries are slowing the server down.
At the same time, this could be anything from other scripts taking up the memory/CPU to faulty disks causing the mysql server to work hard on correcting problems in the I/O field. Your host should really look into things a bit more, especially if your site is less busy than before at this moment.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
At the same time, this could be anything from other scripts taking up the memory/CPU to faulty disks causing the mysql server to work hard on correcting problems in the I/O field. Your host should really look into things a bit more, especially if your site is less busy than before at this moment.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu May 27, 2007 07:59PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 27, 2007 08:02PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 27, 2007 08:26PM |
Admin Registered: 19 years ago Posts: 8,532 |
This isn't really useful output. I don't see any queries in here and I don't know why you see all these startup options in your output. Useful output would include running queries at the moment that you are running into troubles. This output you've got now is about as good as some plain "ps" output.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu May 27, 2007 11:15PM |
Admin Registered: 23 years ago Posts: 4,495 |
Please see all my above posts about ps output on linux. We need queries. Not ps output. Your host is a moron if they really think mysqld is using that much memory. Get some query data and then come back.
Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: Each mysql process from Phorum taking too much memory and cpu May 28, 2007 12:20PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 28, 2007 01:39PM |
Admin Registered: 21 years ago Posts: 9,240 |
both of these queries aren't long running or slow as it seems.
I would bet that these aren't bringing down your host.
We can try to get some more info about their complexity:
Could you run the following query and show us the output?
Thomas Seifert
I would bet that these aren't bringing down your host.
We can try to get some more info about their complexity:
Could you run the following query and show us the output?
explain SELECT message_id,forum_id FROM phorum_user_newflags WHERE user_id=3832 AND forum_id IN(50,0)
Thomas Seifert
Re: Each mysql process from Phorum taking too much memory and cpu May 29, 2007 06:28PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 29, 2007 10:49PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 08:39AM |
Admin Registered: 21 years ago Posts: 9,240 |
Quote
The verdict is clear from all three levels of my tech support: it is definitely the mysql processes associated with the script that cause an overload on the ram.
then the mysql-configuration should be checked by your tech-support.
it should never be possible for mysql to take up too much ram if configured correctly.
as I guess that the server is in the hands of your tech-support there isn't much we could do about it.
> - I have many threads with over 400 messages and plenty of images: is that a factor?
I have threads with more than 3000 messages, no problem there
> - Too big database? it is over 1 gig
my database is more than 15 gig, no problem there.
The query you posted above doesn't show any problems. its really a fast one and couldn't bring any server down.
Thomas Seifert
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 08:45AM |
Admin Registered: 19 years ago Posts: 8,532 |
Are you running in threaded view or in flat view for reading messages? I've had some issues with memory use on large threaded view pages. In that case it was not MySQL using a lot of memory, but PHP. Of course, if PHP would use all system memory, then it could bring the MySQL server to its knees too.
If the MySQL server is crashing, then this should log some info to the MySQL error logs. Can you see if any interesting info is logged in there?
The query for which you ran the explain query doesn't look that complex.
If we would want to look closer at it (time is not on our hand though, all the devs are terribly busy at the moment), would there be UNIX shell access for examining the system?
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
If the MySQL server is crashing, then this should log some info to the MySQL error logs. Can you see if any interesting info is logged in there?
The query for which you ran the explain query doesn't look that complex.
If we would want to look closer at it (time is not on our hand though, all the devs are terribly busy at the moment), would there be UNIX shell access for examining the system?
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 04:46PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 07:18PM |
Admin Registered: 19 years ago Posts: 8,532 |
If you have flat view, then all messages appear like they do on phorum.org (multiple pages, multiple messages at once). In threaded view, you can see the message tree and read messages one by one.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 08:34PM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu May 30, 2007 09:34PM |
Admin Registered: 19 years ago Posts: 8,532 |
If you want me to logon onto the system, then please PM me with some login details. I can't promise to solve this, but I have no problem in inspecting the setup that is currently running on that machine.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu May 31, 2007 03:34AM |
Admin Registered: 19 years ago Posts: 8,532 |
There are 47 databases on your system. There are 174 tables in the database in which your Phorum resides. How can you tell it's specifically Phorum that is causing problems for you? It could be any other piece of software that uses that datbase as well.
I did see that phorum got mentioned in the error log at the moment of some crash, but that doesn't mean that Phorum is the culprit. It just means that the forum was in use by Phorum at the moment that the mysql server died. A query that got logged at a crash time (system out of memory) was:
The logs tell that the limits for the MySQL server are beyond the physical memory availability of the system. If this is really a problem, depends on the amount of concurrent connections to the database. Here's the actual message from the logs at a crash:
It tells us that there were 83 connected threads. Looking at the equation and the my.cnf configuration on your system, this might give a use of: 16MB + (2M+2M)*83 = 338MB of memory by the MySQL server at the time of the crash.
Another thing that I discovered was that your apache processes take 8.5MB+ memory per forked child. Let's asume that for each db connection, there was a corresponding apache child, then we woud have 8.5MB*83 = 705.5MB of memory for apache. Even more if there are also apache childs that handle static content.
At the time of this crash, the memory use by apache and the database server together might have been 1GB+. The physical memory that is available is 1GB though. There is swap space configured, but for running with good performance, swap space usage should be avoided at all times.
Some conclusion from what I have seen:
* The maximum configured number of apache + mysql processes uses more memory than available on the system. Try to trim those down or expand your memory. Trimming down could be done by not loading unneeded apache modules and tweaking MySQL's various buffer and cache sizes.
* I've seen at least 83 concurrent db connections from the logs and I have seen up to 45 apache processes concurrent on the live system. It might be that there are simply more visits on the server than the memory availability allows for;
* I have seen no proof that it is Phorum which is causing problems here. The fact that Phorum is using the database as the time of the crash tells that the Phorum software is used by visitors. The query that was logged at the time of the crash is really really easy and fast for MySQL, so it might at most have been the query to push the server over the edge.
* You have thread_concurrency=2 in your my.cnf. Your system shows 4 processors. You could upgrade the thread_concurrency to 8 in that case (general hint, not related to fixing your problems).
* As far as MySQL server crashes are concerned: I do not see any crashes in the last couple of days, but your posts sound like you still have problems. Maybe you can describe what you see at the time of the crashes. Are there error messages returned?
* When typing on the system, it totally locked up at some point for a minute or so. After that I could continue typing. The load had raised to about 4, but I could not see what pushed the load up (since I couldn't type during the lockup). I have no idea what this was, but it felt suspicious.
That's what I got so far. I hope it helps.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
I did see that phorum got mentioned in the error log at the moment of some crash, but that doesn't mean that Phorum is the culprit. It just means that the forum was in use by Phorum at the moment that the mysql server died. A query that got logged at a crash time (system out of memory) was:
select phorum_messages.author, phorum_messages.datestamp, phorum_messages.email, phorum_messages.message_id, phorum_messages.meta, phorum_messages.moderator_post, phorum_messages.modifystamp, phorum_messages.parent_id, phorum_messages.sort, phorum_messages.status, phorum_messages.subject, phorum_messages.thread, phorum_messages.thread_count, phorum_messages.user_id, phorum_messages.viewcount from phorum_messages where sort=0 or (parent_id=0 and sort=1 and forum_id=54) order by sort, modifystamp descI ran this query by hand on the MySQL server and it gave me:
1 row in set (0.00 sec)So the query completed in virtually no time at all (which should be the case with these kind of Phorum queries). Therefore, I really doubt if it is Phorum which is causing problems on your server.
The logs tell that the limits for the MySQL server are beyond the physical memory availability of the system. If this is really a problem, depends on the amount of concurrent connections to the database. Here's the actual message from the logs at a crash:
threads_connected=83 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1096188 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.
It tells us that there were 83 connected threads. Looking at the equation and the my.cnf configuration on your system, this might give a use of: 16MB + (2M+2M)*83 = 338MB of memory by the MySQL server at the time of the crash.
Another thing that I discovered was that your apache processes take 8.5MB+ memory per forked child. Let's asume that for each db connection, there was a corresponding apache child, then we woud have 8.5MB*83 = 705.5MB of memory for apache. Even more if there are also apache childs that handle static content.
At the time of this crash, the memory use by apache and the database server together might have been 1GB+. The physical memory that is available is 1GB though. There is swap space configured, but for running with good performance, swap space usage should be avoided at all times.
Some conclusion from what I have seen:
* The maximum configured number of apache + mysql processes uses more memory than available on the system. Try to trim those down or expand your memory. Trimming down could be done by not loading unneeded apache modules and tweaking MySQL's various buffer and cache sizes.
* I've seen at least 83 concurrent db connections from the logs and I have seen up to 45 apache processes concurrent on the live system. It might be that there are simply more visits on the server than the memory availability allows for;
* I have seen no proof that it is Phorum which is causing problems here. The fact that Phorum is using the database as the time of the crash tells that the Phorum software is used by visitors. The query that was logged at the time of the crash is really really easy and fast for MySQL, so it might at most have been the query to push the server over the edge.
* You have thread_concurrency=2 in your my.cnf. Your system shows 4 processors. You could upgrade the thread_concurrency to 8 in that case (general hint, not related to fixing your problems).
* As far as MySQL server crashes are concerned: I do not see any crashes in the last couple of days, but your posts sound like you still have problems. Maybe you can describe what you see at the time of the crashes. Are there error messages returned?
* When typing on the system, it totally locked up at some point for a minute or so. After that I could continue typing. The load had raised to about 4, but I could not see what pushed the load up (since I couldn't type during the lockup). I have no idea what this was, but it felt suspicious.
That's what I got so far. I hope it helps.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Each mysql process from Phorum taking too much memory and cpu June 01, 2007 04:19AM |
Registered: 16 years ago Posts: 37 |
Re: Each mysql process from Phorum taking too much memory and cpu June 01, 2007 05:47AM |
Admin Registered: 19 years ago Posts: 8,532 |
The core files only show that there was a segmentation fault in /usr/bin/php. Maybe that is because the systems runs out of memory (I don't know if segfaulting in that case would be the expected behavior; IMO, segfaults are bugs).
What would be good is if you could come up with a single page which would reproduce the problem for you on (about) every hit. I can login on your system, but I can only do some port mortal diagnostics. If I can crash the system on demand, then it's way easier to debug the problem.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
What would be good is if you could come up with a single page which would reproduce the problem for you on (about) every hit. I can login on your system, but I can only do some port mortal diagnostics. If I can crash the system on demand, then it's way easier to debug the problem.
Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Sorry, only registered users may post in this forum.