Alerts And Also 'sent' Time

[marcm]

Hello Webyog!

I have a couple of quick questions. First, when I get emails from Monyog sent and I view them in something like Mailwasher, they do not show the 'sent' time, it's just blank. ( I'm using v. 4.02 ) Is there anyway to fix this?

Also, I have a very busy server. I seem to get quite a lot of Alerts over night. A good number of them seem to be related to eximstats. Some of them are also root notify related. And then some are from my own application.

Finally, it seems as if I need to restart MySQL semi-regularly, as after a while things really slow down, almost to the point of blocking access to the site. How often should one need to restart MySQL?

I've been trying my best to optimize the my.cnf file, but I still feel like there could be more optimizing left to do.

My server is a VPS, so there is some sharing going on, I realize this could affect me, especially at night. I have 1.13Gigs of memory and 562 is dedicated to MySQL. Memory currently used by MySQL is 413mb with a virtual mem size of 752mb. Physical memory usage of applications is 100%.

I do have a couple of tables that are MyISAM, and the rest are INNODB. MyISAM has 288mb allocated and INNODB also has 288mb allocated. Obviously there is a lot of info that I'm leaving out but I do not want to include every last detail here unless it is necessary.

Any thoughts?

[peterlaursen]

1)

The 'sent date problem'. Is it OK before the 'mailwash'? is it this one: http://www.mailwasher.net/ you are using? And what email-client?

2)

“I seem to get quite a lot of Alerts over night. A good number of them seem to be related to eximstats. Some of them are also root notify related. And then some are from my own application.”

I do not know 'eximstats'. What is it? also ' root notify related' I do not understand. Are you telling that all those notifications all come from MONyog?

3)

“How often should one need to restart MySQL?”

In principle it should not be necessary at all. The slowdown could be becuase you set too high buffer sizes as compared to what memory you have (and swapping will occur)

If you have info that you do not want to make public you can create a supprot ticket by sending a mail to [email protected].

[marcm]

Hello Peter, thank you for your response.

Yeah the sent date problem only shows up when viewing the basic email row from Mailwasher. When I download them to Thunderbird, the date then shows to be the same, even for alerts that were sent at different times. I just noticed this last part when looking at the rows in Thunderbird.

I have attached some screen grabs for both Mailwasher and TBird.

Eximstats is a user, which I believe is recording site statistics. One of the long queries looks like this:

time taken 11

SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA

another is

time taken 11

INSERT INTO sends (mailtime,msgid,email,processed,user,size,ip,auth) VALUES(FROM_UNIXTIME('1302001735'),'1Q747y-0005E7-OU','admin@####.com',0,'####','759','','courier_login')

or

update smtp LEFT JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=1 where smtp.transport_is_remote=1 and user='####' and smtp.processed=0

There is also the root user as well:

time taken 11

SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA

or

OPTIMIZE TABLE eximstats.sends, eximstats.smtp

---

As for my VPS memory and my.cnf, it looks like this as of now:

MY VPS has 1.13 gigs of memory dedicated to it. My my.cnf file looks like this:

set-variable = max_connections=600

safe-show-database

local-infile=0

skip-symbolic-links

log-slow-queries = /var/lib/mysql/mysql-slow.log

long_query_time = 1

delay_key_write = OFF

query_cache_size = 32M

table_cache = 144

thread_cache_size = 128

key_buffer_size = 288M

innodb_buffer_pool_size = 288M

tmp_table_size = 32M

max_heap_table_size = 32M

innodb_support_xa = 0

lower_case_table_names = 1

[attachment=1561:monyog_emails_mailwasher.jpg]

[attachment=1562:monyog_emails_thunderbird.jpg]

[Mahesh]

You have enabled “Processlist sniffer” to capture real time queries also you are monitoring long running queries,hence you are getting ALERTS for each long running query.

Also are you using “Disk Info” feature frequently because it requires a exepensive query to be executed on a MySQL server “SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) …..(used for calculating Schema size, table size etc..”

I can see that you are getting alerts only for “Long Running Queries” because your server seems to be busy all the time, you can verify by connecting to server and execute “Show Full processlist“?

Also I suggest you to upgrade to latest build and give a try ?

[Sayan Chaliha]

Hi Marc,

Here are some quick thoughts to add to my co-worker's:

'marcm' wrote:

time taken 11
SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA

This particular query is executed by MONyog when you open the “Disk Info” page. It is definitely heavy on the server but is only executed on-demand, once, when you open/refresh the “Disk Info” page. If you use the Disk Info feature of MONyog sparingly, this query shouldn't be executed much on the server.

'marcm' wrote:

long_query_time = 1

A long_query_time of 1 sec is not generally recommended for most applications. What it will do is log all queries taking longer than 1 sec in MySQL's slow query log. Setting it to 1 sec for a database server serving as a back-end to an actively used application would be almost equivalent to enabling the general query log–the implication is lot of extra processing on MySQL. A real-world application will have billions of rows of data and queries might generally average more than 1 sec to execute. MySQL's default is 10 secs. You can set it to whatever suits you, but 1 sec is a little unrealistic :).

The reason why you're getting so many alerts is related to long_query_time… In MONyog's sniffer options (see screen-shot) you can set the time a query takes to execute for it to be recognized by MONyog as “long running”. Now if you've set it to something like 1 sec, and as it seems most queries take longer than a second to execute, MONyog will keep sending you email alerts on them!

As for the sent time of the emails, it's probably a missing header. We'll look into it and let you know.

From your system configuration and memory usage, it's actually almost a given that MySQL will get slow after a while. Physical memory usage of applications is 100%, and MySQL is already using around 750 MB of virtual memory out of which around 400 MB is resident in physical memory, which means lot memory swaps. Once MySQL's caches fill up, it will need more memory. Two things you could do here: free up system resources and leave them for MySQL; and tune cache sizes.

Tuning cache size is not an exact science. No one can give you a definite value and say “this is optimal”. You should look at the Monitors/Advisors on various cache sizes for tips on how to tune them.

[marcm]

Mahesh,

Yes thanks for your reply, I am aware of why I am getting the alerts for long running queries. I almost never click on the Disk Info tab, but I keep the MONyog window open quite often, and it is usually on the monitors/advisors tab.

Sayan, thanks for your reply also. As I mentioned above, I almost never click on the Disk Info page, so that isn't such a problem.

As for the long_query_time = 1, I can easily change that, and I will. But I only have MONyog set to alert me for queries running over 10 seconds.

I've been trying to tune the cache sizes quite a bit, and in general I think I've been able to speed up the normal usage of the server, but still late at night I start getting the long running queries and I end up getting dozens of alerts.

One of the main things I'm wondering is if I should give 288m to both key_buffer_size and innodb_buffer_pool_size.

For MyISAM key cache, I've been looking at cache hit ratio, cache misses, blocks requested from cache etc.

Current page for MyISAM key cache has these figures:

Allocated Memory: 288M

Block Size 1024

no. of blocks 288k

used blocks 35k

percentage of used blocks 13%

Blocks requested from cache 164M 431/sec

cache misses 47k 0.122/sec

cache hit ratio 99.97%

Total blocks written to cache 479k

blocks writtent to disk 369k

cache writes ratio .77

---

InnoDB Cache

Allocated memory 288M

Free Memory 16k

blocks requested from cache 547m 1.4k/sec

cache misses 18.26k .047/sec

cache hit ratio 100%

cache write wait 0%

additional pool size 1M

no of waits 0

---

Do you think either of these look bad or wrong? Maybe the allocated memory is too high for MyISAM Cache?

[Sayan Chaliha]

Hi Marc,

Yes, the e-mails sent by MONyog were missing the date header. It's been fixed, and will feature in the next release of MONyog (scheduled for mid next week). In case you need the fix immediately please let us know and we'll give you a special build.

I'll get back to you on the cache sizes…

[marcm]

I'm happy to report that after I made some additional changes to my my.cnf file and I optimized some tables, I've been able to stop the onslaught of monyog alerts.

I only made a few tweaks.

I increased the join_buffer_size to 1M.

Even though Monyog was only reporting queries longer than 10 seconds, I did change the

long_query_time to 7. This will prevent all those extra writes to the log file.

I bumped up the query_cache = 40M

I then optimized some of the larger tables, esp the busy profile tables.

There are still some joins that might need tweaking, but overall I'm very pleased with this change as I've had no slow queries (+10) over the past few nights.

[Sayan Chaliha]

Hi Marc,

Glad to know 🙂 … Query optimization is very dependent on the type of application, how the tables are organized, what types of queries are being used (like SELECTs, sub-SELECTS, JOINs, etc). and the like. It can actually get quite difficult to figure out what generic MySQL parameters can be tuned to speed up query execution. But good you figured it out! Was MONyog of any help while tuning your server? Do you have any suggestions for improvement?

[Author]

Posts