After 1.3.8 Upgrade - MySQL issues

[Jeff_Mash]

Hey guys and gals,

For some strange reason, unless it's a big coincidence, immediately after I upgraded from 1.3.7 to 1.3.8a, our site is very, very slow. I use putty.exe to login and there are usually a couple of mysqld processes taking 100% of the CPU.

Is there anything I can check to see why this is now happening with the new upgrade? Like, is there a major difference in database queries or calls that you know about which would cause an older system to work like this?

According to our Server/Info, these are what we're running:

Server OS: Linux 2.4.21-27.ELsmp
PHP Version: 4.4.1 (Zend: 1.3.0)
PHP File Uploads: On Max Size: 2M
Database Data Size: 51,404 kB
Database: MySQL 4.0.27-standard
HTTP Server: Apache/1.3.34 (Unix) mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 PHP/4.4.1 FrontPage/5.0.2.2635 mod_ssl/2.8.25 OpenSSL/0.9.7a
PHP Safe Mode: Off
POST Max Size: 55M
Database Index Size: 7,068 kB

Zen Cart 1.3.8a
Database Patch Level: 1.3.8
v1.3.8 [2008-03-01 08:14:20] (Version Update 1.3.7->1.3.8)


And here is my PHP Configure command for 4.4.1:

'./configure' '--with-apxs=/usr/local/apache/bin/apxs' '--prefix=/usr/local' '--with-xml' '--enable-bcmath' '--enable-calendar' '--with-curl' '--enable-ftp' '--enable-magic-quotes' '--with-mysql=/usr' '--enable-discard-path' '--with-pear' '--enable-sockets' '--enable-track-vars' '--with-zlib'

Unfortunately, I see no option but to revert back to 1.3.7 unless we can figure this out. Please help (Dr. Byte, Ajeh, anyone!).

[Jeff_Mash]

Another thing which may or may not be related. While the overall site to users may be slower, it seems that the ADMIN area is abnormally slower than anything else. For example, when I browse from the Admin Panel to open orders, server info, etc., things seem fine (even with the MySQLD process sucking up 100%). But when I go to email a coupon, the admin screen just indefinitely hangs and eventually stops with a white screen after about 5 minutes.

There are all these little kooky things going on, and I am curious based on my system information (above) and the new 1.3.8 files, if there is any one thing that needs to happen (like upgrading PHP to PHP5, etc).

[Jeff_Mash]

Here is my #top command:

09:25:07 up 23:51, 1 user, load average: 7.49, 6.56, 4.92
117 processes: 109 sleeping, 7 running, 0 zombie, 1 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 77.2% 0.0% 22.7% 0.0% 0.0% 0.0% 0.0%
Mem: 510360k av, 343268k used, 167092k free, 0k shrd, 21828k buff
283084k actv, 22076k in_d, 68k in_c
Swap: 2048276k av, 179156k used, 1869120k free 130848k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
13945 nobody 18 0 10780 9684 1952 S 45.2 1.8 0:06 0 httpd
10832 mysql 25 0 52592 23M 1920 R 11.9 4.6 5:42 0 mysqld
10614 mysql 25 0 52592 23M 1920 R 10.9 4.6 6:03 0 mysqld
12733 mysql 25 0 52592 23M 1920 R 10.3 4.6 1:24 0 mysqld
11912 mysql 25 0 52592 23M 1920 R 9.9 4.6 2:35 0 mysqld
13963 nobody 15 0 8032 6844 1680 S 2.3 1.3 0:00 0 httpd
13964 nobody 16 0 8032 6844 1680 S 2.1 1.3 0:00 0 httpd
13996 root 24 0 1032 1032 776 S 0.9 0.2 0:00 0 top
13755 cpanel 15 0 7848 7848 3224 S 0.5 1.5 0:01 0 php
13960 mysql 15 0 52592 23M 1920 S 0.5 4.6 0:00 0 mysqld
13768 mysql 18 0 52592 23M 1920 R 0.3 4.6 0:01 0 mysqld
13974 root 15 0 1152 1152 896 R 0.3 0.2 0:00 0 top
1 root 15 0 128 96 68 S 0.0 0.0 0:05 0 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration

[Jeff_Mash]

Here is where I stand in this ongoing saga:

I changed the SQL_CACHE_METHOD to 'none' in my configuration files. The site is still maxed out with lots of mysqld processes, but it's not 100% down or anything, meaning users can still painfully navigate through it and purchase.

The admin area seems to work sporadically. However, even when it seems to be lightening fast, whenever I go to the Admin Coupon and hit the "EMAIL" button, it hangs. I can hit the coupon "EDIT" button and it works fine....but that darn email button is taking forever and timing out. Does that mean anything?

Also, since I've gotten more orders & customers since this new 1.3.8 upgrade, if I need to revert back, what tables do I need to reimport back into my 1.3.7 backup database? I assume it's just the customers table and all the orders_ tables, but if there is something else, could you let me know?

[Jeff_Mash]

When I Trace one of the MySqlD processes that is sucking up a lot of CPU, I see something like this:

Process 17815 attached - interrupt to quit
pread(34, "\1\1\361\374\357\337\277\377\361\17\0 \341\220\17\0\0\t"..., 20, 1759996) = 20
pread(34, "\0\nPete Geist\16PG Productions\0221800"..., 480, 1760016) = 480
pread(34, "\3\1\276\2\374\357\337\277\377\361\17\0 \341\221\17\0\0"..., 20, 1760496) = 20
pread(34, "\0\0\fDinesh Senan\3DSA\n2 Kew Walk\tS"..., 430, 1760516) = 430
pread(34, "\3\1\217\1\374\357\337\277\377\361\17\0 \341\222\17\0\0"..., 20, 1760948) = 20
pread(34, "\0\0\vJohn Harris\0\0211885 Rockledge R"..., 383, 1760968) = 383
pread(34, "\3\1\300\0\374\357\337\277\377\361\17\0 \341\223\17\0\0"..., 20, 1761352) = 20
pread(34, "\0\0\vJohn Geerts\vArcane Soft\21Rue d"..., 432, 1761372) = 432
pread(34, "\3\1\276\2\374\357\337\277\377\361\17\0 \341\224\17\0\000"..., 20, 1761804) = 20
pread(34, "\0\0\16Anibal Aguirre\4MCAS\02611343 S. "..., 430, 1761824) = 430
...etc....

Any idea what that process is doing? It goes on very long enumerating through all the customers it seems.

EDIT: I just killed that process through WHM and it brought the system back to normal. Then when I go to Coupon --> Email, it hangs again with the above process being started. What on earth is causing that? In the past, when I click on EMAIL within the Coupon Admin, it would take me to a form where I could email the coupon to a recipient. Now when I click on it, it hangs the entire system and the above process begins. Why is that?

[Jeff_Mash]

This seems directly related to the Coupon Email function, which I have started a new thread here: http://zen-cart.com/forum/showthread...844#post517844

We can close this particular thread out since it was basically me trying to find out the issue. Now that I have it isolated to the above function, I will look for responses on that particular thread.

[DrByte]

Try running this via the Install SQL Patch tool (or via phpMyAdmin after inserting your table-prefix):

update query_builder set query_category = 'disabled' where query_name = 'Customers who have never completed a purchase';

[Jeff_Mash]

Thanks DrByte. That seemed to do the trick. Apparently, that query was killing our system. ZenCart support is THE BEST!