New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
The best way is to look at your queries and optimize your script using them, if that's not an option (i.e. not your script) then you can use http://mysqltuner.com/ to attempt to optimize your MySQL database.
What kind of application are you mostly running? Did you active the slow log query to see if there is some recurring query you can optimize?
The first activity in DB optimizing is to find the queries that uses too much resources and try to find why they use too much resources.
Two usual culprits are:
Would it be possible to put the database on a ramdisk and have it rsync'd to the harddrive every 15 minutes or so?
you need consistency, if you only copy files there is no warranty that something isn't left in ram.
However (in theory) you could replicate your db using the standard replication configuration... the problem is the mess you get:
mmmm... better use a nosql like redis :-)
S
How do I log slow queries and check them?
in the my.cnf file just add
slow_query_log
long_query_time = 5
the second parameter specify how many seconds you consider "slow" queries
Edit: in the [mysqld] section
Your VPS could be swapping a lot. Are your queries heavy reads, heavy writes, or pretty balanced? If the queries are primarily reads, I would recommend using nginx and varnishd (caching engine).
What exactly are you running? There could be many different things you can do to optimize depending on what application/script you're using. Or maybe your MySQL config isn't optimal...
I'd check how many queries per second you're using, you may just need more resources... BuyVM's offloaded MySQL services seem nice (haven't used it personally), might want to look into that if you're running a lot of queries and can get a BuyVM box. Plus, you don't have to worry about optimizing MySQL itself, as I'm sure their configuration is tweaked.
Let's say I have a table named "whatever".
In "whatever", I have 10 columns.
I have a query that says "SELECT * FROM whatever".
If I only need to select 7 of those columns, is it better to have "SELECT col1, col2, col3, col4, col5, col6, col7 FROM whatever" or keep the asterisk because it's not as long?
Why don't you try it on your database and see the time it takes to execute each of them?
It's better to select the columns you're going to actually use.
Not necessarily.
This. Do a trial of, say, 100,000 queries each way, and see which one performs better.
You could try modifying table_cache in your my.cnf to increase repeat queries... Table_Cache should always - well mostly anyway - be significantly bigger than the total number of tables in the server. Otherwise it'll keep opening and closing tables. (IE I/O)
Can someone else run a test like that for me?
SELECT *is a bad coding habit. It may not make much difference on your personal DBs, but in larger deployments you'll notice it. That, and that kind of coding is just a bad habit to have... it's like overusing the regex.+.Yes, select * can be dangerous, expecially if you have text columns in the table(s).
Other than that what's important is to optimize each query, have you verified all queries with explain? Be sure they make use of the indexes.
My understanding of Select * is that it actually does 2 queries: 1 to list all of the columns in a table and the second to pull the column data. If the server already has a list of the columns it's pulling its faster and typically more secure. (ofc I could be wrong since I don't remember where I read it...)
2 different questions... Is that a good idea?
It recently had to be migrated to a less-populated node due to I/O usage. How can I lower this? Thanks.
Basically, put as much as you can in memory, first with the caching system of MySQL. No more precise answer as it depends on your available memory and the schema of your base(s).
SELECT *is always a bad idea, for many reasons; the main one is probably future maintenance trouble. But it could also be a lot slower because you are going to transfer more data than you really need.The problem with the select * is that you usually start using it everywhere (it's so easy) and then don't even know what fields you really need.
Some time ago, one of the big mysql instance we manage for a client (900-1000 qps average) became sloooow, it's a 16 core/64GB ram server with data stored on a 8Gbps FC SAN, so no slowness should be expected :-)
I started investigating and found that there was a lot of I/O activity, thousands of temporary tables were written to disk because of a query that joned a few tables with a select * and one of these table had a few text fields, this query required a temporary table. Temporary tables are created in ram if they DONT make use of any text field (this is a limit of the memory engine), else they are written to disk. I asked to developers if they needed all fields and specifically the text fields and the reply was NO, we don't
they modified the query to include only the required fields and things started flying again...
So the moral is: NEVER USE SELECT *
:-)
Why not. How much are you going to pay?
tmpdir=/dev/shm in your my.cnf (or another ram disk, or making /tmp a ramdisk) will make them be created in memory all the time
Yes, this was the immediate workaround :-)
But on some system you aren't so lucky to have plenty of ram to use for tmp, so developers should optimize things from start... ;-)
My prevents users from signing in (didn't change anything yet) and says I have too many errors and tells me to run "mysqladmin flush-hosts" so I do and it fixes everything until later when it doe it again.
How can I view what is causing the errors?
You could try raising the allowed errors using
max_error_count=XXXX
in my.cnf (you can choose a high value there)
You should look at the logs to see what the errors are.
How do I find these logs?
Go to /var/log/ folder
I have had issues with SELECT * especially on larger databases, it takes FOREVER for the query to load. It is better to tell mysql the exact columns you are needing. You might also want to increase the amount of memory dedicated to caching read requests.
Here is my current my.cnf file. ( http://pastebin.com/ZA82HrW6 ) I do not use InnoDB.
I'm on a Xen VPS with 256mb RAM. Can someone suggest a new my.cnf for me to use?
I ran mysqltuner.pl and it says this:
Nevermind, that AsuraHosting guy helped me out in his IRC channel. I sent screenshots of my mysqltuner.pl output over-and-over and we got rid of all the issues step-by-step. If anyone needs free support you should go there haha. Thanks @AsuraHosting guy.
EDIT: Woot!
