Strona główna forum Wsparcie Konfiguracja Wielkość bazy danych

Wielkość bazy danych

Wszystko o konfiguracji, możliwościach i ustawieniach...

Post 2 cze 2009, o 23:46

Posty: 73
Gadu-Gadu: 277722

Jeżeli czyściłeś je funkcją delete, to normalne - baza nie zmniejszy automatycznie rozepchanej przestrzeni tabel. Dopiero truncate czyści wszystkie rekordy i zwalnia miejsce zarezerwowane na rekordy w danej tabeli. TRUNCATE TABLE działa nie na rekordzie danych, a jakby usuwał całą tabelę, a następnie odtwarzał strukturę pustej tabeli. Przez to jest też znacznie szybszy. Wadą jest to, że nie mozna zastosować w nim klauzuli WHERE, czyli wyciąć tylko niektórych rekordów.
SingleMalt.pl | The real taste of whisky

Wielkość bazy danych

PostTeraz jest 29 mar 2024, o 14:34
Google

Posty: 427
Lokalizacja: Internet

Post 3 cze 2009, o 12:31

Posty: 116
Gadu-Gadu: 0

No dobrze, ale czy przy automatycznym czyszczeniu logów baza będzie się rozmoarowo zmniejszać czy po prostu będą się usuwać rekordy?
Dobry hosting pod Magento? A może sklep KQS? Tak dla kontrastu...

Post 3 cze 2009, o 15:44

Posty: 73
Gadu-Gadu: 277722

Myślę, że zajętość powinna się szybko ustabilizować i być w zasadzie (dla tych tabel logow) stala,
pozdrawiam
SingleMalt.pl | The real taste of whisky

Post 6 lip 2011, o 12:11

Posty: 11
Gadu-Gadu: 0

Temat stary ale pewnie nie jeden użytkownik ma problem z optymalizacją MySQL:
http://www.magentocommerce.com/wiki/gro ... nce_script

Post 25 sie 2016, o 14:01

Posty: 23
Gadu-Gadu: 0

Witam,
a czy ktoś zna radę czemu mam tak dużych wolnych zapytań do bazy danych? Właściciel serwera mówi mi abym coś z tym zrobił. Strona działa bardzo wolno. Poniżej informację od dostawcy usług:

Witam,

obserwujemy dużą ilość wolnych zapytań do serwera baz danych, prosimy rozwiązać problem, niewielki wycinek poniżej:

# Time: 2016-08-24T21:30:27.125523Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213]  Id: 16256311
# Query_time: 8.054674  Lock_time: 0.000568 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074227;
SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1');

# Time: 2016-08-24T21:30:37.964148Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213]  Id: 16256638
# Query_time: 7.870083  Lock_time: 0.000543 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074237;
SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1');

# Time: 2016-08-24T21:30:48.183008Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213]  Id: 16256817
# Query_time: 7.724487  Lock_time: 0.000502 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074248;
SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1');


Moja baza danych miała coś około 300mb, odchudziłem ją do około 40mb w poprzez poniższy sposób:
TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
TRUNCATE catalog_compare_item;

z tym, że TRUNCATE index_event; coś wywalało błąd.

Posiadam magento 1.9

Domyślam się że chodzi coś o core_url_rewrite

Czy ktoś jest w stanie pomóc?

Poprzednia strona

Powrót do Konfiguracja



cron