I have a vimp installation at ahangerooz.org. The site loads very slow. It's built on php-symfony. Trying to see if someone can take a look at it.
Our host says it's a mysql issue and it's taking up a lot of our resources, but not really sure that's the case at the moment.
I guess I'm looking for a Symfony and MySql expert.
Gordon Franke answers:
Look at the debug bur how many queries and how long it takes. Call the slow sql query with EXPLAIN in the mysql console.
I was hoping someone can take a look at it. Not looking for you to fix it until
we know what the issue is.
I would do what you're saying but I'm not familiar with MySQL.
Maybe a little more detailed explanation.
Thanks a lot for your help.
I understand the problem is with the code and can not be changed
because it is encrypted code. I will try to back up the data and see
if I can install it on a faster server only for the time being, until I find
a better software solution.
so you can enabled de _dev.php enviroment, i can look, and try to fix it [OLD]
I have show the dev environment and take only 3000 milliseconds per page, thats is't s slow
and the mysql make 180 query average
where uri are slowly load
Lawrence Krubner answers:
Can you post a little more info?
Do you know what version of Symfony is running?
Do you know what kind of server it is running on?
Are you using Propel or Doctrine or something else?
Are you able to ssh to the sever? If yes, can you run the "top" command? Can you tell us what the average load is?
I have often struggled with the issue of slow Symfony sites. I will here list some tips. Some of these are general PHP tips, and others are specific to Symfony.
If the "top" command tells you that load is above 1, then that means the CPU is slowing down the site. If the load is below 1, then look at the memory. How much free memory do you have?
I just ran "top" on my own server. Look at the screenshot that I am uploading. On the top right, you can see that top is above 1 on my server. That means that the CPU is starting to run into trouble on my server, though the site is still able to function. (Things don't get really bad until top shows you a load of 5 or 6 or even 10 or 20.) The "top" command shows you how much work is backing up. A load of 1 means that the CPU is at 100% of it capacity. A load of 2 means the CPU is facing twice the work that it is ready to handle.
If load is low but your server is slow, then it is possible the problem is elsewhere. Perhaps you are out of memory, in which case your code is writing some data to disk, which is much slower than holding the data in memory.
The other big problem that I have seen sites face is complex database queries. Some of these are very slow. I have worked on several sites that could only be saved by re-writing the database queries so that they were simpler and fetched less data. A badly written query can bring a server to its knees.
Is the database on the same server as your PHP code? If the database is on another server, can you ssh to that server and run top? If you have a server devoted just to the database, and the "top" command shows you a high load on that server, then you know that database queries are the main problem that you are facing.
Can you say what kind of site this is?
I realize you want someone to check out the site itself. I do not have time to do so but hopefully someone else can help you.
If you give anyone access to any resource, be sure to change all passwords as soon as they are done looking at your site.
And remember to never give anyone root access to your server unless you trust them completely!
Thanks for the tips.
Martin Palacio answers:
as Gordon said, according to your dev environment, you are performing 180 queries, that's a lot. This is a sign that you need to perform some join between tables. For example:
SELECT media.MID, media.UID, media.MEDIAKEY, media.MEDIATYPE, media.FIRST, media.HIDDEN, media.STATUS, media.FEATURED, media.CULTURE, media.PROPERTIES, media.CHILDWELFARE, media.TITLE, media.TITLE_CLEAN, media.SID, media.DURATION, media.THUMBNAIL_FILENAME, media.SOURCE_FILENAME, media.SCRAMBLED_FILENAME, media.SERVER, media.FILE_EXTENSION, media.META_TITLE, media.META_DESCRIPTION, media.META_KEYWORDS, media.META_AUTHOR, media.META_COPYRIGHT, media.CONVERSION_NOTICE, media.SUM_RATING, media.COUNT_VIEWS, media.COUNT_RATING, media.COUNT_FAVORITES, media.COUNT_COMMENTS, media.COUNT_FLAGS, media.ACCEPT_COMMENT, media.ACCEPT_RATING, media.CREATED_AT, media.UPDATED_AT, media.CONVERTED_AT FROM media WHERE media.HIDDEN=0 AND media.MEDIATYPE IN (0,1,2,3,4) AND media.STATUS=5 ORDER BY RAND() DESC LIMIT 1
[0.34 ms] SELECT user.UID, user.HIDDEN, user.STATUS, user.CULTURE, user.USERNAME, user.PASSWORD, user.NEW_PASSWORD, user.EMAIL, user.VALIDATION_CODE, user.DELETION_CODE, user.AVATAR_FILENAME, user.GENDER, user.FIRSTNAME, user.LASTNAME, user.CITY, user.COUNTRY, user.BIRTHDAY, user.ABOUT_SID, user.LAST_LOGIN_AT, user.LAST_ACCESS_AT, user.COUNT_VIEWS, user.COUNT_COMMENTS, user.COUNT_FLAGS, user.COUNT_MEDIA_VIEWS, user.COUNT_VIDEO_VIEWS, user.COUNT_PHOTO_VIEWS, user.COUNT_AUDIO_VIEWS, user.COUNT_DOCUMENT_VIEWS, user.COUNT_YOUTUBE_VIEWS, user.COUNT_OWN_MEDIA_VIEWS, user.COUNT_FRIENDS, user.COUNT_FAVORITES, user.COUNT_BLOG_COMMENTS, user.COUNT_OWN_BLOG_COMMENTS, user.COUNT_FORUM_COMMENTS, user.COUNT_OWN_FORUM_COMMENTS, user.COUNT_GROUPS, user.COUNT_OWN_GROUPS, user.COUNT_OWN_MEDIA, user.COUNT_OWN_MEDIA_VIDEOS, user.COUNT_OWN_MEDIA_PHOTOS, user.COUNT_OWN_MEDIA_AUDIOS, user.COUNT_OWN_MEDIA_DOCUMENTS, user.COUNT_OWN_MEDIA_YOUTUBE, user.COUNT_RATING, user.SUM_RATING, user.CREATED_AT, user.UPDATED_AT FROM user WHERE user.UID=8
Maybe you need to perform some join between tables User and Media?
Moreover, according to the "timers" section on the web debug toolbar, Component "media/tagCloud" is taking about 40% of the time to render, check there too.
Roman Piekarski answers:
Yes, it is definitely database problem but not at all.
Generally you have 180 separated queries to database only for your homepage.
The problem looks like as example:
- request to database
- response with list of items
- loop iteration
- for every iteration your script do another request to another table
There is many of this kind of requests. The problem is, you should do use join for tables, not a separated select to one table and another request for extended informations.
The symfony version is 1.1.6 (this is information for other developers if it can be helpfull)
Roman Piekarski comments:
Another, see here: http://ahangerooz.org/frontend_dev.php/youtube/Amir-Bahador---Haghe-man-in-nabod/5a272f8329aead87e0805fae027c9f97
72 mysql queries but this is not important fot the case. See what kind and how many the wrong queries is. select count(column) ...
This is one of the most timeconsuming queries. The queries are to get statistics but for big tables its a bad idea. The solution is, for every new data increment dedicated columns in order to build statistics. As example to have a count of comments for article, you have dedicated column for the article. The column is incrementing for every incoming comment.