Texiwill

More information, the inner query

SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id;

produces 1132453 rows and works just fine.

The following works just fine as well:

SELECT * FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1;

However, once you start the inner join, it fails and is incredibly slow, as follows:

SELECT t1.* FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1 INNER JOIN slim_stats t1 ON ts1.maxid = t1.id ORDER BY t1.dt desc LIMIT 0, 50;

Removing just screenres also just fails… as it is the first INNER JOIN that is causing the issue….

BTW, I also optimized the tables with no affect.

Yes the following command works just fine and produces 50 row that look proper…. This method is MUCH faster and uses implied INNER JOINS …instead of explicit ones…

SELECT t1.*,tb.*,tci.*,tss.* FROM slim_stats t1,slim_browsers tb,slim_screenres tss,slim_content_info tci WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt > 1356998400 AND t1.dt < 1359676799 and t1.browser_id = tb.browser_id and t1.screenres_id=tss.screenres_id and t1.content_info_id = tci.content_info_id order by t1.dt desc limit 50;

I tested this against the following command which just lists the latest entries:

select id,resource,from_unixtime(dt) from slim_stats order by id desc limit 50;

And the info was the same…

Best regards,
Edward

Update:

After freeing YET another 4G of space from the system, the JOIN is still too big for the temporary filesystem. Which is 14G.

My comment is that this query is too complex and needs to change. I noticed that when this fails, the site halts until the db timeout is satisfied on generating this table. I am testing the query directly from the mysql client as to NOT impact the site.

This did not happen on previous versions of Slimstat, so it appears to be related to a change in the 2.9 version of this query…. and only for the ‘get_recent’ query….

Tables sizes: slim_stats 380M, outbound 16K, countries 3.52M, browsers 80K, screenres 160K, content info 64K

Odd it is still collecting data but showing nothing under ‘recent’ unless you show by some of the fields (i.e. by username). Nor does it update the visitors and other tabs until you show by some other field. Which means perhaps the group by is not correct?

Best regards,
Edward

Hello,

More research, the JOIN is causing a temporary table that is most likely greater than available file space…. which in my case would be > 10G off a 380M database…..

So I am removing some larger tables in hopes that frees up enough space. But that seems to be the issue….

Check out: https://www.coderchris.com/mysql/mysql-51-multiple-tmpdirs-and-incorrect-key-file-for-table-errors/2009/01/22

I think the query in question needs to be done differently.

Best regards,
Edward

Hello,

I am using INNODB. Next suggestion?

Best regards,
Edward Haletky

#sql_bf8_1 is a temporary table or some such that cannot be repaired via normal means. Actually, mysqlcheck will not pick it up nor will mysql command ‘check table #sql_bf8_1’ nor does it show up in phpMyAdmin… So it must be a temporary table formed by the join.

Keeping that in mind, I did however run a mysqlcheck against all wordpress tables and they show as OK. So no corruptions reported.

Given that, there sees to be something else going on here, and this is also apparently related to the whole site stopping until Admin screens refresh properly. This only occurs when viewing admin screens. Recording is happening, BUT updates are not happening on the admin reports and when I do go to an admin screen I get exactly one line that reports what I gave you and the site hangs until the admin screen refreshes. I suspect a db timeout is the root cause related to this failed query.

If I run the query SELECT t1.*, tb.*,tci.*,tss.* FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1 INNER JOIN slim_stats t1 ON ts1.maxid = t1.id INNER JOIN slim_browsers tb ON t1.browser_id = tb.browser_id INNER JOIN tslim_screenres tss ON t1.screenres_id = tss.screenres_id INNER JOIN slim_content_info tci ON t1.content_info_id = tci.content_info_id ORDER BY t1.dt desc LIMIT 0, 50;

I receive the output:
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_bf8_1.MYI'; try to repair it

Which clearly shows this is a temporary table formed by the JOIN that is of issue. There is plenty of disk space and the tmp space exists in mysql… So this I think is something entirely different.

Best regards,
Edward Haletky

Ah, I was looking at so many related posts plugins I did not remember correctly it was ‘Better Related Posts’

Best regards,
Edward

I answered that question already, it does not work well, you have to know when things load to make it work. Look at ‘WordPress Related Posts’ plugin, it does what I asked about….

Yes, wordpress related posts does this. But it does not do contextual related posts which is what I really need…. For now I by hand modified the core to use a priority of 20…. I tried a remove_filter, add_filter combo but that does not work very well….

Best regards,
Edward

One way would be to use the PHP Code Widget and add into that something like:

<?php if (function_exists(“echo_ald_crp”)) { echo_ald_crp();} ?>

That would do what you want.

— Edward

Yes I would like to test the new vesion.

Thanks for quick responses,
Edward

Another solution is to rearrange my multi-wordpress configuration so that the DB variables that I want to use for this site show up last.

I suggest you include wp-config.php so that the proper vars are in place. There is a slight overhead but nothing huge.

Best regards,
Edward

I thought I did as well, I Have a development site and a production site. There are no differences in caching mechanisms between the sites. Both use Xcache, DB Cache Reloaded, etc. The Dev Site works flawlessly. But the production site does not.

Turning on a JavaScript debugger, I found the cause of the problem. :} The wp-config.php file is being parsed but not executed, that will cause the problem in my setup as I go to different databases depending on the incoming URL…. So in essence it has different DB_ var names. On the dev server there is only 1 item…

This was an older way to do multi-site setup and we have not changed it yet…. (wp_oneinstall plugin and it still works today). The MU will not work for us in its current form.

So we need a way to set the DB_ vars appropriately… the simplest way seems to just include wp-config.php instead of trying to parse it which could make this MU acceptable.

This is what I did on my dev environment and it seems to work.

Best regards,
Edward

Hello,

One other thing, I also use a memcache plugin but whether I have that enabled or not does not seem to make a difference. I actually have two sites, one it works on, the other it does not and they should be the same.

PHP cache may be a different but unfortunately I cannot remove that to test it out.

site is ‘www.virtualizationpractice.com’

Edward

Hello,

We do not use any page, db, or other caching techniques, so I do not think this is the cause of the issue. I think its all about redirections and the site does none other than what wordpress or WPTouch does.

Best regards,
Edward

Hello,

Finally was able to get online… check out https://www.astroarch.com

Best regards,
Texiwill