[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.

[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?

[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]

[marcm]

Hello Sayan,

I do have SSH enabled on the server. That is the primary way that I access it.

So, do I need to configure MONyog to recognize this? Do I need to setup any port info?

[marcm]

Hi Vishal,

You are welcome, glad to help.

-Marc

[marcm]

Quote:

It would be easier if you provided 'less talk' and 'more exact case'.

^ 'nice'

[attachment=1451:schema.jpg]

[attachment=1452:tabledata1.jpg]

[attachment=1453:table_textfield_view1.jpg]

[attachment=1454:table_textfield_view2.jpg]

[attachment=1455:table_rowresult1.jpg]

SQLyog Ultimate v8.63

XP Pro 64 bit

4 gigs of ram

EVGA NVIDIA GTX 275

[marcm]

Hi Sayan,

Thank you. I was able to manually add it into my my.cnf file and I added a path and then just manually inserted the path into MONyog and it worked. 🙂

So far I am loving MONyog and I only wish I would have installed it sooner 😉

Once I added in the SSH settings and got the monitors and advisors working I was really pleased!

Thanks again.

I'm sure I'll be back with more questions.

[marcm]

post removed. I attempted to stop the service but it failed somehow. I used the command: # /etc/init.d/MONyogd stop and it stopped it and I was able to restart it.

[marcm]

Thanks for your reply Rohit, and best of luck with your new offerings.

[marcm]

Thanks for your reply. Well, I do have a couple of specific questions.

1. Are you against the acquisition of MySQL by Oracle, or are you for it, or neutral?

2. Monty suggests that if Oracle were to kill it or slow it down in the long run, it would in effect kill forks of MySQL because of the lack of ongoing development of MySQL. I'm not sure about the licensing, but if MySQL were killed, say after v6 is released, could MariaDB continue to build on and release subsequent versions, like MariaDB 7, for example, or would the licensing have some impact on it?

My applications are based on MySQL and it concerns me naturally. It also concerns me for the overall availability and development of a free and open LAMP stack for current and future students and small businesses. I'm sure there will be future alternatives, but I don't want to imagine having to rework my code to work with something else. I'm sure there will be problems.

It also concerns me for a budding company such as yours, which is so heavily tied to MySQL.

[marcm]

'marcm' wrote on '04:

Does Webyog and co have any official standpoint on this issue?

Hmm one week later, I guess no comment?

[marcm]

Quick feedback regarding this Mailbrowser. I tried watching your 5 minute video and the narrator's voice is both speaking too quickly and edited together too quickly. I find it more annoying than a commercial on TV. I couldn't finish the video because of this. Perhaps a slower more friendly approach would be more appealing. Maybe others won't mind it but I do and I just wanted to let you know because for every one there could be many…

Good luck with it.

[marcm]

Does Webyog and co have any official standpoint on this issue?

[marcm]

I honestly don't know enough about the other databases out there yet to have an opinion.

I do know that I don't like the idea Oracle owning MySQL. Just like I wouldn't want Microsoft owning Firefox, Linux, PHP or Apache!

I haven't formed an opinion yet on Monty and his intentions, but there are plenty of opinions to go around.

I know his current project is MariaDB. He has invested in his new company quite a bit, so his campaign is likely motivated by MariaDB's future to some degree. I don't know enough about the licensing to know what would happen to MariaDB if Oracle slowed down or even killed MySQL development. One thing for sure though with this campaign is that MariaDB will get a lot of coverage that it didn't have before.

[marcm]

Ok, thanks for your help Peter.

[Author]

Posts