Mysql - GoFuckYourself.com

grumpy · 12-13-2002, 09:01 AM

Im using mysql. Is there a better, faster db out there like mysql? Any input is welcome

doobster · 12-13-2002, 09:04 AM

oracle is good, but it costs an arm and a leg. but worth it if you can afford it

grumpy · 12-13-2002, 09:18 AM

I know oracle....to expensive...anything else?

NetRodent · 12-13-2002, 09:34 AM

Depends on what you want to do...

Mysql is very fast for most things. The problems with mysql aren't so much with speed as with features. Mysql doesn't support subselects, stored proceedures and a few other useful things. If you're doing very complex queries the lack of features can slow things down. However, if you feel that mysql is running too slow its more likely a fault in how you have mysql set up. It could also be a hardware limitation. Spend some time reading about optimizing mysql, it can make a big difference. Check your buffer sizes and make sure you are properly indexed. Consider replication. Consider caching results of common queries. And buy more memory. Memory helps any database application.

PostgreSQL is another popular open source database. It tends to be a bit slower than Mysql but is more feature rich.

Oracle in and of itself isn't faster. Oracle seems faster because people who will shell out the money for oracle will also shell out the money for fancy hardware and a proper database admin. Oracle's biggest advantage seemingly is for its ability to spread a single database over several servers.

Our general view is that mysql is good for data collection, but postgres is better for data analysis.

grumpy · 12-13-2002, 09:40 AM

Thats the kind of input im looking for.

Any tips on tuning the server settings for fastest mysql?
I dont do complex querys...its just a lot :-)

Bama · 12-13-2002, 10:00 AM

Take a look at this version - Not a sql person so I can't comment on its' peformance. I know of the guy and he's written some other pretty good applications. He'd probably be able to hook you up with a copy and appreciate feedback on perfomance.

http://marrison.com/?article=marrisonsql

Dragon Curve · 12-13-2002, 10:33 AM

You should generally not be doing a *lot* of queries - and if you are, there's something wrong with your code. MySQL can handle a LOT of queries, but if you're doing so many that it can't handle it, look at your code, and work out how to reduce the number.

grumpy · 12-13-2002, 10:51 AM

Quote:

Originally posted by Dragon Curve
You should generally not be doing a *lot* of queries - and if you are, there's something wrong with your code. MySQL can handle a LOT of queries, but if you're doing so many that it can't handle it, look at your code, and work out how to reduce the number.

Im running a few stats counters through mysql db...so its a lot af access to the db...not because im using a lot of queries in my programs. I just have a lot of visitors :-)

buran · 12-13-2002, 12:33 PM

I've got extensive experience with mysql, postgres, and oracle. I started using msql and migrated to mysql in 1996. Mysql has come a long, long way and is a reasonably solid product. For the majority of web hacks it's going to work great.

The last three software products we've developed have all be designed to work under heavy load with many thousands of requests. We also decided to switch to Postgres to make use of the more advanced features we found ourselves needing.

What you need to do is put a layer inbetween your web requests and the database. You should only serve content directly from the database if there is no other way to do it. What you can do then is to write hits data to flatfiles on the individual machines in your cluster. Then run a cronjob which sucks up that data, collates it, and inserts the reduced data into sql. If you stagger this import on the machines in your cluster, your sql server won't get overburdened and you've no longer got that 1 to 1 association between hits and queries.

Also make sure that you database is optimized, you use the smallest possible column, you have indexes on what you search on and none on the ones you don't, that you set all possible columns as NOT NULL, etc. There are plenty of sites with information on tweaking performance from SQL.

Hope this helps.

grumpy · 12-13-2002, 01:14 PM

Thanks guys for the great input

It help(ed) a lot. Tuninng servers and scripting right now. I apriciated the idea about the " in between " layers.

12-13-2002, 09:01 AM	#1
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	Mysql Im using mysql. Is there a better, faster db out there like mysql? Any input is welcome __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

12-13-2002, 09:04 AM	#2
doobster Confirmed User Join Date: Oct 2002 Location: Ottawa, Canada Posts: 314	oracle is good, but it costs an arm and a leg. but worth it if you can afford it __________________ <a href="http://www.adultautosurfer.com/">AdultAutosurfer.com</a> - 500 Free Hits Automatically After Signup. Completely free to join.

12-13-2002, 09:18 AM	#3
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	I know oracle....to expensive...anything else? __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

12-13-2002, 09:34 AM	#4
NetRodent Confirmed User Join Date: Jan 2002 Location: In the walls of your house. Posts: 3,985	Depends on what you want to do... Mysql is very fast for most things. The problems with mysql aren't so much with speed as with features. Mysql doesn't support subselects, stored proceedures and a few other useful things. If you're doing very complex queries the lack of features can slow things down. However, if you feel that mysql is running too slow its more likely a fault in how you have mysql set up. It could also be a hardware limitation. Spend some time reading about optimizing mysql, it can make a big difference. Check your buffer sizes and make sure you are properly indexed. Consider replication. Consider caching results of common queries. And buy more memory. Memory helps any database application. PostgreSQL is another popular open source database. It tends to be a bit slower than Mysql but is more feature rich. Oracle in and of itself isn't faster. Oracle seems faster because people who will shell out the money for oracle will also shell out the money for fancy hardware and a proper database admin. Oracle's biggest advantage seemingly is for its ability to spread a single database over several servers. Our general view is that mysql is good for data collection, but postgres is better for data analysis. __________________ "Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats." --H.L. Mencken

12-13-2002, 09:40 AM	#5
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	Thats the kind of input im looking for. Any tips on tuning the server settings for fastest mysql? I dont do complex querys...its just a lot :-) __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

12-13-2002, 10:00 AM	#6
Bama Confirmed User Join Date: Nov 2001 Location: Redmond, WA Posts: 2,727	Take a look at this version - Not a sql person so I can't comment on its' peformance. I know of the guy and he's written some other pretty good applications. He'd probably be able to hook you up with a copy and appreciate feedback on perfomance. http://marrison.com/?article=marrisonsql Last edited by Bama; 12-13-2002 at 10:03 AM..

12-13-2002, 10:33 AM	#7
Dragon Curve Confirmed User Join Date: Oct 2002 Posts: 252	You should generally not be doing a lot of queries - and if you are, there's something wrong with your code. MySQL can handle a LOT of queries, but if you're doing so many that it can't handle it, look at your code, and work out how to reduce the number.

12-13-2002, 12:33 PM	#9
buran Confirmed User Join Date: Mar 2002 Location: how'd I get here? Posts: 264	I've got extensive experience with mysql, postgres, and oracle. I started using msql and migrated to mysql in 1996. Mysql has come a long, long way and is a reasonably solid product. For the majority of web hacks it's going to work great. The last three software products we've developed have all be designed to work under heavy load with many thousands of requests. We also decided to switch to Postgres to make use of the more advanced features we found ourselves needing. What you need to do is put a layer inbetween your web requests and the database. You should only serve content directly from the database if there is no other way to do it. What you can do then is to write hits data to flatfiles on the individual machines in your cluster. Then run a cronjob which sucks up that data, collates it, and inserts the reduced data into sql. If you stagger this import on the machines in your cluster, your sql server won't get overburdened and you've no longer got that 1 to 1 association between hits and queries. Also make sure that you database is optimized, you use the smallest possible column, you have indexes on what you search on and none on the ones you don't, that you set all possible columns as NOT NULL, etc. There are plenty of sites with information on tweaking performance from SQL. Hope this helps. __________________ [this signature intentionally left blank]

12-13-2002, 01:14 PM	#10
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	Thanks guys for the great input It help(ed) a lot. Tuninng servers and scripting right now. I apriciated the idea about the " in between " layers. __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888