YOUR QUERY
SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.
REALITY CHECK
No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.
CLUSTERED INDEX
Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.
REDUNDANT INDEXES
You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:
ALTER TABLE newbb_innopost
DROP INDEX threadid,
DROP INDEX threadid_2,
DROP INDEX threadid_visible_dateline,
ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;
Why strip down these indexes ?
- The first three indexes start with threadid
threadid_2
and threadid_visible_dateline
start with the same three columns
threadid_visible_dateline
does not need postid since it's the PRIMARY KEY and it's embedded
BUFFER CACHING
The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.
Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.
TABLE LAYOUT
You could shave of some space from the row by considering importthreadid
and importpostid
. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.
You should run this
SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();
This will recommend what data types these columns should be for the given dataset.
CONCLUSION
MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.
While you revealed the amount of RAM (32GB
) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002
), there are still other pieces to this puzzle you have not revealed
- The InnoDB settings
- The Number of Cores
- Other settings from
my.cnf
If you can add these things to the question, I can further elaborate.
UPDATE 2014-08-28 11:27 EDT
You should increase threading
innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M
I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)
query_cache_size = 0
I would preserve the Buffer Pool
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
Increase purge threads (if you do DML on multiple tables)
innodb_purge_threads = 4
GIVE IT A TRY !!!
OMG! I can't believe I solved it!
I started mysqld. However, client could not be started. When I gave the command 'mysql', the following error was shown:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql
Why not create a user account in mysql?! Hence, I created a user account 'root' with 'root' as the password by writing the following in a text file named init-file and placing it where the user mysqlnew have access right. Note that they must be in different lines. Also, ensure that the server is not running.
UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
FLUSH PRIVILEGES;
I started the server by:
./mysqld --basedir=/usr/local/mysqlnew --datadir=/usr/local/mysqlnew/data --query_cache_type=1 --init-file=/home/mysqlnew/init-file &
I then started the client by
mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql -u root -p
Enter password:
Since I have now created a user account in mysql, I don't need to use --skip-grant-tables anymore for starting server.
However, its not yet finished!
mysql> select 1+1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
So, now I need to rectify this!
mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected (0.28 sec)
And that is it! :)
Best Answer
MySQL has no particular SMTP setup, mechanisms, or drivers whatsoever built in.
However, there are two basic things you can do the kind of monitoring you want.
Option 1 : You Could Monitor the Binary Logs
If binary logs are enabled, you could write a shell script to call mysql and do SHOW MASTER STATUS; If either the filename or filesize changes, something changed. Once detected, you could send out an email expressing that something changed !!!
Try something like this:
Option 2 : You Could Monitor information_schema.tables
You could loop through every table and check its UPDATE_TIME column in information_schema.tables
First collect all table names prepended with database. Then, loop through all the table names and check that entry in information_schema.tables.
Try the following (Any table that changed in the last 10 minutes):
These are just skeleton scripts to detect changes. For option 1, you can do mysqlbinlog against the current binary log and see the SQL that was executed in whatever timeframe you need. For option 2, you can change the SQL to retrieve the datetime stamp of the last update for a given table.
UPDATE 2011-06-29 06:30 EDT
Option 3 : You Could Monitor the general log
Interestingly, you could activate the general log. What's even more intriguing is that you can activate it a MySQL Table. The template to the general log as a table already exists in /var/lib/mysql/mysql as general_log.CSV. Here are the steps:
Step 01) Add these to /etc/my.cnf
Step 02) service mysql restart (general_log is a CSV table after restart)
Step 03) Run these commands to convert the general_Log from CSV to MyISAM
Step 04) Move the general_log file to a huge disk volume that can accommodate a fast growing log table
Example: If you have the following disk layout
Perform these steps to move the general log table:
When do make sure the symlinks exist
Step 05) Run this SQL command
That's it. You should have the general_log as a MyISAM table
All you need to do is poll the general_log table every 15 minutes via crontab running this query
WARNING : Entries will pile up quickly. Delete all events keeping the last 3 days. Run this in a crontab every night a midnight
Give this a Try !!!