Improving mysql performance?

[blazin]

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

Thanks

[fris]

customize your /etc/my.cnf

default is usally for a crap low end server

increase allowed connections, buffer size, etc

[drama]

write better application code
learn how to use mysql indexes

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

[jimthefiend]

Originally Posted by fris

increase allowed connections,

Thats key.


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

[Jay_StandAhead]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.

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

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.

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]

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]

Thanks for the suggestions all

[ServerGenius]

Originally Posted by drama

How here is some good advice

I love you too

[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.

[Calvinguy]

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

[NetRodent]

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.