I guess I have a memory problem with MySQL server.
I’m running Ver 5.0.45-log for mandriva-linux-gnu on x86_64 (Mandriva Linux - MySQL Standard Edition (GPL)) on a Intel Xeon E5310 (Quad Core) with 3GB of RAM and 4GB of swap.
But after 5 hours after a restart of MySQL the whole 3GB of RAM is full and 2GB of swap is full.
The guy that made the my.cnf told me that the server is only used for MySQL so it couldn’t be a problem.
But I guess it will be a problem. It has 74 databases and we have around 3 queries a second. Even I see at PHPMyadmin variables in red like: Slow_queries, Innodb_buffer_pool_reads, Innodb_log_waits, Handler_read_rnd, Handler_read_rnd_next, Binlog_cache_disk_use, Created_tmp_disk_tables, Select_full_join, Sort_merge_passes and Opened_tables.
I searched the net to improve theses parts, but it says always stuff like: “increase this part to put more stuff in the memory” that part is impossible because almost all the memory is full.
Is there anything I can change in the my.cnf to lower the memory and/or to speed up MySQL. If some more data is needed, I’m available.
Almost all of the red variables are from bad queries and bad indexes.
You could set in my.cnf two things (with time three):
(make sure, that mysqld will be able to write to the /var/log/mysql - it should, so all You have to do is to run mysql and check if there is log-slow-queries.log)
Inside that file there will be queries that take longer time that long_query_time and queries that don’t use indexes.
You could also make Your own query list. Just put something into php scripts (or applications) that will fetch all the queries. You can skip “insert”, they are not interesting. As for other:
select doesn’t need anything
update needs rewrite
update table set a = b, c = d where e = f
select * from table where e = f
- delete needs rewrite
delete from table where a = b
select * from table where a = b
After that put "explain" in the front of all queries:
explain select * from table where a = b
Put those queries inside sql tab in the phpmyadmin and look at the results table. The most important columns are:
- select_type (ALL means that all the rows from the table are used in the query)
- key (index used with this table)
- rows (amount of the rows that mysql will examine)
- extra (additional info, like sorting, creating temporary table etc).
If there are joins in the query, then You should multiply values in the rows column. It is very bad when result is bigger that 10*fetched rows (the worst case: all the rows from all joined tables).
When You find the worst query then You could add some indexes (on proper columns of course). After that You look for another bad query. After few “loops” mysql should be glad for You efforts and everything should run faster than before.
Edit: I forgot. Mysql runs faster when it has got less queries to execute. Less means no more than 30-40 in one queue. I saw php scripts that executed more than 4000 queries. Mysql wasn’t happy about that.
The log-slow-queries was already there.
Not the option: log-queries-not-using-indexes
I added it.
I tried some of them, but they say every time:
Possible keys: PRIMARY and sometimes a FK
Type: sometimes INDEX, sometimes ref or eq_ref
Rows: sometimes like 6000 and sometime just < 10 (without JOIN)
Extra: Using where, Using Index
But I have no idea what to do with it. Some using indexes, so that isn’t the problem.
But bad queries and indexes could be a possible problem of the memory hog (I’m not a MySQL expert).
Since I added: log-queries-not-using-indexes and restart the mysql server it doesn’t log anything at: /var/run/mysqld/mysqld-slow.log. But MySQL says:
Slow queries: 181169
Flush tables: 1
Open tables: 65
Queries per second avg: 3.855
If no file_name value is given for --log-slow-queries, the default name is host_name-slow.log. If a file name is given, but not as an absolute path name, the server writes the file in the data directory.
With this information I guess I can check the other ones myself. Some will reveal confidential information. That’s why I’m not posting them.
Thanks for that gmouse.
And what about the “SELECT /*!40001 SQL_NO_CACHE */ * FROM SHDIST_53;” queries?
I know it’s difficult to define how much RAM we need for this server, but I guess it’s running out of RAM.
What is a nice RAM, for a DB with:
3 query per second (62% are SELECT queries; 7.5% are SET OPTION queries; 6% are SHOW CREATE TABLE queries; 5.5% are SHOW FIELDS queries; 5% are SHOW TRIGGERS queries; 4% are SHOW TABLES queries; 3% are INSERT queries; the ultimate 7% are divided by a lot of different queries > 0.13% < 1.5% )