Thread

I set up a log for slow queries.

Here is one that was just logged, I checked my system memory usage on the server and it spiked, probably due to this query, not sure what page was being accessed when it ran this query, but I am guessing it was within the CP since it references member tables.

SELECT  t.entry_id, t.weblog_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.dst_enabled, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.allow_trackbacks, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.trackback_total, t.sent_trackbacks, t.recent_trackback_date, t.site_id as entry_site_id,
                                                w.blog_title, w.blog_name, w.blog_url, w.comment_url, w.tb_return_url, w.comment_moderate, w.weblog_html_formatting, w.weblog_allow_img_urls, w.weblog_auto_link_urls, w.enable_trackbacks, w.trackback_use_url_title, w.trackback_field, w.trackback_use_captcha, w.trackback_system_enabled,
                                                m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
                                                md.*,
                                                wd.*
                                FROM exp_weblog_titles          AS t
                                LEFT JOIN exp_weblogs           AS w  ON t.weblog_id = w.weblog_id
                                LEFT JOIN exp_weblog_data       AS wd ON t.entry_id = wd.entry_id
                                LEFT JOIN exp_members           AS m  ON m.member_id = t.author_id
                                LEFT JOIN exp_member_data       AS md ON md.member_id = m.member_id WHERE t.entry_id IN (942) ORDER BY t.sticky desc, t.entry_date desc, t.entry_id desc;

# Time: 110816 11:04:30
# User@Host: db_username[db_username] @ localhost []
# Query_time: 16  Lock_time: 0  Rows_sent: 0  Rows_examined: 1868702269
# administrator command: Init DB;

Seems like 1868702269 rows it way too many.

v1.7.1 build 20110509

Ran the query manually, returned one record within 6.9 ms, so it seems like that is not the culprit.

This query was just logged:

SELECT DISTINCT ee.* FROM exp_extensions ee WHERE enabled = 'y' ORDER BY hook, priority ASC, class;

I am the only one with a super admin account and I did not load the extensions or any utilities within the CP. Could this query run on the front end of the site, when a visitor views a webpage?

Also, I’ve seen some more exp_security_hashes table queries coming in, such as:

INSERT INTO exp_security_hashes (date, ip_address, hash) VALUES(UNIX_TIMESTAMP(), '184.8.187.4', 'ef8de60db6e1faed1ff6cacb50e4e50c894074b5');

This just came in as well,

SHOW FULL COLUMNS FROM `exp_message_copies` FROM `expression_engine`;

Another came in

SHOW TABLES FROM `expression_engine` LIKE 'exp\_%';

Not familiar w/ these queries, as to whether they should be running within the CP or front-end when the user views a regular old webpage.

The last query I posted looks like someone is trying to find out more about the database structure. No super admin was using the CP when this query ran.

Hi Brad,

The first query could be related to a Member List. Are you running Discussion Forum or do you have a large number of Members on your site?

The second query could possibly be ran anytime a template is accessed via the front side of the site. It’s calling for active extensions and ordering by the API hooks that run those extensions.

The third query appears to be logging the visitor’s IP address, which is something that ExpressionEngine does as part of Security.

The last 2, I can’t really say, but overall, I don’t see anything out of the ordinary going on here.

Cheers,

Thanks Dan.

We beefed up the server’s resources from:

1 GB of memory to 3 GB

1 CPU to 4 CPUs

I am monitoring the system charts and keeping an eye on the slowlog and will post a response later today or tomorrow.

Any tips on optimizing a the simple search form? {exp:search:simple_form}

I have four CPU’s on the server the website is being hosted on and when the site lags, you see spikes in the CPU. Has there been any cases in the past with customers reporting the same issue? I wonder what they may of done to resolve their issue.

EllisLab, please respond.

Howdy Brad =)

From reading this, it looks like you’re looking for general optimization tips.  Is that the case, or are you running into a particular problem you are trying to solve?

Thank you.

Turned on template debugging and optimized any templates that may be taking too long to render, but after beefing up the server settings, the site occasionally hangs, and the query is logged within the slowlog.

I do not think it is a particular query or queries, but it might be the time it takes to write and read from the storage on the server.

We’ve had increase in traffic over the last 3 weeks and I think it is a deeper server issue and not so much an EE issue. Have you ever come across anyone having a problem with their read/write from storage on their server as the culprit of site slowness?

Brad,

Disk access is going to be the slowest part of the whole process. If you’ve seen a spike in traffic that corresponds to the slowness, I wouldn’t say that’s out of the ordinary.

Aside from these questions, is there a technical issue we can help you with? We’re not really qualified or equipped to advise on server tuning methods.

Cheers!