Improving mysql performance? - GoFuckYourself.com

blazin · 09-21-2005, 03:55 AM

Anyone got some simple tips on how to improve performance of mysql database.

Thanks

fris · 09-21-2005, 04:40 AM

customize your /etc/my.cnf

default is usally for a crap low end server

increase allowed connections, buffer size, etc

drama · 09-21-2005, 04:49 AM

write better application code
learn how to use mysql indexes

Finally, http://dev.mysql.com/doc/mysql/en/my...imization.html

jimthefiend · 09-21-2005, 04:55 AM

Quote:

Originally Posted by fris

increase allowed connections,

Thats key.

Also check and optimize your tables regularly, and get your own sql server.

Jay_StandAhead · 09-21-2005, 04:58 AM

you could pay a very experienced programmer to look at your setup and optimize it. Probably would be very cheap

check out: http://www.gofuckyourself.com/showthread.php?t=515418

drama · 09-21-2005, 04:58 AM

Quote:

Originally Posted by jimthefiend

Thats key.

Also check and optimize your tables regularly, and get your own sql server.

You;re an idiot. Keep your mouth shut when you dont know what you're talking about

fusionx · 09-21-2005, 04:59 AM

Quote:

Originally Posted by blazin

Anyone got some simple tips on how to improve performance of mysql database.

Thanks

Here's a great article: http://www.databasejournal.com/featu...0897_1402311_2

You find some other example config files - my-huge.cnf, my-large.cnf, my-medium.cnf, and my-small.cnf. Grab one of those and edit it to your liking and copy it over my.cnf. I'd suggest backing up the current config just to be safe. The article above describes how to choose which one to start with.

jimthefiend · 09-21-2005, 05:02 AM

Quote:

Originally Posted by drama

You;re an idiot. Keep your mouth shut when you dont know what you're talking about

Go fuck yourself moron. I just went through all that and improved the performance of my forums and such exponentially.

Stfu noob.

errr I mean TROLL.

drama · 09-21-2005, 05:06 AM

Quote:

Originally Posted by jimthefiend

Go fuck yourself moron. I just went through all that and improved the performance of my forums and such exponentially.

Stfu noob.

errr I mean TROLL.

Since you're an expert why dont you tell us how increasing the max connections to the server will help with the speed of the server? How about explaining what the optimize table does and how that can improve the speed of the server?

You're an idiot. Case in point.

jimthefiend · 09-21-2005, 05:14 AM

Quote:

Originally Posted by drama

Since you're an expert why dont you tell us how increasing the max connections to the server will help with the speed of the server? How about explaining what the optimize table does and how that can improve the speed of the server?

You're an idiot. Case in point.

Who the fuck are YOU again?

Did I say I was an expert?

As I recall he didnt ASK about improving speed, he ASKED about improving performance. I would say that increasing connections to prevent errors like:

"Warning: Too many connections in /path/to/blahblahblah/includes/functions/database.php on line 19 Warning: MySQL Connection Failed: Too many connections in /path/to/blahblahblah//includes/functions/database.php on line 19. Unable to connect to database server!"

Qualifies as improving performance.

drama · 09-21-2005, 05:18 AM

Quote:

Originally Posted by jimthefiend

Who the fuck are YOU again?

Did I say I was an expert?

As I recall he didnt ASK about improving speed, he ASKED about improving performance. I would say that increasing connections to prevent errors like:

"Warning: Too many connections in /path/to/blahblahblah/includes/functions/database.php on line 19 Warning: MySQL Connection Failed: Too many connections in /path/to/blahblahblah//includes/functions/database.php on line 19. Unable to connect to database server!"

Qualifies as improving performance.

Why dont i tell you why they WONT improve the speed of the server and possibly make it worse.

Max Connections, if your apps are getting locked out of mysql due to the server not having enough connections chances are that your tables are not properly indexed and thus mysql takes longer to scan the table looking for the results of your query. But lets say you do increase the max connections, o now you got more apps waiting on mysql to run queries which only worsens the problem. Wheres the speed?

Optimizing tables,This *Might* help out but chances are that it will do nothing special for you unless you do lots of deletes in the table. What this command does is re-creates the table with your data minus data blocks marked as deleted but where the data hasnt been purged. Wheres the speed?

Use proper indexes, up some of the buffers (key buffer,read buffer, sort buffer). This all depends on the amount of ram you want mysql to use AND what your max connection setting.

key_buffer_size=134217728
read_buffer_size=16773120
max_used_connections=37
max_connections=150
threads_connected=24
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3308966 K

Now you see how upping the max connections should be used as a last resort.

like i said, If you dont know what you're talking about then keep your mouth shut

jimthefiend · 09-21-2005, 05:24 AM

Why dont you MAKE me shut up you fucking gimp?

Anyway, I dont think thats going to be much of an issue if the db is running on it's own server.

Let me say this again big mouth: I never CLAIMED to be an expert, I just know whats working well for ME.

And that's what matters isnt it? RESULTS.

ServerGenius · 09-21-2005, 05:26 AM

reduce the amount of queries by combining multiple queries to 1
check howto use indexes. In case you have a lot of queries use persistant
connections so you don't setup connections for each induvidual query thus
flooding your own server with connections.....reduce connection time-out
so you don't have unused connection lingering idle on your server

drama · 09-21-2005, 05:33 AM

Quote:

Originally Posted by jimthefiend

Why dont you MAKE me shut up you fucking gimp?

No need to "make" you shut up. I'll just keep on correcting you and making you look stupid infront of your peers.

Quote:

Originally Posted by jimthefiend

Anyway, I dont think thats going to be much of an issue if the db is running on it's own server.

Yes it will make a huge difference. HUGE! "exponentially" even LOL

Quote:

Originally Posted by jimthefiend

Let me say this again big mouth: I never CLAIMED to be an expert, I just know whats working well for ME.

You claimed to have read all this information and made HUGE changes which affected the server "exponentially" when all you did was fix a poorly configured database server. If you knew WTF you were doing it would have never been an issue to begin with. It has absolutely nothing to do with fine tuning a DB server for speed.

Quote:

Originally Posted by jimthefiend

And that's what matters isnt it? RESULTS.

[/QUOTE]

Yes, but you produced nothing, nadda, zip, zero! Your comment was as worthless as all your other posts.

drama · 09-21-2005, 05:34 AM

Quote:

Originally Posted by DynaSpain

reduce the amount of queries by combining multiple queries to 1
check howto use indexes. In case you have a lot of queries use persistant
connections so you don't setup connections for each induvidual query thus
flooding your own server with connections.....reduce connection time-out
so you don't have unused connection lingering idle on your server

How here is some good advice

blazin · 09-21-2005, 05:43 AM

Thanks for the suggestions all

ServerGenius · 09-21-2005, 05:46 AM

Quote:

Originally Posted by drama

How here is some good advice

I love you too

Gungadin · 09-21-2005, 06:29 AM

If you are doing mostly select's, mysql query cache will make a HUGE improvement in speed. Recently I had a query go from 11 seconds to 0.006 seconds just by turning on caching.

Calvinguy · 09-21-2005, 10:10 AM

If not already update to mysql 4+ and have query cache enabled. Some hosts still mess around with version 3.23

NetRodent · 09-21-2005, 10:54 AM

Quote:

Originally Posted by Gungadin

If you are doing mostly select's, mysql query cache will make a HUGE improvement in speed. Recently I had a query go from 11 seconds to 0.006 seconds just by turning on caching.

This is only true if you're doing a lot of identical selects. In your example, the first query will always take 11 seconds, only the subsequent identical queries will come in at 0.006 seconds.

Always use 'Explain' on your queries before putting them into production.

09-21-2005, 03:55 AM	#1
blazin Confirmed User Join Date: Aug 2002 Posts: 2,781	Improving mysql performance? Anyone got some simple tips on how to improve performance of mysql database. Thanks __________________ I don't endorse a god damn thing......

09-21-2005, 04:40 AM	#2
fris Too lazy to set a custom title Industry Role: Join Date: Aug 2002 Posts: 55,454	customize your /etc/my.cnf default is usally for a crap low end server increase allowed connections, buffer size, etc __________________ Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence. My Cam Feeds Script

09-21-2005, 04:58 AM	#5
Jay_StandAhead Confirmed User Industry Role: Join Date: Jul 2002 Posts: 3,103	you could pay a very experienced programmer to look at your setup and optimize it. Probably would be very cheap check out: http://www.gofuckyourself.com/showthread.php?t=515418 __________________ Our Programs: StandAhead \| IndieBucks \| BoyCrushCash \| Phoenixxx \| EmoProfits \| BritishBucks \| HunkMoney \| LatinoBucks Make $$$ with Gay! Lowest Minimum Payouts in the Business, Perfect Track Record, Amazing Sites

09-21-2005, 05:26 AM	#13
ServerGenius Confirmed User Join Date: Feb 2002 Location: Amsterdam Posts: 9,377	reduce the amount of queries by combining multiple queries to 1 check howto use indexes. In case you have a lot of queries use persistant connections so you don't setup connections for each induvidual query thus flooding your own server with connections.....reduce connection time-out so you don't have unused connection lingering idle on your server __________________ \| http://www.sinnerscash.com/ \| ICQ: 370820 \| Skype: SinnersCash \| AdultWhosWho \| Last edited by ServerGenius; 09-21-2005 at 05:27 AM..

09-21-2005, 05:43 AM	#16
blazin Confirmed User Join Date: Aug 2002 Posts: 2,781	Thanks for the suggestions all __________________ I don't endorse a god damn thing......

09-21-2005, 04:49 AM	#3
drama Confirmed User Join Date: Jul 2004 Posts: 847	write better application code learn how to use mysql indexes Finally, http://dev.mysql.com/doc/mysql/en/my...imization.html

09-21-2005, 05:24 AM	#12
jimthefiend So Fucking Banned Join Date: Oct 2003 Location: icq: 121189 Posts: 18,889	Why dont you MAKE me shut up you fucking gimp? Anyway, I dont think thats going to be much of an issue if the db is running on it's own server. Let me say this again big mouth: I never CLAIMED to be an expert, I just know whats working well for ME. And that's what matters isnt it? RESULTS.

09-21-2005, 06:29 AM	#18
Gungadin Confirmed User Join Date: Aug 2005 Location: O&A PARTY ROCK Posts: 444	If you are doing mostly select's, mysql query cache will make a HUGE improvement in speed. Recently I had a query go from 11 seconds to 0.006 seconds just by turning on caching. __________________ The GFY popularity contest scoreboard

09-21-2005, 10:10 AM	#19
Calvinguy Confirmed User Join Date: Oct 2002 Location: European Union Posts: 1,752	If not already update to mysql 4+ and have query cache enabled. Some hosts still mess around with version 3.23