Link to home
Start Free TrialLog in
Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America

asked on

Issues with SQL Deadlocks and sluggish system

We've been having a whale of a time determining why, all of a sudden, we're getting SQL Deadlocks and extreme slowness in our website. I upped the max_connections to alleviate some "Out of Connections" error we had as well.

I have the following in my.cnf
port		= 3306
socket		= /var/lib/mysql/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 150

max_allowed_packet = 16M

key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Open in new window

We are using ExpressionEngine and Magento, and I know Magento is a database hog and is the ONLY source of the SQL Deadlocks as it is occuring in Magento's PDO.php file when a customer update is being performed.
#0 /store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `magcust...', Array)
#3 /store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `magcust...', Array)
#4 /store/lib/Zend/Db/Adapter/Abstract.php(604): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `magcust...', Array)
#5 /store/app/code/core/Mage/Eav/Model/Entity/Abstract.php(1133): Zend_Db_Adapter_Abstract->update('magcustomer_ent...', Array, 'entity_id='9170...')
#6 /store/app in /srv/www/htdocs/store/lib/Zend/Db/Statement/Pdo.php on line 235

Open in new window

We authenticate our users through our ERP using a web service and have everyone's Magento account have the same password so that we can log them in automatically. We update their Magento record when we authenticate.

I added code to retry when a deadlock was found but that only caused the system to slow down even more. So, what do you think may be causing our issues?  

Here is also the uncommented variables in my PHP .ini file:
zend.ze1_compatibility_mode = Off
short_open_tag = On
asp_tags = Off
precision    =  14
y2k_compliance = On
output_buffering = 4096
zlib.output_compression = Off
implicit_flush = Off
serialize_precision = 100
allow_call_time_pass_reference = Off
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
disable_functions =
disable_classes =
expose_php = On

max_execution_time = 60     ; Maximum execution time of each script, in seconds
max_input_time = 60	        ; Maximum amount of time each script may spend parsing request data
memory_limit = 1024M        ; Maximum amount of memory a script may consume (128MB)
error_reporting  =  E_ALL

display_errors = Off
display_startup_errors = Off
log_errors = On
log_errors_max_len = 1024
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
html_errors = Off
error_log = /var/tmp/php_error.log
variables_order = "GPCS"
register_globals = Off
register_long_arrays = Off
register_argc_argv = Off
auto_globals_jit = On
post_max_size = 20M

magic_quotes_gpc = Off
magic_quotes_runtime = Off
magic_quotes_sybase = Off
auto_append_file =

default_mimetype = "text/html"
default_charset = "UTF-8"

include_path = ".:/usr/share/php5:/usr/share/php5/PEAR"
doc_root =
user_dir =
extension_dir = /usr/lib64/php5/extensions
enable_dl = Off

file_uploads = On
upload_tmp_dir = "/tmp"
upload_max_filesize = 20M

allow_url_fopen = On
allow_url_include = Off
default_socket_timeout = 60

define_syslog_variables  = Off

sql.safe_mode = Off

odbc.allow_persistent = Off
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1

mysql.allow_persistent = Off
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =

mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =

mysql.connect_timeout = 60
mysql.trace_mode = Off

mysqli.max_links = -1
mysqli.default_port = 3306
mysqli.default_socket =

mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =

mysqli.reconnect = Off

session.save_handler = files

session.save_path = "/var/lib/php5"
session.use_cookies = 1
session.use_only_cookies = 1 = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.cookie_httponly = 1
session.serialize_handler = php
session.gc_probability = 1
session.gc_divisor     = 1000
session.gc_maxlifetime = 1440
session.bug_compat_42 = 0
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 16
session.entropy_file = /dev/urandom
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 1
session.hash_bits_per_character = 5
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=fakeentry"


Open in new window

Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eddie Shipman


That solution will not work for us. The deadlock is coming from a single point, the $customer->save()
in a function called mag_login_or_create() in our ExpressionEngine plugin. I have put in some logging and it seems that the call actually IS being called multiple times at once from the same "user". Not exactly sure why, though.
Unfortunately, deadlocks are usually a design problem, not a configuration or implementation problem.  Resolving these deadlocks will almost certainly involve debugging and modifying the code - something that would best be done by the authors.  Have you tried contacting Magento for any assistance?

I'm not familiar enough with Magento to be of any real, specific help.  The most I could do is point you in the right direction to isolate the cause of the lock.  It sounds like you've already found it.

Perhaps some other experts with more knowledge will chime in.
Are you kidding, attempting to get help from them is like pulling teeth from a lion. Their "support" forums are a joke, no one uses them anymore and the Stack Overflow Magento area is not very highly trafficked.