I set indexes "Experimental Indexes for Core modules", but i get mysql server to crash, i unselect, don't know if the indexes get removed or set to drupal default ? How i can get to drupal default....?

Thanks

Comments

mikeytown2’s picture

mikeytown2 commented

If you unselect and save it will remove the indexes.

What mysql sever are you using? Adding indexes to tables should not make mysql crash. Are you 100% sure this is what caused the crash? any way to reproduce this? What do you mean by crash?

toma’s picture

toma commented

I am setting my website offline now because it slow down mysql until the server get down, i am not sure if its what make my server down, but it was the last change i make to gain performance to my website...

I have MySQL 5.0.91 with php 5.2.14

Information from my drupal sql status report

Command counters

Variable Value Description
Variable Value Description
Com_select 19 The number of SELECT-statements.
Com_insert 1 The number of INSERT-statements.
Com_update 1 The number of UPDATE-statements.
Com_delete 2 The number of DELETE-statements.
Com_lock_tables 0 The number of table locks.
Com_unlock_tables 0 The number of table unlocks.

Query performance

Variable Value Description
Variable Value Description
Select_full_join 0 The number of joins without an index; should be zero.
Select_range_check 0 The number of joins without keys that check for key usage after each row; should be zero.
Sort_scan 1 The number of sorts done without using an index; should be zero.
Table_locks_immediate 2721148 The number of times a lock could be acquired immediately.
Table_locks_waited 23 The number of times the server had to wait for a lock.

Query cache information

The MySQL query cache can improve performance of your site by storing the result of queries. Then, if an identical query is received later, the MySQL server retrieves the result from the query cache rather than parsing and executing the statement again.

Variable Value Description
Variable Value Description
Qcache_queries_in_cache 0 The number of queries in the query cache.
Qcache_hits 0 The number of times MySQL found previous results in the cache.
Qcache_inserts 0 The number of times MySQL added a query to the cache (misses).
Qcache_lowmem_prunes 0 The number of times MySQL had to remove queries from the cache because it ran out of memory. Ideally should be zero.
mikeytown2’s picture

mikeytown2 commented

You enabled the experimental indexes and after some time mysql could not keep up with the requests coming in. Is this correct? I run with all the experimental indexes so I wonder which one could be giving you grief. Could you try it without the menu_links turned on?

Are you using innodb or myisam?

toma’s picture

toma commented

You enabled the experimental indexes and after some time mysql could not keep up with the requests coming in. Is this correct?

Yes!
For the first time i was run with innodb engine, and switch back to myisam, i unselect all Experimental Indexes for Core modules

Thanks

mikeytown2’s picture

mikeytown2 commented

I should make a note saying that unless your using innodb, the Experimental Indexes might be a bad idea. Thanks for the bug report; & let me know how menu_links turn out, if you wish to test (please do). I came up with the experimental indexes after running "mysql_explain_slow_log" from http://www.willamowius.de/mysql-tools.html

toma’s picture

toma commented

When i am trying to unselect "Table: node Column: type"

I get 

Array
(
    [0] => Array
        (
            [success] =>
            [query] => ALTER TABLE {node} DROP INDEX type
        )

)

But i receive change after the message

user warning: Can't DROP 'type'; check that column/key exists query: ALTER TABLE node DROP INDEX type in /home/biladim/public_html/includes/database.mysql-common.inc on line 448.

The "Table: node Column: type" still get indexed and selected

toma’s picture

toma commented

I undo every step i make, i trun the engine to myisam, unselect all index table, still get "Table: node Column: type " selected even i get the success message

Thanks for your support

mikeytown2’s picture

mikeytown2 commented

That's an index that is apart of core; It will show it, but if you try to remove it it will won't work.

Are you sure you wish to undo all the indexes? The ones that gave you issues are "experimental" for a reason (not a 100% sure thing). The rest will help the performance of your views; I'm fairly certain of this.

toma’s picture

toma commented

I am just trying to undo everything so i can see if i get the server to work when i get the website online, so the indexes will work better with MyISAM engine ?

mikeytown2’s picture

mikeytown2 commented

is the server still down? if so then this is something else...

do you have something like phpmyadmin? if so click on the "Processes" tab. If not run this mysql command SHOW PROCESSLIST
Count how many rows appear.

toma’s picture

toma commented

I have the server up, just one website that i set offline, now i unselect all "Experimental Indexes for Core modules" and i keep the indexe core as you advise me, i set the website online, for now i have no problem, hope it still up

I can't access the server now, i am out of office