I was able to setup a new mysql cluster, with total 6 VMs. New requirement is to add memcache plugin to mysql cluster, memcache failing to start with this error message WARNING -- Failed to allocate nodeid for API at 192.168.1.100. Returned error: 'No free node id found for mysqld(API).' . Here is my existing setup. 192.168.1.100 Management1 192.168.1.101 Management2 192.168.1.102 datanode1 192.168.1.103 datanode2 192.168.1.104 sqlnode1 192.168.1.105 sqlnode2 I am trying to run memcached on existing sql nodes > sqlnode1 & sqlnode2 Here is the configuration on management nodes Management1, Management2 [ndb_mgmd default] DataDir=/mysql/data [ndbd default] NoOfReplicas=2 LockPagesInMainMemory=1 DataMemory=25G IndexMemory=5G NoOfFragmentLogFiles=300 DataDir=/mysql/data MaxNoOfConcurrentOperations=1000000 SchedulerSpinTimer=400 SchedulerExecutionTimer=100 RealTimeScheduler=1 MaxNoOfTables=1024 MaxNoOfOrderedIndexes=256 [ndb_mgmd] HostName=192.168.1.100 NodeId=51 [ndb_mgmd] HostName=192.168.1.101 NodeId=52 [ndbd] HostName=192.168.1.102 NodeId=53 [ndbd] HostName=192.168.1.103 NodeId=54 [mysqld] HostName=192.168.1.104 NodeId=55 [mysqld] HostName=192.168.1.105 NodeId=56 [mysqld] HostName=192.168.1.104 NodeId=57 [mysqld] HostName=192.168.1.105 NodeId=58 Here is configuration file /etc/my.conf on data & sql nodes datenode1, datanode2, sqlnode1, sqlnode2. [mysqld] ndbcluster ndb-connectstring=192.168.1.100,192.168.1.101 datadir=/mysql/data socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql_cluster] ndb-connectstring=192.168.1.100,192.168.1.101 current status of cluster. #ndb_mgm -e show Connected to Management Server at: 192.168.1.100:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=53 @192.168.1.102 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0) id=54 @192.168.1.103 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0, *) [ndb_mgmd(MGM)] 2 node(s) id=51 @192.168.1.100 (mysql-5.7.19 ndb-7.5.7) id=52 @192.168.1.101 (mysql-5.7.19 ndb-7.5.7) [mysqld(API)] 4 node(s) id=55 @192.168.1.104 (mysql-5.7.19 ndb-7.5.7) id=56 @192.168.1.105 (mysql-5.7.19 ndb-7.5.7) id=57 (not connected, accepting connect from 192.168.1.104) id=58 (not connected, accepting connect from 192.168.1.105) # here is the error when I try to add memcache plugin to mysql cluster. created ndbmemcache configuration in database, didnt notice any error. # mysql -u root -p ndb_initialize() main --> connect_to_primary_cluster() 27-Oct-2017 15:35:04 UTC NDB Memcache 5.7.19-ndb-7.5.7 started [NDB 7.5.7; MySQL 5.7.19] Contacting primary management server (192.168.1.100:1186,192.168.1.101:1186) ... main --> ClusterConnectionPool::connect() FAILED. Could not connect to NDB. Shutting down. main --> ndb_destroy() Failed to initialize instance. Error code: 255 # cluster management logs ndb_51_cluster.log show this Warning message. 2017-10-27 15:33:44 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API).' 2017-10-27 15:35:06 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API).' 2017-10-27 15:35:09 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API).' 2017-10-27 15:35:12 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API).' 2017-10-27 15:35:15 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API).' 2017-10-27 15:35:18 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.104. Returned error: 'No free node id found for mysqld(API). ' Can someone please suggest how can I fix this issue, I already have additional sql client nodes, but not able to start memcache process. Thanks,

I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL. I'm wondering whether the memcached plugin has been added to MariaDB? And if not whether one can still use it as an addon? And if not, can I use the existing Cassandra plugin of MariaDB to the same effect?

We have a situation where we need to update on a single row several thousands time a second and this requirement lasts continuously for 5 hours every day due to the nature of the business. Design cannot be changed so we are looking for solutions. Usually we use very fast SSD drives for faster performance and so far we are able to cope up with the requests. But our traffic is increasing every day and we need another solution that can replace our current method and help us update same row many more thousands of time a second. Any suggestion would be great. We use MYSQL 5.6 currently

I originally used "the handlersocket" plugin for NoSQL access to InnoDB tables, then it eventually couldn't be built on newer versions so I migrated to the "Memcached" plugin which was being baked in by Oracle and seemed like a good long term replacement. Now there is a notice on the plugin documentation that "InnoDB memcached plugin is deprecated as of MySQL 8.0.22". Does anyone know why, and second, does this just mean that it won't be baked in any more but can still be compiled when required, or that development of the plugin has ceased? If it's the latter, are there newer/different options for socket access to tables that skip the overhead of the Query Optimizer? I don't understand why something that adds so much efficiency and flexibility would be dropped so unceremoniously :(

I have a mysql server (version 5.1) running Roundcube Webmail client. We have a few hundred users on it during the day. My hypervisor, Virtuozzo, they have been working with us on this all day and found that the container itself is generating an enormous amount of I/O. > The I/O got so high that it crashed the entire server, with corruption > to the file system. ** > DISCLAIMER: The SHOW anything below is fresh after a mysql stop/start! ** (On Centos 6.9) Specs: AMD Opteron(tm) Processor 6344 Core Name Abu Dhabi # of Cores 12-Core # of Threads 12 Operating Frequency 2.6 GHz Hyper Transports 6.40 GT/s L2 Cache 6 x 2MB L3 Cache 2 x 8MB Manufacturing Tech 32 nm 30GB RAM (I have provided this to the container), server has 64 total. My my.cnf config is as follows: (and sorry, it is messy, a lot of us have been trying to keep the server up all day. You may organize it if you wish to do so..) [mysqld] local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 innodb_table_locks=0 #innodb_buffer_pool_size=10240M #Add innodb_buffer_pool_size=16G thread_cache_size=20 tmp_table_size=256M max_heap_table_size=256M #innodb_log_buffer_size=16M query_cache_size=0 query_cache_type=0 innodb_additional_mem_pool_size=32M #innodb_log_file_size=128M #innodb_log_buffer_size=16M innodb_file_io_threads=8 innodb_stats_on_metadata=0 #innodb_additional_mem_pool_size=1024M innodb_thread_concurrency=24 # performance enhancements by python script # innodb_file_per_table=1 # query_cache_size=0 # query_cache_type=0 # thread_cache_size=4 # Recommended. Also changed thread concurrency to 24 from 12 innodb_flush_log_at_trx_commit=0 #query_cache_size=128M key_buffer_size=64M slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time = 1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid innodb_buffer_pool_size=2M innodb_additional_mem_pool_size=500K innodb_log_buffer_size=500K innodb_thread_concurrency=2 [mysqld] max_connections=2000 max_user_connections=0 More Info: mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | innodb_log_file_size | 5242880 | +----------------------+---------+ 1 row in set (0.00 sec) More Info: mysql> SHOW GLOBAL STATUS; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 830216 | | Bytes_sent | 39213368 | | Com_admin_commands | 5 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 2 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 22 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 0 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 7 | | Com_insert_select | 0 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 4259 | | Com_set_option | 1098 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 1 | | Com_show_column_types | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 0 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 0 | | Com_show_create_table | 0 | | Com_show_create_trigger | 0 | | Com_show_databases | 3 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 0 | | Com_show_errors | 0 | | Com_show_fields | 449 | | Com_show_function_status | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | Com_show_processlist | 0 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 7 | | Com_show_storage_engines | 0 | | Com_show_table_status | 0 | | Com_show_tables | 3 | | Com_show_triggers | 0 | | Com_show_variables | 9 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 0 | | Com_update | 26 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 1167 | | Created_tmp_disk_tables | 16 | | Created_tmp_files | 5 | | Created_tmp_tables | 39 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 4309 | | Handler_delete | 2 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 17 | | Handler_read_key | 6473 | | Handler_read_next | 26911 | | Handler_read_prev | 0 | | Handler_read_rnd | 1449 | | Handler_read_rnd_next | 3241 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 24 | | Handler_write | 2468 | | Innodb_buffer_pool_pages_data | 3675 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 239 | | Innodb_buffer_pool_pages_free | 1044849 | | Innodb_buffer_pool_pages_misc | 52 | | Innodb_buffer_pool_pages_total | 1048576 | | Innodb_buffer_pool_read_ahead_rnd | 5 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 125478 | | Innodb_buffer_pool_reads | 3403 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 545 | | Innodb_data_fsyncs | 156 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 62099456 | | Innodb_data_reads | 3447 | | Innodb_data_writes | 321 | | Innodb_data_written | 8141824 | | Innodb_dblwr_pages_written | 239 | | Innodb_dblwr_writes | 38 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 575 | | Innodb_log_writes | 53 | | Innodb_os_log_fsyncs | 80 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 296448 | | Innodb_page_size | 16384 | | Innodb_pages_created | 18 | | Innodb_pages_read | 3657 | | Innodb_pages_written | 239 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 2 | | Innodb_rows_inserted | 7 | | Innodb_rows_read | 32244 | | Innodb_rows_updated | 24 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 53585 | | Key_blocks_used | 3 | | Key_read_requests | 6 | | Key_reads | 3 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 0.000000 | | Max_used_connections | 16 | | Not_flushed_delayed_rows | 0 | | Open_files | 3 | | Open_streams | 0 | | Open_table_definitions | 241 | | Open_tables | 64 | | Opened_files | 365 | | Opened_table_definitions | 241 | | Opened_tables | 519 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | | Queries | 7047 | | Questions | 7047 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 995 | | Select_range_check | 0 | | Select_scan | 44 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 31 | | Sort_merge_passes | 0 | | Sort_range | 31 | | Sort_rows | 1449 | | Sort_scan | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 4331 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 12 | | Threads_connected | 4 | | Threads_created | 16 | | Threads_running | 1 | | Uptime | 405 | | Uptime_since_flush_status | 405 | +-----------------------------------+----------+ 291 rows in set (0.01 sec) And my GLOBAL VARIABLES are here I am not sure why, but I am getting MAJOR I/O to my hypervisor from this container. Here is some info from newrelic: Now I understand that MYSQLTuner needs to have the mysql service be running during this issue times but we made changes to the my.cnf file and restarted, and then ran it. Here is some additional information running right after a fresh service stop/start so keep in mind this may not be helpful: So my question is how can I resolve this extremely bad slowdown I have. I am at a loss for where to begin.. If you need additional info from me let me know. Thank you all!

## Context: I have complicated very often executed query: CREATE OR REPLACE VIEW ordered_words AS SELECT word.id FROM category JOIN frequency ON category.id = frequency.category_id JOIN word ON frequency.word_id = word.id JOIN language ON word.language_id = language.id WHERE category.name='Subtitles' AND iso_639_1='en' ORDER BY frequency.value DESC; I enabled cache query_cache_type=1 and I obtain great performance improvement. **First time (4.5 s)** sql> SELECT * FROM ordered_words 500 rows retrieved starting from 1 in 4s 585ms (execution: 4s 576ms, fetching: 9ms) **Second time (5 ms)** sql> SELECT * FROM ordered_words 500 rows retrieved starting from 1 in 11ms (execution: 5ms, fetching: 6ms) It is ok. ------------------- ## Problem But now I want to add to this result one column with identifiers 1,2,3... because I would like to be able to get selected row from result of this query. I created the following query: SELECT @ROW := @ROW + 1 AS ROW FROM ordered_words t join (SELECT @ROW := 0) t2; But this query is not cached. Any time execution gets about 4.8 sec. I want to save to cache result of this query. ### Alternative solutions: Because of my experience in databases is small. I do not know if my approach is optimal so I describe wider context. In my application inserts are very rarely used. My real target is execute very often query that get one random element from ordered_words with dynamically assigned distribution of probability. I do not want to create new table for this, because category and language are free parameters. I will use function rather. But I do not want execute all query any time. Logic of randomization will be executed out of MySQL. MySQL will obtain only number of word in temporal order saved in result of ordered_words. I am planning treat column row as temporary identifier that allow select word_id without calculating all joins and ordering from first mentioned query. Maybe is there something like temporary table created in memory, or should I use other database system?  ---------------- # Update As @Rick-James suggest. Timing was wrong. Cache was created by my IDE - DataGrip not by MySQL. Finally I decided to save result of my complicated query to table in memory by command: CREATE TABLE IF NOT EXISTS words_memory_subtitles_en (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, word_id INTEGER) ENGINE=MEMORY AS SELECT word.id as word_id FROM category JOIN frequency ON category.id = frequency.category_id JOIN word ON frequency.word_id = word.id JOIN language ON word.language_id = language.id WHERE category.name='Subtitles' AND iso_639_1='en' ORDER BY frequency.value DESC; I will mange creating and removing tables like this in PHP. I decided to remove id from frequency table.

I have complicated query (some joins and ordering), that I want to execute very often. Results of query can change very rarely. Query gets words form given language and category sorted by frequency, so it have two parameters - language and category. I heart about materialized views, or databases in memory, bu I do not have experience enough to consider cons and props in reasonable way. I want to have possibility execute this query for these parameters, and next not execute it until I decide manually, that it must be refreshed. In practice, all application based on executing this query many times, so I do not know if can I create one view in memory, when database use innodb. I can change mysql on something else, and innodb too if is it recommended.

We currently get a lot of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on intervals with a cron. Is this possible? What happens if 2 people hit at the exact same millisecond? Will the counter still go up twice or will it only increment once? Some more information on how to handle this would be great.

We currently get a LOT of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on intervals with a cron. Is this possible? What happens if 2 people hit at the exact same millisecond? Will the counter still go up twice or will it only increment once? What is the best way to handle such a write heavy database?

**background :** I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data. currently I'm using MySQL and have a table to store click info with this schema: visit_id (int) ip (int) date (datetime) country browser device os referrer (varchar) url_id (int) //as foreign key to the shortened URL and for now , just the url_id field has index The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ... for example to generate graphs for past month , I do following queries: SELECT all DAY(date) AS period, COUNT( * ) FROM ( SELECT * FROM visits WHERE url_id = '$url_id' ) AS URL WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) GROUP BY DAY( DATE ) //another query to display clicker browsers in this period //another query to display clicker countries in this period // ... **issues:** - for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible. - lots of memory and CPU is needed to run such queries **questions :** 1- how to improve and optimize the structure , so the analytics of high traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage of CPU and RAM ? should I make an index on the fields date, country, browser, device, os, referrer ? if yes , how to do that for the field date because I should group clicks some times by DAY(date), sometimes by HOUR(date), sometimes by MINUTE(date) and ... 2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example? 3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions? **UPDATE:** I made an index on column referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server. I think making index on these columns would not help to solve my problem, my idea is about one of these: 1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request. 2- using some caching solution like memcached to help MySQL with high traffic links. 3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever. what do you think?

I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely standalone memcached servers in cacheing scenarios. We're using HandlerSocket quite heavily, as it provided a massive increase in IO, and from the original papers on the subject (at the time), it was actually faster than traditional memcached access, thus why we pursued it. However in learning that the new memcache plugin serves the same purpose now, I went searching for benchmarks, comparisons, or even cock-eyed opinion, but wasn't able to find any specifically addressing both. I'd like to know if anyone has had experience with both, switched to/from either and what their experiences were?

What is the default ttl of a key in memcached bucket? Also, what happens when a bucket is full? I created a 256MB RAM Bucket and tried to overload it by sending infinite set queries till error is thrown. But to my surprise, it keep accepting inputs, but total items now fixed to 2.8M with 204M RAM used. Meanwhile, when I tried to fetch the first key it says key not found. Moreover, I keep calling a specific key infinitely it doesnt get expired. So how is it exactly working? Please guide.

thanks for reading this question i hope someone can give some insight into some steps. Here is my steps report. I added memcached instead of mysql cache. optimize the 2 tables from buddypress that where making the mysql overload. But still the problem is there. Not sure what the step might be next? Any guide into this would be much appreciated Table 1 structure wp. SELECT DISTINCT g.id, g.*, gm1.meta_value AS total_member_count, gm2.meta_value AS last_activity FROM b9t_bp_groups_groupmeta gm1, b9t_bp_groups_groupmeta gm2, b9t_bp_groups g WHERE g.id = gm1.group_id AND g.id = gm2.group_id AND gm2.meta_key = 'last_activity' AND gm1.meta_key = 'total_member_count' AND g.status != 'hidden' ORDER BY last_activity DESC LIMIT 0, 20 Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.5.33-31.1-log - Percona Server (GPL), Release rel31.1, Revision 566 Protocol version: 10 cpsrvd 11.42.1.16 Database client version: libmysql - 5.0.96 PHP extension: mysqli bp_groups InnoDB 10 Compact 15191 7703 117030912 0 6979584 5242880 14555 2014-06-12 11:39:33 NULL NULL utf8_general_ci NULL bp_groups_groupmeta InnoDB 10 Compact 30487 86 2637824 0 7946240 4194304 32376 2014-06-12 11:59:52 NULL NULL utf8_general_ci NULL

I have been trying to use memcache with MySQL 5.6 but I can not figure out how to do this. I have add an entry into innodb_memcache.containers so I can query the database through memcache. What I would usually do with memcache is get the value for the key by doing this: $value = $memcache->get($key); but in mysql5.6 potentially there could be a lot of tables specified , so using PHP , how do I specify the table I am trying to query against using the memcache layer?.

I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get cached. So, I configured it manually to bypass cache and stay dynamic (code is executed on every page load).
So, most part of the entire page is executed using HTML and a specific part (a featured bar box) uses a PHP/MySQL.
The code that runs on every page load are some simple ` to select some data from my database and some $fetch and ` to print the results in the webpage, of course. But instead of everytime calling database to get this results, it would be much better to cache this results. So, I have three options: 1. Let WP Super Cache caches my entire page. But I can't do that, because inside my box that currently isn't getting cached, the database data can change anytime (normally 1 time per day). So, I can't cache this box and we have to discart this option 1. 2. Activate query_cache in my.cnf. However, this will only cache MySQL and don't PHP. :( 3. Than, we have memcache (or APC, I don't know much about it's difference). After all my explanations, here's the final question: I'd like to know if installing memcache in my server is enought to get it working imediatally or I have to make any changes in my code to adapt it with memcache; I'd like to know if it will really help me with both PHP and MySQL for this specific part of code I have to cache or it's results is similar to enabling query_cache? Or is using APC rather than memcache better in my case? Thanks,
André.