February 2015 Archives

Query to get size of database tables in MB

If your hosting environment has no database monitoring, you should be able to monitor it manually once in a while or with your own shell script to prevent rapidly growing MySQL tables, because the result of large MySQL tables ( I am talking about 2 – 10 GB with millions of rows ) are often random performance issues, especially if you are using community modules with not well written MySQL queries.

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"
ORDER BY (data_length + index_length) DESC;

SELECT TABLE_NAME, table_rows, data_length, index_length,

round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"

FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"

ORDER BY (data_length + index_length) DESC;

This is the output of the query.

| enterprise_logging_event                      |     122389 |    15939572 |      3270656 |      18.32 |
| catalog_product_index_eav                     |     324014 |     4212182 |     18053120 |      21.23 |
| log_visitor_info                              |     182838 |    19706548 |      3599360 |      22.23 |
| catalog_product_index_eav_idx                 |     324014 |     4522245 |     21139456 |      24.47 |
| catalog_product_entity_varchar                |     255303 |    10852180 |     15244288 |      24.89 |
| log_url                                       |     228886 |    10966704 |     15636480 |      25.37 |
| log_url_info                                  |     228886 |    80258352 |      7542784 |      83.73 |
| enterprise_logging_event_changes              |      69184 |   160619752 |      1499136 |     154.61 |
+-----------------------------------------------+------------+-------------+--------------+------------+
586 rows in set (0.05 sec)

| enterprise_logging_event | 122389 | 15939572 | 3270656 | 18.32 |

| catalog_product_index_eav | 324014 | 4212182 | 18053120 | 21.23 |

| log_visitor_info | 182838 | 19706548 | 3599360 | 22.23 |

| catalog_product_index_eav_idx | 324014 | 4522245 | 21139456 | 24.47 |

| catalog_product_entity_varchar | 255303 | 10852180 | 15244288 | 24.89 |

| log_url | 228886 | 10966704 | 15636480 | 25.37 |

| log_url_info | 228886 | 80258352 | 7542784 | 83.73 |

| enterprise_logging_event_changes | 69184 | 160619752 | 1499136 | 154.61 |

+-----------------------------------------------+------------+-------------+--------------+------------+

586 rows in set (0.05 sec)

Table is marked as crashed and should be repaired

Don’t freak out if your Magento is not working and because of the following error message.

SQLSTATE[HY000]: General error: 145 Table './magento/log_visitor_info' is marked as crashed and should be repaired

1	SQLSTATE[HY000]: General error: 145 Table './magento/log_visitor_info' is marked as crashed and should be repaired

The reason of a crashed MySQL table is mostly related to a server issue. For example:

Any kind of hard-disk failure
Sudden server reboot (power outage in hosting)
Hard server reboot (ACPI shutdown)

In most cases you can repair the affected table with a simple repair command, which comes with MySQL.

mysql> repair table log_visitor_info;
+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| magento.log_visitor_info | repair | status   | OK       |
+-----------------------+--------+----------+----------+
1 row in set (0.54 sec)

mysql> repair table log_visitor_info;

+-----------------------+--------+----------+----------+

+-----------------------+--------+----------+----------+

+-----------------------+--------+----------+----------+

1 row in set (0.54 sec)

This command looks pretty easy and safe, but keep in mind that you should always create a backup before you change anything in your database, because probably more MySQL tables are affected.

Useful Postfix Commands

If your server is configured to send emails with postfix, you should be familiar with some basic Postfix commands without any help from your sysadmin.

1. List your default and custom settings

If you have added your custom Postfix settings or set your own email aliases with virtual_alias_maps, you will find it with postconf -n.

$ postconf -n
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
broken_sasl_auth_clients = yes
....
virtual_alias_maps = hash:/etc/postfix/virtual

$ postconf -n

alias_database = hash:/etc/aliases

alias_maps = hash:/etc/aliases

broken_sasl_auth_clients = yes

....

virtual_alias_maps = hash:/etc/postfix/virtual

If you want to see your Postfix default settings, just change the parameter -n to -d.

$ postconf -d
connection_cache_status_update_time = 600s
connection_cache_ttl_limit = 2s
...
virtual_mailbox_domains = $virtual_mailbox_maps
virtual_mailbox_limit = 51200000

$ postconf -d

connection_cache_status_update_time = 600s

connection_cache_ttl_limit = 2s

...

virtual_mailbox_domains = $virtual_mailbox_maps

virtual_mailbox_limit = 51200000

It’s a good practise to keep this output as a running configuration snapshot in a simple text file in case you have any troubles with Postfix after server reboot or software updates.

$ postconf -d > ~/YYYYMMDD.postfix.default
$ postconf -n > ~/YYYYMMDD.postfix.custom

1 2	$ postconf -d > ~/YYYYMMDD.postfix.default $ postconf -n > ~/YYYYMMDD.postfix.custom

2. List the current queue and email id’s

Check your postqueue frequently to make sure all your emails were delivered correctly. If everything is okay you will see the message “Mail queue is empty”. Otherwise, you will see a list of undelivered emails.

$ postqueue -p
-Queue ID- --Size-- ----Arrival Time---- -Sender/Recipient-------
1B1CDC3312     2102 Tue Feb 17 00:00:01  root@web.yourserver.org
(conversation with smtp.yourserver.org[72.2.43.23] timed out while receiving the initial server greeting)
noreply@otherserver.com

94DD1C031A     1217 Fri Feb 20 00:00:02  root@web.yourserver.org
(conversation with smtp.yourserver.org[72.2.43.23] timed out while receiving the initial server greeting)
noreply@otherserver.com

$ postqueue -p

-Queue ID- --Size-- ----Arrival Time---- -Sender/Recipient-------

1B1CDC3312 2102 Tue Feb 17 00:00:01 root@web.yourserver.org

(conversation with smtp.yourserver.org[72.2.43.23] timed out while receiving the initial server greeting)

noreply@otherserver.com

94DD1C031A 1217 Fri Feb 20 00:00:02 root@web.yourserver.org

(conversation with smtp.yourserver.org[72.2.43.23] timed out while receiving the initial server greeting)

noreply@otherserver.com

3. Read emails from the queue

$ postcat -q 94DD1C031A

1	$ postcat -q 94DD1C031A

4. Delete emails from the queue

$ postsuper -d 94DD1C031A

1	$ postsuper -d 94DD1C031A