Slooooooooooooooooow MySQL
Just a general question.
I have a database (it's private and you have to be on the school's network to actually have access to it) with thousands of records in it.
Reading the database isn't a problem, it's writing to the database that is slow. Taking up to 15 seconds.
The system is set up so that a form is filled out, submit is clicked and a php script executes insert statements. A pretty standard system.
Nothing fancy has really been done with the database.
Any general tips on how to speed up the writing process?
Thanks!
kb posted this at 19:06 — 16th July 2007.
He has: 1,380 posts
Joined: Feb 2002
Is this a MySQL db? Any standard relational database should be very quick... whether it's MySQL, MSSQL, Oracle, etc.
It's either your server (need more RAM or disk space?), or your script (efficiency?).
sublimer posted this at 19:46 — 16th July 2007.
They have: 41 posts
Joined: Aug 2006
Murphy's Law.
Well, its only when using INSERT or UPDATE when the query takes a while to execute. SELECT works fine.
There is roughly 2000 records in the main table.
It was running fine but started to slow down once there were a lot of records added to the database. The more records added, the slower the database.
If it's not the server, what could be done to try and speed it up?
kb posted this at 19:27 — 17th July 2007.
He has: 1,380 posts
Joined: Feb 2002
I'm assuming that you are using MySQL since you didn't directly answer the question...
Can we see some of the queries? You may be doing things that will slow it down, despite the server's speed.
Also, you can always look into something like memcached, or a home grown solution, that only processes INSERT and UDPATE queries at a predefined interval as a batch, and uses something like a LUT (look up table) for SELECT queries... all rather than do it directly to the database. The whole idea is to make it faster (Facebook and LiveJournal are big memcached users)
pr0gr4mm3r posted this at 23:50 — 17th July 2007.
He has: 1,502 posts
Joined: Sep 2006
Insert delayed statements may improve your performance, but it seems like your problem is worse than an optimizing issue. How many columns do you have indexed?
kb posted this at 04:55 — 18th July 2007.
He has: 1,380 posts
Joined: Feb 2002
I was thinking about this today, and I decided you have a few more things you might want to look at...
1) Why are you so sure it's not the server? Do you have enough RAM? Is some of the RAM faulty? Is the processor functioning correctly? Is the processor strong enough to do this along with everything else?
2) PHP has an inherent caching system. Are you using PHP? If so, is it configured for caching?
3) I believe MySQL has a caching system as well.
4) How are you processing your UPDATE calls? Are you using location methods that aren't efficient (for example, UPDATE...WHERE this_field LIKE = ' %$something%' ... instead of UPDATE... WHERE id = '$id')
5) Do you have faulty / improper connections to the db? I forgot to close a connection on a site once, and the database performance seriously suffered. When I found this, and corrected it, performance increased exponentially... always keep track of your connections, and be sure to close them so you save system resources.
But to really help you, we need more information from you. Specific code, site stats, db stats, maybe some server info... But I've told you this before, and you haven't given us anything.
sublimer posted this at 21:22 — 18th July 2007.
They have: 41 posts
Joined: Aug 2006
Trust me, I would love to show you the snippets of code, but I don't have access to them right now. It's running on my alum high school's intranet. I am out of town right now and accessing it would be a job in itself.
That's why I was asking for general ideas about what slows it down. An IT guy from the school called me and notified me of the problem. I developed the code so I am very familiar with it.
For brady.k
For number 1, I do not think it is the server because it has no problem running other sites on the same server, such as the school's website and other teacher applications.
For 2 and 3, We installed PHP ourselves and did very little configuration because we were running out of time to develop the program. By default, is PHP caching enabled?
For number 4, I am not doing my statements like that.
For number 5, I was unaware that closing connections could change performance. I know that I am not doing that. I am a self-taught PHP/MySQL person, so often times I miss those little tid-bits. I will inform IT about adding the code to close the connection.
For pr0gr4mm3r
There are approximately 2,200 rows. Split up into 10 columns.
I'll try to get you some code to help you guys out. I appreciate the ideas in the meantime though!
kb posted this at 01:11 — 19th July 2007.
He has: 1,380 posts
Joined: Feb 2002
2 and 3, I'm not exactly sure. I can look into it and get back to you.
5, my favorite thing to do is this...
Pretend your database connection is something like
<?php$db =mysql_connect(....balalalhahalahablah);
?>
And then in your footer (or whatever you call your last includes file that runs all the things at the end of the page), you can do something like
<?phpif (($db != NULL) || ($db != '')) {
mysql_close();
}
?>
That will close all of your MySQL connections, but won't attempt to close any if you never had any in the first place.
A quick and easy fix to a very big problem!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.