I have few websites who are currently using MyISAM DB engine. They are very buzzy joomla websites and consuming a lot of ram and CPU power way much more than i expected.

I`m planing to change the engine from MyISAM to InnoDB. Will this make any progress ?!


One of the websites was abusing the mysql service so i had to make big changed on the website.

here is one part from mysql log.


408 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 409 | user_dbname | localhost | user_dbname | Query | 14 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 410 | user_dbname | localhost | user_dbname | Query | 19 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 413 | user_dbname | localhost | user_dbname | Query | 19 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 416 | user_dbname | localhost | user_dbname | Query | 18 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 417 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 419 | user_dbname | localhost | user_dbname | Query | 14 | Locked | UPDATE jos_content SET ordering = 22 WHERE id = '3128' |
| 429 | user_dbname | localhost | user_dbname | Query | 18 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 431 | user_dbname | localhost | user_dbname | Query | 19 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 432 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 433 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 435 | user_dbname | localhost | user_dbname | Query | 19 | Copying to tmp table | SELECT a.*, u.name, u.username, cc.image AS image, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":" |
| 437 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 438 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 439 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 441 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 442 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 443 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 444 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 445 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 446 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 448 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 449 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 450 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 451 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 452 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 453 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 454 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 456 | bidizdra_new | localhost | bidizdra_new | Query | 0 | Updating | UPDATE jos_content SET hits = ( hits + 1 ) WHERE id='141' |
| 457 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 458 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 459 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 460 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 462 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 463 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 464 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT cc.title AS category, a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.secti |
| 465 | macedoni_joomla1 | localhost | macedoni_joomla15 | Sleep | 0 | | |
| 466 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 467 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 468 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 469 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 470 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 471 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 472 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 473 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 474 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 475 | user_dbname | localhost | user_dbname | Query | 14 | Locked | SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid |
| 476 | user_dbname | localhost | user_dbname | Query | 13 | Locked | SE



I have change the user and the DB name in the log for security.


The is not always problem , sometimes when there are a lot of visitors on line there is no problem. And sometimes when there are so little visitors on line i have big problems

What do you think can be the problem ?!

Hi!

Actually there could be a lot of problems:
*) poor indexing
*) poor application design
*) too litle memory allocated/available for different MySQL buffers

I definitely would suggest you to migrate to InnoDB. It does not lock (so others must wait) whole table on every write to the table. Check out if you use some MyISAM only features, for example, FULLTEXT indexes, which will not work with InnoDB.

You could even replace whole MySQL server with Percona patched MySQL version (see http://www.percona.com/docs/wiki/percona-xtradb:start ) which will give you some additional boost. It's easy to upgrade and is fully backwards compatible with standard MySQL InnoDB plugin (Percona guys claim it is).

Enable also slow query log to see which parts of application do not use indexes and then create appropriate indexes. It would greatly imrove performance.

Try to tune different kind of MySQL buffers (mainly key_buffer for MyISAM or innodb_buffer_pool_size for InnoDB). There are tons of usefull tips how to tune MySQL at http://www.mysqlperformanceblog.com/

I convert to InnoDB and have the same problem.
The process are not lock now but again on some point the ram usage go from 300 to over 2GB.
There are many mySQL process . Everything is going back to normal when i turn off the DB and when i turn in on again is working fine
for few hours or maybe days. and then the same problem again.

Any suggestions.

You should then optimize your application. Unfortunately there is no silver bullet out there. Maybe start with turning on slow query log and after a while take a look on queries not using indexes. This script will help you analyze slow query log: http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_parser

I have worked on cases when adding just a few indexes have an impact on performance in magnitudes of ten.

I`m planing to change the engine from MyISAM to InnoDB. Will this make any progress ?!


Unlikely, unless you have verified you have a specific condition for which InnoDB works better. Generally MyISAM performs better than InnoDB.


What do you think can be the problem ?!


Impossible to say without knowing what's going on; and that's hard work.

But, for example, consider the following code:

LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;

That's perfectly reasonable code to do the job it needs to do - updating a number of rows all at once; but it's going to lock the table and keep it locked till it's finished.

Indexing won't help, because on average the query updates half the rows in the table, and so the quickest method is to read the whole table.

But my central point is, you need to find out what queries are taking the time, and assess whether the time they are taking is reasonable for the function they are performing. If it is, then all you can do to speed things up is provide faster hardware: you can create multiple tablespaces on separate drives and partition the table over the tablespaces. Or use faster disks. Or sometimes keep the table in memory.

Generally MyISAM performs better than InnoDB.

s/better/faster/

To me, a fully transactional engine that supports foreign key constraints performs better, but that's entirely subjective.I generally prefer to choose a db engine based on functionality, not speed.

There's a lot to be done by simply configuring MySQL--for example, I noticed some repeating SELECTs in the log: those should pretty much be always being fed from query_cache--and making sure your tables are properly indexed. Start with the slow_query_log and EXPLAIN statements...

Also, if there really is nothing more you can do to speed up a database, with the low cost of hardware, its often more worthwhile to not worry about tweaking and testing every little setting you can in lieu of just dumping more memory in or upgrading to a faster box. When you factor in the value of your time that will be lost, a few hundred bucks up front, or an extra $50/month might be well worth it.

s/better/faster/

To me, a fully transactional engine that supports foreign key constraints performs better, but that's entirely subjective.I generally prefer to choose a db engine based on functionality, not speed.

And it would be perfectly possible to implement transactions while keeping full-table locking, the difference here is InnoDB performs row-level locking which moves contention from the table down to the row - hence why there is a performance increase in this situation.

I do not need hosting upgrade i`m not on shared hosting.
I have Hybrid VPS server with 2 dedicated Xenon 5430 cores and dedicated 2 GB of ram with RAID 5 HDD.

Like i sad before there is not specific time when i have problem. When there are a lot of users online everything work fine. And at some points when there are only few users online i experience this problem.

90% of the time i use under 400mb of ram.
The problem is starting almost instantly, the ram usage is normal ~300 and in lest than 30-45 sec is starting to rising 500mb, 700mb, 900mb and goes to 2 GB and the same time the swap usage is going up to 2GB too. This is happening in less than 1 minute.

So the upgrade will not fix the problem.


Any other suggestion ?

I do not need hosting upgrade i`m not on shared hosting.
I have Hybrid VPS server with 2 dedicated Xenon 5430 cores and dedicated 2 GB of ram with RAID 5 HDD.

Like i sad before there is not specific time when i have problem. When there are a lot of users online everything work fine. And at some points when there are only few users online i experience this problem.

90% of the time i use under 400mb of ram.
The problem is starting almost instantly, the ram usage is normal ~300 and in lest than 30-45 sec is starting to rising 500mb, 700mb, 900mb and goes to 2 GB and the same time the swap usage is going up to 2GB too. This is happening in less than 1 minute.

So the upgrade will not fix the problem.


Any other suggestion ?

Yes, you need to find out what's causing it.

What you can do is have Apache record in its log the time taken to generate each request, then analyze the logs to find out which pages are taking a long time.

Once you know that, you can examine what they are doing.

For example, suppoose MySQL is configured to use lots of RAM for sorting, and there are some queries that sort a large amount of data. Then when those queries are executed, RAM usage can go up. Similarly, if MySQL is configured to use lots of RAM for temporary tables, then a query that creates a large temporary table can cause RAM use to go up. (Bear in mind that SQL intermediate results can be much larger than the database being used.)

Ooops .....

In addition to Tim's suggestions, if this doesn't seem to have a high correlation with site traffic, or with a specific page or query, make sure there isn't some other background process happening on the machine that's eating up resources at inconvenient times of the day.

When you're talking about RAM usage, are you specifically referring to the MySQL process, or are you looking at the box as a whole?

Also, you say that things are eating up more CPU and RAM than expected, but is the site actually running slowly? Yes, you can always optimize it down, but don't forget that you got all those resources so that they can be used :).

Better to have a fast-running site that is hovering around 75% RAM utilization than a slow site that's at 10%! With that said, the page swaps are troubling...you definitely want to reduce that activity as it absolutely will slow down your site.

But, the first step is to do what has already been suggested. Turn on slow_query logging and have Apache report request times. Try to isolate slow pages and slow queries first.