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

LVL 27
Eddie ShipmanAll-around developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
This appears to be a pretty common problem with Magento.  This page has some tips for modifying the code to ease the burden, but specifically states that it leave some other deadlock sources alone.

The good news is that the sluggishness is very likely caused by the deadlocks...resolve one and you resolve the other.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eddie ShipmanAll-around developerAuthor Commented:
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.
Steve BinkCommented:
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.
Eddie ShipmanAll-around developerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.