Memory hog of MySQL

[quacktop]

Hi people,

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.

Kind regards.

[januzi]

Almost all of the red variables are from bad queries and bad indexes.

You could set in my.cnf two things (with time three):
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

(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

[B]Quote:[/B]

update table set a = b, c = d where e = f | v select * from table where e = f

- delete needs rewrite

[B]Quote:[/B]

delete from table where a = b | v select * from table where a = b

After that put "explain" in the front of all queries:

[B]Quote:[/B]

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.

[quacktop]

Hi,

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:
Select_type: SIMPLE
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).

[januzi]

Please, post some of those 6k+ queries and some of those that take more than 5 seconds (in query log look for query time).

As for doing something about it. Are those Your queries ? Fix them. Not Your queries ? Find owner and tell him that he should fix them.

[quacktop]

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:
Threads: 2
Questions: 2923087
Slow queries: 181169
Opens: 438081
Flush tables: 1
Open tables: 65
Queries per second avg: 3.855

my.cnf:
log-slow-queries
long_query_time = 2
log-queries-not-using-indexes
log_long_format

[gmouse]

Do you use innodb, and if so, what are your innodb settings in my.cnf?

If queries are somewhat slow, they also show up in SHOW FULL PROCESSLIST.

[quacktop]

Yes, InnoDB.

The only 2 things I found about InnoDB are:

innodb_buffer_pool_size = 2400M
innodb_log_file_size = 600M

Found 1 query in SHOW FULL PROCESSLIST, that was there for a little time.
The query is has some confidencial information, but I’ll post the explain.

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE p index istpatp PRIMARY 4 NULL 137779 Using where1 SIMPLE stpatp eq_ref PRIMARY,stpatpFK PRIMARY 3 ds.p.istpatp 1 Using where1 SIMPLE stpatptrp ref istpatp istpatp 4 ds.stpatp.istpatp 1 Using where1 SIMPLE s eq_ref PRIMARY,sFKIndex1,sFKIndex5 PRIMARY 3 ds.stpatp.is 1 1 SIMPLE m eq_ref PRIMARY,mFKIndex3 PRIMARY 2 ds.s.im 1 1 SIMPLE u eq_ref PRIMARY PRIMARY 3 ds.s.iu 1 Using index1 SIMPLE e eq_ref PRIMARY PRIMARY 1 ds.m.ie 1 Using index1 SIMPLE pa eq_ref PRIMARY PRIMARY 1 ds.stpatp.ipa 1 Using index

And the first one has a lot of rows.
The query has 24 SELECTS (9 of them are AS), 7 JOINS, 12 WHERE options, 1 ORDER BY and 1 LIMIT (0, 5000)

[gmouse]

Find out what multi column indices can do for you.

[januzi]

[B]quacktop wrote on Fri, 24 July 2009 13:23[/B]

my.cnf: log-slow-queries

[B]mysql_man[/B]

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.

[quacktop]

Ok, the logging is working again…

Time: 090731 19:28:38

User&#64;Host: *

[] @ [192.168..*]

Query_time: 0 Lock_time: 0 Rows_sent: 27 Rows_examined: 86

SELECT id, n FROM p WHERE a=‘1’ ORDER BY n;

“Query time: 0” but it’s logged?

And does this do:

Time: 090801 3:31:03

User&#64;Host: *

[] @ [192.168..*]

Query_time: 25 Lock_time: 0 Rows_sent: 562701 Rows_examined: 562701

SELECT /*!40001 SQL_NO_CACHE */ * FROM SHDIST_53;

or:

Time: 090802 3:20:50

User&#64;Host: *

[] @ [192.168..*]

Query_time: 254 Lock_time: 0 Rows_sent: 1423850 Rows_examined: 1423850

SELECT /*!40001 SQL_NO_CACHE */ * FROM CORTESDESPACHADORES;

Also there are some queries with > 10 < 20 querytime.

[gmouse]

log-queries-not-using-indexes that’s why your 0-time query is logged.

SELECT id, n FROM p WHERE a=‘1’ ORDER BY n;
Add index on combined fields (a,n).

Show the other queries.

[quacktop]

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?

Another question:
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% )
• DB size: 50 GB
• Table engine: Mixed InnoDB and MyISAM

[gmouse]

Those queries are probably for back-up. They need to read and send lots of data and hence take long.

innodb_buffer_pool_size = 2400M
If you also use MyISAM, then this is too much.

3 queries/second is not a lot. It will depend on the size of your working set and the required response time how much you will need.

I could take a look at your settings and indices for a modest fee; pm if you are interested.